none
Excel vba to copy table to outlook body RRS feed

  • Question

  • Here is my requirement:

    I have a table in excel workbook's (sheet1), now i want to copy the table and paste it in email body.

    So far i have:   
    Dim olApp As Outlook.Application
        Set olApp = CreateObject("Outlook.Application")
        Dim msg As Object
        Range("Table1[#All]").Select
        Selection.Copy
    
        Set msg = olApp.CreateItem(olMailItem)
        With msg
        .To = ""
        .CC = ""
        .Subject = ""
        .Body = Selection.Paste
        .Display
        End With

    I'm not able to figure out how to paste the table in outlook. It throws me error at ".Body = Selection.Paste"
    • Moved by Bill_Stewart Saturday, November 28, 2015 2:22 AM Move to more appropriate forum
    Friday, November 27, 2015 10:10 AM

Answers

  • >>>I'm not able to figure out how to paste the table in outlook. It throws me error at ".Body = Selection.Paste"

    Here is an example of adding a table into the body of an email.
    To recreate this sample. Add this to your Sheet code in the VB Editor.

    Sub Mail_Selection_Range_Outlook_Body()
    
        Dim rng As Range
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set rng = Nothing
        On Error Resume Next
        'Only the visible cells in the selection
        Set rng = Selection.SpecialCells(xlCellTypeVisible)
        'You can also use a fixed range if you want
        'Set rng = Sheets("YourSheet").Range("D4:D12").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 = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .HTMLBody = RangetoHTML(rng)
    '        .Body = Selection.Paste
            .Display   'or use .Send
        End With
        On Error GoTo 0
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub

    Then add this code to this Module:

    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 new workbook to past the data in
        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 a 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 RangetoHTML
        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 we used in this function
        Kill TempFile
    
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function

    Monday, November 30, 2015 7:57 AM

