VBA Copy and Paste Excel Cells Into the Email Body

Yes. In Excel for Microsoft 365 on Windows, VBA can copy a cell range and paste it into a message in classic Outlook as a formatted table. The practical approach is to copy the range, display an Outlook email, then paste through Outlook’s Word editor; the macro below creates a draft for review rather than sending it automatically.

What the macro does—and why the method matters

Excel cells are not automatically an email table just because a macro assigns a range to an Outlook message. Outlook’s plain-text Body property does not preserve a worksheet’s rows, columns, borders, colors, and number formats as a rich table. For a formatted result, the macro uses Excel’s clipboard and Outlook’s Word-based message editor, which can receive a copied range as a table.

The sample creates a new message, shows it, and pastes the selected range at the beginning of its body. It leaves the message open so you can check the recipients, text, layout, and attachments before deciding whether to send it. The pasted table is a snapshot: changing the worksheet later does not automatically revise the table already in the draft.

Before you run the VBA

This method is intended for desktop Excel and classic Outlook for Windows. The newer Outlook for Windows does not provide the same COM automation and WordEditor support used by this approach. If Outlook is set to the new experience, use classic Outlook if it is available, or choose one of the alternatives below.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA in the file.
Change the worksheet name and range in the example to match your workbook.
Make sure classic Outlook is installed and configured with an account. The recipient and subject in the example are placeholders.
Enable the Developer tab if needed: in Excel, select File > Options > Customize Ribbon, then check Developer.

VBA macros can make changes that Ctrl+Z cannot undo. Save a copy of the workbook before running a macro, especially when adapting code that edits or deletes worksheet data.

Copy a range into an Outlook email with this macro

In Excel, press Alt+F11 to open the Visual Basic Editor on Windows, choose Insert > Module, and paste in this code. The example copies cells A1:D8 on a sheet named Report. It uses late binding, so you do not have to add an Outlook reference in the VBA editor.

Sub EmailReportRange()
    Dim sourceRange As Range
    Dim outlookApp As Object
    Dim message As Object
    Dim inspector As Object
    Dim wordDoc As Object
    Dim insertionPoint As Object

    ' Set this to the worksheet and cells to include.
    Set sourceRange = ThisWorkbook.Worksheets("Report").Range("A1:D8")
    sourceRange.Copy

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

    ' Create and display a draft before accessing its Word editor.
    Set message = outlookApp.CreateItem(0)
    With message
        .To = "recipient@example.com"
        .Subject = "Weekly report"
        .Display
    End With

    Set inspector = message.GetInspector
    Set wordDoc = inspector.WordEditor
    Set insertionPoint = wordDoc.Range(0, 0)
    insertionPoint.PasteExcelTable False, True, False

    ' Clear Excel's moving copy border; the pasted table remains.
    Application.CutCopyMode = False
End Sub

Run the macro from Excel’s Developer > Macros list, select EmailReportRange, and choose Run. The message should appear with the range in its body. In the code, False, True, False are the three arguments to Word’s PasteExcelTable method: do not link the pasted table to Excel, use Word formatting, and do not request RTF. The exact appearance can still vary with the cells’ formatting and Outlook’s message settings.

Adapt the range, recipients, and message

Change Worksheets("Report") to the actual tab name, keeping the name inside quotation marks, and set the range to the cells you want included. For example, Range("A1:F20") copies a fixed block. A fixed range is predictable, but it will not expand automatically when new rows are added below it. If the amount of data changes, you can instead refer to an Excel table’s data range or calculate the last used row in VBA; choose a method that matches the structure of your sheet.

Set .To to the intended recipient or recipients, separated by semicolons, and edit .Subject. You can add a greeting or explanatory text in the Outlook editor after the draft opens. The sample pastes at the start of the message, so the table may appear before an existing default signature. Review the finished draft and move the cursor or table if you want a different order.

The code deliberately calls .Display and does not call .Send. That pause is useful: it gives you a chance to catch a wrong range, stale data, an incorrect recipient, or formatting that needs adjustment. Avoid changing it to send automatically unless you have a controlled process for validating the content and recipients first.

Choose between a table, picture, and plain text

