Sending daily status updates or sales figures via email often means manually copying Excel tables and pasting them into Outlook. Doing this repeatedly every day is time-consuming and introduces unnecessary formatting mistakes.

In this guide, you will learn how to use a simple Excel VBA macro that converts a specified range into a clean HTML table and generates an Outlook email with the data right inside the email body with just one click.

Why Automate Excel-to-Outlook Emails?

Embedding reports directly into the email body makes your updates immediately readable for managers and clients without requiring them to open attachments:

• Clean HTML Formatting: Preserves table structure, fonts, and cell alignment inside Outlook.
• Pre-filled Recipients & Subject: Automatically populates the ‘To’, ‘CC’, and ‘Subject’ fields to eliminate manual input errors.
• Draft or Direct Send: Allows you to review the email draft before sending, or auto-send it instantly.

Step 1: Open the VBA Editor Window

1. Open the Excel workbook containing the table you want to email.
2. Press Alt + F11 (Option + F11 on Mac) to open the Visual Basic Editor.
3. In the top menu, click Insert ➔ Module to open a blank code module.

Step 2: Copy and Paste the Email Automation Code

Copy the code block below and paste it directly into your blank module window:

Sub SendExcelTableViaOutlook()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim rng As Range
    Dim htmlBodyText As String
    
    ' 1. Select the range to send (Adjust range A1:E10 as needed)
    On Error Resume Next
    Set rng = ActiveSheet.Range("A1:E10")
    On Error GoTo 0
    
    If rng Is Nothing Then
        MsgBox "The specified range was not found!", vbExclamation, "Excel Owl Automation"
        Exit Sub
    End If
    
    ' 2. Initialize Outlook Application
    On Error Resume Next
    Set OutlookApp = CreateObject("Outlook.Application")
    On Error GoTo 0
    
    If OutlookApp Is Nothing Then
        MsgBox "Microsoft Outlook is not installed or opened on this system.", vbCritical, "Outlook Error"
        Exit Sub
    End If
    
    ' 3. Create Mail Item
    Set OutlookMail = OutlookApp.CreateItem(0)
    
    ' Convert Range to HTML Table String
    htmlBodyText = "<p>Hello Team,</p>" & _
                   "<p>Please review today's summary report below:</p>" & _
                   RangeToHTML(rng) & _
                   "<p>Best regards,<br><strong>Excel Owl Automation</strong></p>"
    
    ' 4. Configure Email Fields
    With OutlookMail
        .To = "manager@example.com"
        .CC = "team@example.com"
        .Subject = "Daily Report Update - " & Format(Date, "YYYY-MM-DD")
        .HTMLBody = htmlBodyText
        .Display ' Use .Send to send automatically without previewing
    End With
    
    ' 5. Clean up objects
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

Function RangeToHTML(rng As Range) As String
    ' Helper Function: Converts Excel Range to HTML Format
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
    
    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy-hh-mm-ss") & ".htm"
    
    ' Copy the range and paste into a temporary workbook
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1, 1).PasteSpecial Paste:=8
        .Cells(1, 1).PasteSpecial xlPasteValues, , False, False
        .Cells(1, 1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1, 1).Select
        Application.CutCopyMode = False
    End With
    
    ' Publish temporary workbook as HTML
    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
    
    ' Read HTML code from temporary file
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangeToHTML = ts.ReadAll
    ts.Close
    
    ' Close temporary workbook and clean up files
    TempWB.Close SaveChanges:=False
    Kill TempFile
    
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Step 3: Run the Macro Using Alt + F8

Generate your email preview instantly right from Excel:

1. Switch back to your main Excel sheet.
2. Press Alt + F8 (or Option + F8 on Mac) to open the Macro dialog.
3. Select SendExcelTableViaOutlook from the list and click Run.
4. An Outlook email window will pop up with your Excel table embedded neatly into the email body!

💡 Pro Tip: Automatic Background Sending
Change .Display to .Send in step 4 of the code if you want the email to send silently in the background without opening the preview window first.

Step 4: Save as Macro-Enabled Workbook (.xlsm)

To preserve your email automation code, save your Excel file in the correct macro format:

1. Click File ➔ Save As (or press F12).
2. In the Save as type drop-down, choose Excel Macro-Enabled Workbook (*.xlsm).
3. Click Save.

⚠️ Important Note: Regular .xlsx files do not support macro scripts. Saving as .xlsx will permanently delete your VBA code!