How to Paste Excel Formatting into an Email with VBA
Question details
The user wants to automate copying a selected range in Excel and pasting it into an email body using VBA while preserving the original cell formatting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating email reporting where formatted Excel tables need to be embedded directly in the email body.
- Observed behavior
- The user is looking for the correct VBA method to prevent the copied Excel data from being pasted as plain text.
Ensure the Developer tab is enabled in your Excel ribbon and that you have added the 'Microsoft Outlook Object Library' reference in your VBA editor via Tools > References.
Use the HTMLBody Property in VBA
Convert the Excel range to HTML and assign it to the email's HTML body to retain colors, fonts, and borders.
By publishing the Excel range as a temporary HTML file and reading it back as a string, you can inject the exact formatting into the MailItem.HTMLBody property. This is the most reliable method for preserving complex Excel formatting in emails.
Press Alt + F11 to open the VBA Editor, then click Insert > Module to create a new script module.
Write a custom function that uses the 'PublishObjects.Add' method to save your selected Excel range as an HTML file, and then reads the file into a text stream.
Create your Outlook mail item script using 'CreateObject("Outlook.Application").CreateItem(0)'.
Set the '.HTMLBody' property of the mail item to include the output string from your HTML conversion function, then use '.Display' or '.Send'.
Use the Outlook WordEditor Inspector
Use Outlook's built-in Word editor inspector to simulate a manual copy-and-paste action, preserving the enhanced formatting.
Discover WPS Office for Your Spreadsheet Needs
While advanced VBA integrations with Outlook are specific to the Microsoft ecosystem, WPS Office provides a free, lightweight, and highly compatible alternative for all your daily spreadsheet tasks.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open your Excel Workbook: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm files with full compatibility.
- 3. Share via Email Easily: Use the built-in 'Share' or 'Export to PDF' features to quickly send your formatted spreadsheets via email without needing complex VBA scripts.
Frequently Asked Questions
Why does the .Body property strip my Excel formatting in VBA?
The .Body property in an Outlook MailItem only supports plain text. To retain fonts, colors, and table structures, you must use the .HTMLBody property or interact with the WordEditor object.
Do I need to enable specific references in the VBA editor to interact with Outlook?
Yes, if you are using early binding (declaring specific Outlook variable types), you must go to Tools > References in the VBA editor and check 'Microsoft Outlook Object Library'. If using late binding (CreateObject), this is not strictly required.
Can I send an Excel range as an attachment instead of pasting it?
Yes. You can use VBA to save the specific range or workbook as a new Excel file or a PDF, and then use the '.Attachments.Add' method in your MailItem script to attach the file.
Does WPS Office support VBA macros?
Yes, WPS Office supports VBA macros in its premium versions, providing a highly compatible environment for running your existing Excel scripts.
