VBA Excel Range Copy and Paste Into an Outlook Email

To copy an Excel range into an Outlook email with VBA, copy the range, display a new message in classic Outlook for Windows, then paste the range through the message’s Word editor. The macro below inserts the range as a formatted table and displays the email for review; it does not send it.

How Excel-to-Outlook pasting works

Outlook’s message editor on Windows uses Microsoft Word technology. That makes it possible for an Excel macro to copy cells to the clipboard and ask the open email’s Word editor to paste them. The result is generally a table in the message body, rather than an image or an attachment. Cell formatting such as borders, fills, and number formats can carry across, though the final appearance can depend on the Outlook message format and the recipient’s email client.

The process has an important order: create the message, display it so its editor is available, obtain that editor, and then paste. Trying to paste before the message is displayed can fail because the Word editor may not yet exist. The code uses late binding for Outlook and Word objects, so it does not require adding an Outlook or Word reference in the VBA editor.

VBA macro to paste a range into a new email

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and put the macro in the new standard module. Change the worksheet name and range to match the cells you want to include. The example adds a greeting, pastes A1:D12 from a worksheet named Report, and opens the draft.

Sub PasteRangeIntoOutlookEmail()
    Dim sourceRange As Range
    Dim outlookApp As Object
    Dim mailItem As Object
    Dim inspector As Object
    Dim wordDocument As Object
    Dim wordRange As Object

    ' Set the worksheet and cells to copy.
    Set sourceRange = ThisWorkbook.Worksheets("Report").Range("A1:D12")

    ' Attach to Outlook if it is open; otherwise start it.
    On Error Resume Next
    Set outlookApp = GetObject(, "Outlook.Application")
    If outlookApp Is Nothing Then
        Set outlookApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0

    If outlookApp Is Nothing Then
        MsgBox "Outlook could not be opened.", vbExclamation
        Exit Sub
    End If

    ' Create an HTML message and display it to initialize its editor.
    Set mailItem = outlookApp.CreateItem(0)
    mailItem.BodyFormat = 2
    mailItem.Display

    Set inspector = mailItem.GetInspector
    Set wordDocument = inspector.WordEditor
    Set wordRange = wordDocument.Range(0, 0)

    ' Add a short introduction, then paste the copied cells as a table.
    wordRange.InsertBefore "Hello," & vbCrLf & vbCrLf
    wordRange.Collapse 0
    sourceRange.Copy
    wordRange.PasteExcelTable False, False, False

    Application.CutCopyMode = False
End Sub

Save a copy of the workbook before running a macro. VBA actions cannot be reversed with Ctrl+Z, and this macro creates an Outlook draft. Confirm the worksheet, range, recipient, and pasted content in Outlook before sending the message yourself.

Adjusting the range and email content

The sample uses a fixed range, which is easy to understand but must be edited if the report changes size. For a variable report, you can use a named range, a table’s DataBodyRange, or code that finds the last populated row and column. Make sure the selected area does not include thousands of unused rows: a huge range can create an unwieldy email and take longer to copy.

To address the email, add a recipient and subject before displaying it. For example, set the mail item’s To property to an email address and its Subject property to a meaningful subject line. Keep the message displayed rather than calling Send while developing or testing. A draft gives you a chance to catch incorrect recipients, stale figures, hidden columns, or an unexpectedly large selection.

The example places the table near the start of the message, before any existing signature. If you want it after a signature or after other content, the insertion point must be moved to the intended position in the Word document. Outlook signatures can be configured differently for new messages and replies, so check where the signature appears in your own draft before automating that layout.

Choosing between a table, an image, and a link

Pasting a range as a table is useful when recipients need to read, select, or copy the values. The pasted cells are a snapshot: later edits to the workbook do not update the email. In the sample, all three PasteExcelTable arguments are False, so the paste is not linked to the workbook. A linked table can update in some desktop Office workflows, but links are fragile when the recipient cannot access the source file and may raise security or update prompts.

Use a pasted table for a compact report that should be readable in the email body.
Use an image when preserving a fixed visual layout matters more than selecting individual cell values. An image is less accessible and its text is not as convenient to copy.
Attach the workbook when recipients need formulas, multiple sheets, filtering, or the complete underlying data.
Share a workbook link when people should work from one current file and have appropriate access to its storage location.

For most routine status emails, an embedded table is a practical compromise: it is visible without opening an attachment, while the workbook can still be attached or linked when more detail is needed.

Formatting and message-format considerations