A pasted table is usually the best choice when recipients need to read individual values, select text, or copy cells into another application. It can retain useful formatting, but it may reflow when the email window is narrow or when viewed on a phone. Wide worksheets are often easier to read as a deliberately narrowed range than as a full-width report.

Editable table: Copy and paste the cells through the Word editor as in the macro. Recipients can generally select and copy the content, although email clients may render tables differently.
Picture: Copy a range as an image when exact visual appearance matters more than editing or copying its values. Excel’s Copy as Picture command is available from Home > Copy > Copy as Picture. A picture can be hard to read when scaled down and is less accessible than text.
Plain text: Use a text representation for a small, simple list when formatting is unimportant. It is compact, but columns may not line up consistently in proportional fonts and the result is not a true spreadsheet table.
Workbook attachment: Attach the workbook when recipients need formulas, multiple sheets, filtering, or the underlying data. An attachment is not a substitute for a quick in-body summary, and it may expose other workbook content if you have not checked what the file contains.

One common misconception is that a copied table stays connected to its source by default. This example explicitly chooses an unlinked paste, so later worksheet edits do not flow into the message. A linked paste can depend on access to the source workbook and may be unsuitable for recipients outside your organization; for routine email, a reviewed snapshot is usually more dependable.

Common problems and practical fixes

The macro stops at Outlook creation: Confirm that classic Outlook is installed and configured, and that your organization permits Outlook automation. The new Outlook application does not support this classic COM-based workflow.
The body is blank or the paste fails: The message must be displayed before the code accesses Inspector.WordEditor. Keep .Display before the WordEditor lines, and check that the message editor is available rather than relying on a background draft that has not opened.
The wrong cells appear: Verify the tab name and range in sourceRange. A sheet name must match the worksheet tab, including spaces. Test with a small, harmless range before sending a real report.
Formatting looks cramped: Reduce the number of columns, shorten long headings, or adjust column widths before copying. Email clients and screen sizes differ, so a table that fits in Outlook on a desktop may require horizontal scrolling on a phone.
The table appears above a signature: The insertion point is at the start of the message body. Move the table or signature in the draft, or adapt the insertion point if your workflow requires a specific layout.
The copied range seems stale: The macro pastes what Excel has copied at that moment. Confirm that formulas have recalculated and that the intended sheet is active or explicitly referenced before creating the draft.

If the VBA editor reports that a method or object is unavailable, check which Outlook application is installed before rewriting the paste code. Excel for Windows automation examples that use Outlook.Application and WordEditor are not directly portable to every Outlook edition.

Windows, Mac, and web differences

The code above targets Excel for Microsoft 365 on Windows with classic Outlook. Excel for the web cannot run VBA macros. Excel for Mac can run VBA, but Outlook for Mac does not expose the same Windows COM automation model used here, so this exact Outlook object-creation and WordEditor method is not a cross-platform solution. Keyboard shortcuts differ too: Ctrl+C is the Windows copy shortcut, while Command+C is used on Mac.

If you need a repeatable workflow across platforms, consider using a Microsoft 365 automation tool supported by your organization, or prepare the email manually and paste the copied range. If using an automated service, verify that it supports the data sources, mail client, permissions, and formatting you require; the availability of a tool can depend on your Microsoft 365 plan and tenant configuration.

Frequently Asked Questions

Can VBA paste Excel cells directly into the body of an email?
Yes. On Windows with classic Outlook, VBA can copy a range and paste it into the message’s Word editor as a table. The sample creates a draft so you can inspect it before sending.

Will the pasted email table update when the Excel cells change?
Not with the sample code. It creates an unlinked snapshot at the time of pasting. Run the macro again to create a fresh table after updating the worksheet.

Can I use this macro with the new Outlook for Windows?
Not as written. The code depends on classic Outlook’s COM automation and WordEditor support. Use classic Outlook if available, or choose a manual paste or a supported automation alternative.

Can I email only the visible cells in a filtered range?
The example copies the specified range, and it does not explicitly filter the copy to visible cells. If you need only visible cells, select or identify those cells deliberately and test the result first; copying a filtered range can require additional VBA handling to avoid including hidden rows.

Can I add text before or after the pasted table?
Yes. After the draft opens, type in the Outlook editor and position the cursor where the text belongs. For a fully automated layout, the VBA needs to manage the Word editor’s insertion points and content order as well as the table paste.

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.