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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.


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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you don't know how to downgrade, my instructions below should be helpful.
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
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 …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

747 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

13 Experts available now in Live!

Get 1:1 Help Now