none
VBA Code to send Email from Outlook Using Range of data as body RRS feed

  • Question

  • Hi,

    I have  an excel template file which contain mail id to whom i need to send mail. One sheet of that file contain a table of 9 columns and 12 rows (fixed). I need to send this table(eg. Range (A1:I12)) as mail body (not as attachment) including color of the row and column with same format (like a image of that range). I can send mail from outlook through Excel VBA but never like this. So please help me.

    Thanks,

    Srimanta Manna

    Wednesday, January 13, 2016 9:19 AM

Answers

  • >>>I need to send this table(eg. Range (A1:I12)) as mail body (not as attachment) including color of the row and column with same format (like a image of that range). I can send mail from outlook through Excel VBA but never like this. So please help me.

    According to your description, I have made a sample to let you refer to:

    Sub SendMail()
        Debug.Print RangetoHTML(Range("A1"))
        
        Dim OutApp As Object
        Dim OutMail As Object
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
                
        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(Range("A1"))
            .Display
        End With
         
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub
    
    Function RangetoHTML(rng As Range)
        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 workbook to receive the data.
        rng.Copy
        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
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
     
        ' Publish the sheet to an .htm file.
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
     
        ' Read all data from the .htm file into the RangetoHTML subroutine.
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
     
        ' Close TempWB.
        TempWB.Close savechanges:=False
     
        ' Delete the htm file.
        Kill TempFile
     
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function
    

    For more information, click here to refer about OfficeTalk: Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook

    • Marked as answer by Srimanta Manna Friday, January 15, 2016 11:43 AM
    Thursday, January 14, 2016 6:08 AM

All replies

  • Hello Srimanta,

    Looks like you need to automate Outlook from Excel VBA. See How to automate Outlook from another program for more information. 

    The Outlook object model provides three main ways for working with item bodies:

    1. Body - a string representing the clear-text body of the Outlook item. 
    2. HTMLBody - a string representing the HTML body of the specified item.
    3. Word editor - the Microsoft Word Document Object Model of the message being displayed. The WordEditor property of the Inspector class returns an instance of the Document class from the Word object model which you can use to set up the message body.

    You can read more about all these ways in the Chapter 17: Working with Item Bodies. It us up to you which way is to choose to customize the message body. But the Body property will not help you because you need to preserve colors (keep the markup as is).

    Note, you can Copy the required range in Excel and then use the Paste method from the Word object model for pasting the data in the message body automatically. 

    Wednesday, January 13, 2016 12:46 PM
  • >>>I need to send this table(eg. Range (A1:I12)) as mail body (not as attachment) including color of the row and column with same format (like a image of that range). I can send mail from outlook through Excel VBA but never like this. So please help me.

    According to your description, I have made a sample to let you refer to:

    Sub SendMail()
        Debug.Print RangetoHTML(Range("A1"))
        
        Dim OutApp As Object
        Dim OutMail As Object
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
                
        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(Range("A1"))
            .Display
        End With
         
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub
    
    Function RangetoHTML(rng As Range)
        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 workbook to receive the data.
        rng.Copy
        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
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
     
        ' Publish the sheet to an .htm file.
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
     
        ' Read all data from the .htm file into the RangetoHTML subroutine.
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
     
        ' Close TempWB.
        TempWB.Close savechanges:=False
     
        ' Delete the htm file.
        Kill TempFile
     
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function
    

    For more information, click here to refer about OfficeTalk: Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook

    • Marked as answer by Srimanta Manna Friday, January 15, 2016 11:43 AM
    Thursday, January 14, 2016 6:08 AM
  • You will find your answer here.

    http://www.rondebruin.nl/win/s1/outlook/amail4.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, January 15, 2016 1:41 PM
  • Hello there!  I like what you did with your code to create the email, however it does not display a signature at the bottom of the body.  If someone has a default signature that they need at the bottom every time, how would you add that to the code to make sure it shows the signature?
    Monday, February 11, 2019 5:38 AM