Yes. In Excel for Microsoft 365 on Windows, a VBA macro can turn a worksheet range into an HTML table and place it in an Outlook email body. The example below displays the message for review before you send it; it requires classic Outlook for Windows, because the new Outlook does not support VBA’s Outlook COM automation.
How the Excel-to-Outlook method works
The macro reads a range of cells, builds HTML table markup, creates an Outlook email, and assigns that markup to the message’s HTMLBody property. HTML is what allows the email to show rows, columns, borders, and basic styling rather than displaying the range as plain text.
This is different from attaching a workbook or pasting a picture. The table is part of the message body, so recipients can read it without opening an attachment. The example uses the cells’ displayed text, which usually preserves number formats such as currency and dates, but it does not reproduce Excel’s full cell formatting, formulas, conditional formatting, or column widths.
Requirements and setup
Use desktop Excel for Microsoft 365 on Windows and classic Outlook for Windows. The macro uses late binding, so it does not require you to add an Outlook library reference in the VBA editor. Outlook must be installed and configured with an account. Macros cannot run in Excel for the web, and Outlook for Mac does not provide the same COM automation interface used by this example.
First, save a copy of the workbook. In Excel, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code below into the module. On a Mac, the Visual Basic Editor shortcut is typically Option+F11 (some keyboards require Fn as well); macro availability and Outlook automation differ, so use the Windows version for this specific code. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
VBA code to put a range in an email
In this example, the worksheet is named Report and the table occupies A1:C4. Change those settings and the recipient, subject, and introductory text to suit your workbook. The macro displays the email instead of sending it automatically, so you can check the recipient and contents first.
Option Explicit
Sub EmailRangeAsHTMLTable()
Dim olApp As Object
Dim olMail As Object
Dim ws As Worksheet
Dim rng As Range
Dim tableHtml As String
Dim signatureHtml As String
Dim messageHtml As String
' Change the sheet name and range for your report.
Set ws = ThisWorkbook.Worksheets("Report")
Set rng = ws.Range("A1:C4")
tableHtml = RangeToHTMLTable(rng)
' Attach to an open classic Outlook session, or start Outlook.
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If
Set olMail = olApp.CreateItem(0)
With olMail
.To = "recipient@example.com"
.Subject = "Weekly report"
' Display first so Outlook can load the default signature.
.Display
signatureHtml = .HTMLBody
messageHtml = "<p>Hello,</p>" & _
"<p>Here is the latest report:</p>" & _
tableHtml & _
"<p>Regards,</p>"
.HTMLBody = messageHtml & signatureHtml
End With
End Sub
Private Function RangeToHTMLTable(ByVal rng As Range) As String
Dim rowNum As Long
Dim colNum As Long
Dim cellText As String
Dim html As String
html = "<table style='border-collapse:collapse;font-family:Arial,sans-serif;font-size:10pt;'>"
For rowNum = 1 To rng.Rows.Count
html = html & "<tr>"
For colNum = 1 To rng.Columns.Count
cellText = HtmlEscape(CStr(rng.Cells(rowNum, colNum).Text))
If rowNum = 1 Then
html = html & "<th style='border:1px solid #999;padding:6px;background:#EAF2F8;text-align:left;'>" & cellText & "</th>"
Else
html = html & "<td style='border:1px solid #999;padding:6px;'>" & cellText & "</td>"
End If
Next colNum
html = html & "</tr>"
Next rowNum
RangeToHTMLTable = html & "</table>"
End Function
Private Function HtmlEscape(ByVal value As String) As String
value = Replace(value, "&", "&")
value = Replace(value, "<", "<")
value = Replace(value, ">", ">")
value = Replace(value, """", """)
value = Replace(value, vbCrLf, "<br>")
value = Replace(value, vbLf, "<br>")
HtmlEscape = value
End Function
The angle brackets and ampersands inside the code block are HTML-escaped for display on this page; when copying the code, use ordinary VBA string contents such as "<p>Hello,</p>" only if your editor retains the displayed entities as their corresponding characters. In a plain-text editor, the intended string starts with a literal less-than sign, as in "<p>Hello,</p>"; do not leave HTML entity text like < in the VBA source. Review the copied code in the VBA editor before running it.
Choose the range and control what appears
The sample range is fixed. If the report grows, you can use a named range or determine the last used row and build the range accordingly. A fixed range is often safer for a regular report because it prevents unrelated notes elsewhere on the sheet from appearing in the email. If you use a dynamic range, test it with an empty sheet and with extra rows so the macro does not include unintended content.
- Change the worksheet: replace
Reportwith the exact worksheet tab name. - Change the range: replace
A1:C4with the cells to include, including the header row. - Set message details: edit
.Toand.Subject; add.CCor.BCCif needed. - Check blank cells: they appear as empty table cells. If you do not want blank rows, remove them from the selected range or add logic to skip them.
The routine treats the first row as a header and renders it with a different background. If your range has no header row, change the condition that checks rowNum = 1 so every cell is written as a regular table cell.
Formatting, values, and signatures
The code reads each cell’s .Text, meaning the value as currently displayed in Excel. This can retain formats such as $1,250.00 or a date format. It also means a narrow column showing #### may produce those hash marks in the email. Widen the column or use a different method of formatting the values before sending. Formula cells contribute their displayed results, not the formula itself.
Only basic table styling is included: borders, padding, a header color, and a font. Email programs can interpret HTML and CSS differently, so simple inline styles are generally more dependable than elaborate layouts. The helper function escapes characters such as ampersands and angle brackets so cell text is treated as text instead of being interpreted as HTML. It also converts line breaks to HTML breaks.
The code displays the message before replacing its body and then appends the Outlook signature that was loaded when the message opened. If your signature contains complex formatting, images, or links, inspect the result in a draft before relying on it. Setting .HTMLBody without first loading or retaining the signature can replace the default signature.
Display the draft or send it automatically
For routine use, displaying the email is a useful safety check: you can verify the table, account, recipient, and signature before clicking Send. To send without opening a draft, the usual Outlook VBA method is to replace .Display with .Send. However, doing so removes the visual review step. Build and validate the message with .Display first, and only automate sending when the recipient list and report range are controlled.
Outlook security settings, organizational policies, or endpoint protection may affect programmatic email. A macro should not be treated as a way to bypass an organization’s approval rules. If a prompt appears or sending is blocked, check with the Microsoft 365 administrator rather than weakening security settings.
Common problems and fixes
- “ActiveX component can’t create object” or Outlook is unavailable: confirm that classic Outlook is installed and configured. The new Outlook does not expose the COM automation object used by this macro.
- “Subscript out of range”: the worksheet name in the code does not match a tab in the workbook. Check spelling and spaces.
- The email table is empty or incomplete: verify the range address and that it refers to the intended workbook sheet. Make sure the data is actually within those cells.
- Numbers appear as hashes: Excel’s displayed text is being used, and the column may be too narrow. Widen it before running the macro.
- Formatting looks different in the recipient’s email: mail clients do not render every style identically. Keep the markup simple and send a test message to the mail systems your recipients use.
- The macro is blocked: save as
.xlsmand follow your organization’s trusted-document or signed-macro process. Do not enable macros in files from unknown sources.
VBA macros cannot be undone with Ctrl+Z. Save a separate copy of the workbook before running or editing code, and test with a harmless recipient address before sending real reports.
Alternatives to an HTML table macro
If you only need a one-time email, copying cells in Excel and pasting them into a new Outlook message is simpler and avoids maintaining VBA. For a recurring report, an HTML macro offers control over the selected range and table appearance, but it relies on desktop Excel and classic Outlook being available.
For workflows that must run online or on a schedule without an open desktop session, consider Power Automate with an Office Script or another approved Microsoft 365 workflow. The setup differs from VBA and depends on your tenant’s services and permissions. If recipients need formulas, filters, or the complete workbook, sending a workbook attachment or a link to a controlled file may be more appropriate than embedding a snapshot of the visible values.
Frequently Asked Questions
Can Excel VBA send a range as a real table in an email?
Yes. Convert the range to HTML table markup and assign it to Outlook’s HTMLBody. Recipients see a formatted table in the message, though styling can vary by email client.
Does this macro work with the new Outlook?
No. This example uses Outlook COM automation, which is available with classic Outlook for Windows, not the new Outlook for Windows. Use an approved workflow such as Power Automate if you need an online alternative.
Can I include more than one worksheet range?
Yes. Build an HTML table for each range and concatenate the resulting HTML strings into the message body. Give each table a heading so recipients can tell the sections apart.
Will the macro include Excel colors and fonts?
Not as written. It creates its own basic HTML styles and reads displayed cell text. Preserving more Excel formatting requires additional VBA that translates cell formatting into HTML.
Can the email send without opening Outlook?
This code starts or attaches to classic Outlook and creates a message through its desktop automation interface. It is not a server-side, unattended sending method; for unattended or cloud-based automation, use a service designed for that workflow and approved by your organization.
Related
- Excel Unable to Merge Cells? Causes, Fixes, and Alternatives
- Excel Pasting: Stop Tab-Delimited Text Splitting on Paste
- Excel Text to Columns Wizard: Split Data Easily
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- MS Access VBA: Send Email With an Attachment SafelyYes. In Microsoft Access for Windows, VBA can create an email in classic Outlook, add a file with…
- Excel VBA Get a Character from a String by PositionTo get one character from a string in Excel VBA, use Mid$(text, position, 1); the position starts at…
- Excel VBA: Hide a Column by Number in Your WorksheetTo hide a column by its number in Excel VBA, set that worksheet column’s Hidden property to True.…
- Excel VBA Concatenate Strings: Methods and ExamplesIn Excel VBA, concatenate strings with the ampersand operator (&), which joins text values into one string. For…
- Google Sheets Macro Tracker: Build a Practical Food LogYes—Google Sheets can work as a flexible macro tracker: log foods and their calories, protein, carbohydrates, and fat,…
- Excel VBA: Delete Rows Based on a Cell Value MatchTo delete worksheet rows when a cell matches a value, loop upward from the last used row and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.