Transfering Excel Content To Outlook Email Body

Posted on 2009-05-11
Last Modified: 2012-05-06
Hello Experts,

I'd like to set up a command buttion which when clicked will transfer all of the text in a range of cells in Excel (A1:I11) and place it in the body of an email in Outlook.

Please advise & thank you!
Question by:Escanaba
  • 4
  • 3
LVL 59

Accepted Solution

Saurabh Singh Teotia earned 500 total points
ID: 24358648
There you go, I believe this is what you are looking for...
further you can have a look over the following link:-
which provides different ways to do what you want...
All credit Goes to genius Rondebruin.

Author Comment

ID: 24358755
Thanks for the quick response Saurabh...
We are close but can you tell me what I need to do to prevent this from automatically sending the email out?  Ideally I want Outlook to open with the selected range in the body of the email and then have the end user put in their own send to, CC, BC and subject line information and then manually send it out.

Author Comment

ID: 24358770
Current Snippet:

Private Sub CommandButton1_Click() 

    Dim rng As Range

    Dim OutApp As Object

    Dim OutMail As Object 

    Set rng = Nothing

    On Error Resume Next

  Set rng = Sheets("Email Template").Range("A1:I11").SpecialCells(xlCellTypeVisible)

    On Error GoTo 0 

    If rng Is Nothing Then

        MsgBox "The selection is not a range or the sheet is protected" & _

               vbNewLine & "please correct and try again.", vbOKOnly

        Exit Sub

    End If 

    With Application

        .EnableEvents = False

        .ScreenUpdating = False

    End With 

    Set OutApp = CreateObject("Outlook.Application")


    Set OutMail = OutApp.CreateItem(0) 

    On Error Resume Next

    With OutMail

        .To = ""

        .CC = ""

        .BCC = ""

        .Subject = "This is the Subject line"

        .HTMLBody = RangetoHTML(rng)

        .Send   'or use .Display

    End With

    On Error GoTo 0 

    With Application

        .EnableEvents = True

        .ScreenUpdating = True

    End With 

    Set OutMail = Nothing

    Set OutApp = Nothing

End Sub

Function RangetoHTML(rng As Range)

' Changed by Ron de Bruin 28-Oct-2006

' Working in Office 2000-2007

    Dim fso As Object

    Dim ts As Object

    Dim TempFile As String

    Dim TempWB As Workbook


    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


    'Copy the range and create a new workbook to past the data in


    Set TempWB = Workbooks.Add(1)

    With TempWB.Sheets(1)

        .Cells(1).PasteSpecial Paste:=8

        .Cells(1).PasteSpecial xlPasteValues, , False, False

        .Cells(1).PasteSpecial xlPasteFormats, , False, False


        Application.CutCopyMode = False

        On Error Resume Next

        .DrawingObjects.Visible = True


        On Error GoTo 0

    End With


    'Publish the sheet to a htm file

    With TempWB.PublishObjects.Add( _

         SourceType:=xlSourceRange, _

         Filename:=TempFile, _

         Sheet:=TempWB.Sheets(1).Name, _

         Source:=TempWB.Sheets(1).UsedRange.Address, _


        .Publish (True)

    End With


    'Read all data from the htm file into RangetoHTML

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

    RangetoHTML = ts.ReadAll


    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _

                          "align=left x:publishsource=")


    'Close TempWB

    TempWB.Close savechanges:=False


    'Delete the htm file we used in this function

    Kill TempFile


    Set ts = Nothing

    Set fso = Nothing

    Set TempWB = Nothing

End Function 




Open in new window

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 24358785
Just realized the .display prompt which takes care of it.  Thanks so much for your assistance!!!
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24359031
I was away for a while thats why wasnt able to reply you back and glad you were able to fix it, Also for my reference any particular reason why you graded it B..?

Author Comment

ID: 24368643
Your help was greatly appreciated.  I typically give a B grade when responses are links to another Q&A or publication rather than providing the code in the inital response.
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24370232
I agree that i gave you link to the page directly but isn't that page covered everything that you need, I can have copied the entire thing and pasted here and thats not the right thing since im not giving the credit to the right person who should ideally get it. Again my only point that i want to make out here, if you follow your rule which you mentioned you should ideally check how much re-work you need to do because of the link provided which was nothing in this case.

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Read this checklist to learn more about the 15 things you should never include in an email signature.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now