The macro sets BodyFormat to 2, the Outlook constant for HTML, before displaying the message. HTML supports formatted content and tables; plain-text messages do not preserve a pasted spreadsheet table in the same way. The PasteExcelTable call takes three Boolean arguments: whether to link to Excel, whether to use Word formatting, and whether to use RTF. This example uses False for each, favoring an unlinked paste with Excel-side formatting rather than Word formatting.

Excel and Outlook do not render every detail identically. Column widths may change to fit the message window, long text can wrap, and colors or fonts can look different on another device. Before relying on a specific layout, inspect the draft at a realistic window width. If the table is too wide, reduce the copied columns, adjust widths, or provide a simpler summary in the email and include the full workbook separately.

Hidden rows or columns can also surprise you depending on the selected cells and paste behavior. If the email must show a precise report, create a clean output range or copy only the visible, intended cells. Avoid copying a worksheet’s entire used range when only a small table is needed.

Outlook version and platform limits

This method is for Excel and classic Outlook desktop on Windows. It relies on Outlook’s Windows Object Model and WordEditor, accessed through COM automation. The new Outlook for Windows does not support this traditional VBA/COM automation workflow. If the macro cannot create or control Outlook, verify that classic Outlook is installed and that the intended Outlook profile is available.

Excel for the web cannot run VBA macros. On a Mac, Excel supports VBA in general, but this Outlook automation code depends on Windows COM and does not work as written. Mac users need a different approach, such as manually copying and pasting, using an available Microsoft 365 workflow, or redesigning the process around a platform-supported service. The exact options depend on the organization’s account, policies, and apps.

Common problems and practical fixes

“Outlook could not be opened” or an automation error: Check that classic Outlook is installed, configured with a profile, and permitted by organizational policy. The new Outlook is not a drop-in COM automation target.
The table does not appear: Ensure the message is displayed before accessing WordEditor, and confirm that the range contains cells. If the clipboard has been replaced by another operation, copy the range again immediately before pasting.
The table replaces or disrupts text: The insertion point determines where pasted content goes. The sample collapses the Word range after adding the greeting; inspect the draft and adjust the insertion position if you change the preceding text.
Formatting looks different: Check the selected range, column widths, message format, and paste options. Some rendering differences between Excel, Outlook, and recipients’ clients are normal.
A security prompt appears: Programmatic access can be affected by Outlook security settings, antivirus status, and organization-managed policies. Do not weaken security controls just to suppress a prompt; ask an administrator about approved automation methods.

Excel may block macros depending on the file’s origin and Trust Center policy. In Excel for Windows, macro-related options are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Follow your organization’s rules; do not enable macros from an untrusted workbook. A signed, approved macro or a managed automation process is safer than broadly lowering macro protections.

When a different automation method is better

VBA is suited to a desktop workflow where a person wants Excel to prepare a draft for review. If the process must run unattended, send messages on a schedule, or work across Windows and web clients, consider an organization-approved service such as Power Automate or a Microsoft Graph-based application. Those options require appropriate permissions and setup; they are not simply alternate VBA commands.

For a one-off email, manual copy and paste may be faster and easier to verify. For recurring reports, a macro can reduce repetitive steps, but it should be designed around a stable output range and include checks for missing data. Regardless of the method, review the recipient list and confidential content before sending. Automation makes a repeated task faster; it does not establish that the copied information is correct or appropriate to share.

Frequently Asked Questions

Can VBA paste an Excel range into the body of an Outlook email?
Yes, in classic Outlook for Windows. Copy the Excel range, display the message, obtain its WordEditor, and paste at a Word range. The example above uses PasteExcelTable to create an embedded table.

Does the pasted Outlook table update when the Excel cells change?
Not with the sample macro. Its paste is unlinked, so the email contains a snapshot of the cell contents and formatting at the time it was pasted. If the data changes, generate or paste the table again.

Can I send the email automatically instead of displaying it?
VBA can call an Outlook mail item’s Send method, but sending immediately removes the review step and can distribute incorrect or sensitive information. The sample deliberately displays a draft. Add automatic sending only when recipients, data, and authorization have been carefully validated.

Will this macro work with the new Outlook or Outlook on the web?
No, not through the traditional Outlook COM Object Model used here. Use classic Outlook for Windows for this macro. Outlook on the web and new Outlook require a different supported automation approach.

Can I use the same code in Excel for Mac or Excel for the web?
No. Excel for the web does not run VBA, and the code’s Outlook automation relies on Windows COM. Excel for Mac does not provide that Windows COM connection, so use a platform-appropriate alternative or paste manually.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.