VBA to Copy Range to Email Body

If you regularly send data from an Excel cell range via email, using VBA to copy the range to the email body can save you time and effort.

In this tutorial, I will show you how to copy an Excel range to the body of an email body as an HTML table or image using Excel VBA.

Note: Ensure MS Outlook is installed on your device and configured before using the methods.

Method #1: Use VBA to Copy a Range to an Email Body as an HTML Table

You can use VBA code to copy a range in Excel to an email body as an HTML table. An HTML table is a structured arrangement of data into rows and columns using Hypertext Markup Language (HTML).

Suppose you want to use VBA to copy the ‘SalesSummary’ range below on ‘Sheet1’ to an email body in Outlook as an HTML table.

You can use the below steps:

1. Copy the following code and paste it into a standard module:

'Code developed by Steve Scott from https://spreadsheetplanet.com

Sub CopyRangeToOutlookEmailBody()
Dim OutlookApp As Object
Dim rng As Range
Dim OutlookMail As Object
Set rng = Sheets("Sheet1").Range("SalesSummary")
If rng Is Nothing Then
    MsgBox "The worksheet is protected, or the selection is not a range. " & _
           vbNewLine & "Please review and try again.", vbOKOnly
    Exit Sub
End If
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
    .To = "manager@repeatsales.com"
    .CC = ""
    .BCC = ""
    .Subject = "Sales Summary"
    .HTMLBody = RangetoHTML(rng)
    .Display
End With
On Error GoTo 0
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub
Function RangetoHTML(rng As Range)
Dim TempFile As String
Dim TextStream As Object
Dim FSO As Object
Dim TempWB As Workbook
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
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
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
Set FSO = CreateObject("Scripting.FileSystemObject")
Set TextStream = FSO.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = TextStream.ReadAll
TextStream.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
     "align=left x:publishsource=")
TempWB.Close savechanges:=False
Kill TempFile
Set FSO = Nothing
Set TempWB = Nothing
Set TextStream = Nothing
End Function

2. Press Alt + F11 to switch to the worksheet containing the range you want to copy.
3. Press Alt + F8 to open the ‘Macro’ dialog box, select the ‘CopyRangeToEmailBody’ macro on the ‘Macro name’ list box, and click ‘Run.’

The code executes and copies the range to the email body as an HTML table:

Note: This method removes any existing default signature and salutation message in the email body. You should manually reinsert the signature and add a salutation message. 

Explanation of the Code

The VBA macro CopyRangeToOutlookEmailBody, designed for use in Microsoft Excel, begins by identifying a specific range named SalesSummary on the worksheet “Sheet1“.

It checks if this range is valid and, if not, displays an error message before exiting the subroutine.

To optimize performance, the macro temporarily disables Excel’s event handling and screen updating. It then proceeds to create a new email in Outlook, addressing it to “manager@repeatsales.com” with the subject “Sales Summary”.

The core functionality of the macro is to embed the specified Excel range into the email body in HTML format, which is achieved through the RangetoHTML function.

This function copies the range into a temporary workbook, converts it to an HTML file, and then reads this file to set the email’s HTML body, ensuring the Excel data’s formatting is preserved in the email.

Finally, the macro cleans up by closing the temporary workbook and file, re-enabling Excel’s event handling and screen updating, and releasing any used resources by setting the Outlook objects to Nothing.

This automation streamlines the process of sending formatted Excel data via email, directly from within Excel.

Benefits of Copying Range as an HTML Table

Copying a range to an email body as an HTML table instead of an image has several benefits:

1. HTML tables allow the text to be searchable and selectable. Recipients can easily copy and paste information from the table into other documents or applications, which is impossible with an image.
2. HTML tables are more accessible to individuals with disabilities. Screen readers can interpret and read the content of HTML tables, making the email accessible to persons with disabilities.
3. HTML tables can adapt to different screen sizes and devices, providing a better user experience on various devices, including desktops, tablets, and smartphones. Images, on the other hand, may not resize or adapt as effectively.
4. HTML content tends to load faster than images. Using an HTML table can contribute to a quicker loading time for the email, improving the user experience.

Also read: Save Workbook Using VBA

Method #2: Use VBA to Copy a Range to an Email Body as an Image

Suppose you want to use VBA to copy the range ‘SalesSummary’ below on ‘Sheet1’ to an email body in Outlook as a picture.

Add Reference to Microsoft Word Object Library

To copy an Excel range as a picture in the email body using VBA code, you must first add a reference to the Word Object Library in VBA. This reference enables your VBA code to access the Word Object Model in your Excel VBA project, which enables copying the range as an image.

Note: If you don’t add the reference, you will get the error ‘Compile Error: User-defined type not defined’ when you run the code.

You can add the reference using the steps below:

1. Open the ‘Tools’ menu in VB Editor and click ‘References.’

2. On the ‘References – VBAProject’ dialog box, scroll down the ‘Available References’ list box,  select the ‘Microsoft Word 16.0 Object Library’ check box, and click ‘OK.’

Add VBA Code

After adding a reference to the Word Object Library, you can proceed with the following steps:

1. Copy the following VBA code and paste it into a standard module:

'Code developed by Steve Scott from https://spreadsheetplanet.com

Sub CopyRangeToEmailBodyAsImage()
Dim rng As Range
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim Sh As Excel.Worksheet
Dim wdDoc As Word.Document
Set Sh = Sheets("Sheet1")
Set rng = Sh.Range("SalesSummary")
    rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
Set wdDoc = OutlookMail.GetInspector.WordEditor
With OutlookMail
    .To = "manager@repeatsales.com"
    .CC = ""
    .BCC = ""
    .Subject = "Sales Summary"
     wdDoc.Range.PasteAndFormat Type:=wdChartPicture
     With wdDoc
        .InlineShapes(1).Height = 150
     End With
    .Display
End With
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

2. Press Alt + F11 to switch to the worksheet containing the range you want to copy.
3. Press Alt + F8 to open the ‘Macro’ dialog box, select the ‘CopyRangeToEmailBodyAsImage’ macro on the ‘Macro name’ list box, and click ‘Run.’

The macro executes and copies the range to the email body as an image:

Note: This method removes any existing default signature and salutation message in the email body. We recommend manually reinserting the signature and the salutation message. 

Explanation of the Code

The code copies the specified range to the clipboard and creates instances of the Outlook application and an Outlook email item. 

The code further retrieves the Word editor associated with the Outlook email item and pastes the picture from the clipboard into the Word document embedded in the email. 

The code adjusts the height of the inline shape (the pasted image) in the Word document and displays the composed email for the user to review and send.

Benefits of Copying Range to Email Body as an Image

While copying a range to an email body as an HTML table has its advantages, there are situations where you might prefer copying as an image:

1. Images are fixed and cannot be easily edited or manipulated. Copying the range as an image ensures that the data you send via email remains unchanged and is presented as intended without the risk of accidental or intentional modifications.
2. An image maintains the original appearance of the Excel range.

In this tutorial, I showed you how to copy an Excel range to an email body as an HTML table or image.

I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What is VBA in Excel?
• What is Excel?
• How to Activate Workbook Using VBA
• How to Download One Sheet From Excel?
• Useful Excel VBA Macro Codes Examples
• VBA to Print Range of Cells to PDF
• VBA to Copy Sheet to New Workbook
• VBA to Copy Range to Another Sheet
• Creating Named Range Using VBA