[VBA] How to Paste an Excel Table into Outlook

2025年12月30日

Environment

Windows Specification: Windows 10 Pro
Version: 20H2

Background

I investigated whether it is possible to paste a table created in Excel into an Outlook email using VBA.

Using “.GetInspector().WordEditor.Windows(1).Selection.Paste”

I found that using .GetInspector().WordEditor.Windows(1).Selection.Paste works well. Here is the code.

Sub SendEmail()

    Dim objOutlook As Outlook.Application
    Dim objMail As Outlook.MailItem
    Dim wsMail As Worksheet

    Set objOutlook = New Outlook.Application
    Set wsMail = ThisWorkbook.Sheets(1)
    Set objMail = objOutlook.CreateItem(olMailItem)

    ActiveWorkbook.Save

    With wsMail
        objMail.To = .Range("A6") ' Email To
        objMail.CC = .Range("B6") ' CC
        objMail.Subject = .Range("C6").Value   ' Subject
        objMail.BodyFormat = 3     ' Email Format (Rich Text)
        objMail.Body = .Range("D6").Value  ' Body
        
        .Range("D10:F15").Copy ' Copy the table
        
        ' Paste the table
        objMail.GetInspector().WordEditor.Windows(1).Selection.Paste
    End With
  
End Sub

Code Explanation

(Blank lines are not counted.)

Line 1: Defines the macro name.
Lines 2-4: Declares variables.
Lines 5-7: Sets the variables. In Line 5, I reference “Sheet1” because that is where I have written information like the recipient address and subject.
Line 8: Overwrites and saves the file.
Line 9: Starts the With statement.
Line 10: “To” address. If there are multiple recipients, you can connect them with &.
Line 11: “CC” address.
Line 12: Email Subject.
Line 13: Email Format. I set this to “3” for Rich Text format. (Usually, “1” is Plain Text and “2” is HTML). Set this according to your needs.
Line 14: Email Body.
Line 15: Copies the table range.
Line 16: This is the command to paste the table.
Line 17: Ends the With statement.
Line 18: Ends the macro.

Future Developments

With this method, the table is pasted at the very beginning of the email body. I wanted to paste the table at the end of the body, but I could not get it to work, so I gave up for now. Therefore, this article only covers the process of pasting the table.