All replies

  • Paste takes a target. To see the syntax record the operation as a macro in Outlook and look at the generated code.


    \_(ツ)_/

    Friday, November 27, 2015 7:23 PM
  • Here is a full example in PowerShell of how to paste the clipboard into a new mail item.

    $ol=New-Object -ComObject Outlook.Application
    $mi=$ol.CreateItem(0)
    $mi.GetInspector.Display()
    $mi.GetInspector.WordEditor.Range(0,0).Paste()


    \_(ツ)_/

    Friday, November 27, 2015 8:46 PM
  • 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.

    If want to continue using the Paste method in the code, you need to use the Paste method of the Selection object which comes from a Word document representing the message body, not Excel.
    Saturday, November 28, 2015 10:49 AM
  • 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.

    If want to continue using the Paste method in the code, you need to use the Paste method of the Selection object which comes from a Word document representing the message body, not Excel.

    Don't play this.  Your Ad has nothing to do with how to paste into a message, If you cannot provide accurate help then please don't advertise unhelpful answers. 

    The PowerShell example shows how to insert from the clipboard into a "new" mail item. Salesman who propose silly and useless answers should be ignored or banished from this forum.

    The PowerShell code shows that it takes only one line of code to paste into a mail item body.

    $mailitem.GetInspector.WordEditor.Range(0,0).Paste()

    That is all. just execute the "Paste" method (command).


    \_(ツ)_/

    Saturday, November 28, 2015 12:19 PM
  • PowerShell specific questions are answered on another forum. The current one is for Excel dev questions.

    My post describes all possible way for getting the job done, not just a single one which works from powershell.



    Saturday, November 28, 2015 1:01 PM
  • PowerShell specific questions are answered on another forum. The current one is for Excel dev questions.

    My post describes all possible way for getting the job done, not just a single one which works from powershell.



    Your answer does not answer the question.  PLease consider the question correctly.

    Assume the question is asked for VBA and not for VBScript.  The PowerShell answer can beeasily converted to VBScript or to VBA.

    Please excuse my laziness as I am tired from years of writing VBA and VBScript and prefer the simplicity of the modern Net languages like C# and PowerShell. I ill leave it to you members of the geriatric set to convert this the PowerShell and VSTO versions to the old VBA methods.

    P.S. - most VBA examples posted for this are wrong.


    \_(ツ)_/

    Saturday, November 28, 2015 5:15 PM
  • >>>I'm not able to figure out how to paste the table in outlook. It throws me error at ".Body = Selection.Paste"

    Here is an example of adding a table into the body of an email.
    To recreate this sample. Add this to your Sheet code in the VB Editor.

    Sub Mail_Selection_Range_Outlook_Body()
    
        Dim rng As Range
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set rng = Nothing
        On Error Resume Next
        'Only the visible cells in the selection
        Set rng = Selection.SpecialCells(xlCellTypeVisible)
        'You can also use a fixed range if you want
        'Set rng = Sheets("YourSheet").Range("D4:D12").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 = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .HTMLBody = RangetoHTML(rng)
    '        .Body = Selection.Paste
            .Display   'or use .Send
        End With
        On Error GoTo 0
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub

    Then add this code to this Module:

    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 new workbook to past the data in
        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 a 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 RangetoHTML
        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 we used in this function
        Kill TempFile
    
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function

    Monday, November 30, 2015 7:57 AM
  • Thanks David.

    The code really works, but the formatting is misbehaving.

    I'll work a bit on it and let you know.

    Thanks again.

    Monday, November 30, 2015 10:22 AM
  • There is really no need for all of that reformatting.  Just copy the object tween the apps using the clipboards.

    Copy spreadsheet table to clipboard from Outlook VBA.

    Sub CopyExcelToClipBoard()
        Dim xl As New Excel.Application
        Dim wb As Excel.Workbook
        Dim sht As Excel.Worksheet
        
        wbfile = "c:\scripts\test.xlsx"
        Set wb = xl.Workbooks.Open(wbfile)
        Set sht = wb.ActiveSheet
        sht.UsedRange.Select
        xl.Selection.Copy
        
    End Sub
    
    


    Paste contens of clipboard into ne mail item:

    Sub PasteClipBoard()
        Dim mi As MailItem
        
        Set mi = Me.CreateItem(olMailItem)
        mi.GetInspector.Display
        Set Word = mi.GetInspector.WordEditor
        Word.Range(0, 0).Paste
    
    
    End Sub
    
    

    Just call the two in sequence.  You can also format the object after pasting it by getting the object and applying any stock or custom formats.

    Apply error management as needed.


    \_(ツ)_/

    • Proposed as answer by ryguy72 Wednesday, December 2, 2015 5:17 PM
    Monday, November 30, 2015 11:58 AM
  • For some reason, the code above has had its credits removed (whether by David or someone previously, I don't know) - this is actually a copy-and-paste of Ron Debruin's code (except that the comments giving him credit for it has been removed).  You can see that his e-mail address is still in the To: line ;-)

    So to give him the credit back again, here's the link to the original code, complete with his commented acknowledgement as the author.  Would be nice if those comments were left in.

    rondebruin.nl/win/s1/outlook/bmail2.htm

    Monday, December 14, 2015 2:46 AM
  • Ron copied his code from the MS Samples.  The code was posted to allow the OP to use it as a template to build a PowerShell solution.  The OP seems t have  lost interest in the thread.

    I modified the original code a log time ago as you can see. I took my header and Ron's off to save space.


    \_(ツ)_/

    Monday, December 14, 2015 4:18 AM
  • The code marked as an answer is also not the way to insert a table into Outlook.  In Outlook this takes only one line of code when using PowerShell.

    \_(ツ)_/

    Monday, December 14, 2015 4:20 AM
  • David, thanks for the code. 

    Can you tell me what edits to make to make the email send automatically? I still need to click Send in outlook.

    Wednesday, May 16, 2018 3:58 AM
  • In the Mail_Selection_Range_Outlook_Body function just change the .Display to .Send

        With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .HTMLBody = RangetoHTML(rng)
    '        .Body = Selection.Paste
            .Display   'or use .Send
        End With

    Monday, May 28, 2018 5:52 AM