Macro to Email Multiple Tables from Excel as HTML table in Body

Let us assume, I have multiple tables in my Excel worksheet. I want to email the tables as it is (with color, fonts etc.) as HTML table in the email body. I’ll show you how easily you can do this using a simple VBA macro.

email multiple tables from Excel as HTML table in body using vba macro

Create tables in your Excel worksheet

First, create few tables in your Excel worksheet.

Name the tables. So, we know which tables to extract data from. I have two tables in my worksheet and I have named it "tab_August" and "tab_September".

Note: You must have Ms-Office Outlook installed in your computer. Therefore, please make sure you have the latest Outlook installed and configured.

👉 Also Remember: Before you start working with the macro, you must add a Reference of Microsoft Outlook 16.0 Object Library (version 12.0 or later). I have explained it here.

VBA Macro to Email tables as HTML table in Body

Option Explicit

Dim sHTMLBody As String

Private Sub extractTableData()
    ' For HTML.
    Dim sTableStyle As String
    
    ' Loop through each list object (tables) in sheet1.
    Dim objCount As Integer
    For objCount = 1 To Sheet1.ListObjects.Count
        
        Dim oList As ListObject
        Set oList = Sheet1.ListObjects(objCount)
        
        ' Get the headers first.
        Dim iTotalColumns, iColCnt As Integer      ' Column counters.
        Dim sTableHeads As String
        
        Dim thBkColor, thColor As String        ' For header background and fore color.
        
        iTotalColumns = oList.Range.Columns.Count
        For iColCnt = 1 To iTotalColumns
            ' Table header concatenated with HTML  tags.
            If (sTableHeads) = "" Then
                sTableHeads = "<th>" & oList.Range(1, iColCnt) & "</th>"
            Else
                sTableHeads = sTableHeads & "<th>" & oList.Range(1, iColCnt) & "</th>"
            End If
            
            ' get the background and fore color of the HEADER cells.
            thBkColor = Right("000000" & Hex(oList.Range(1, iColCnt).Interior.Color), 6)
            thBkColor = Right(thBkColor, 2) & Mid(thBkColor, 3, 2) & Left(thBkColor, 2)
            
            thColor = Right("000000" & Hex(oList.Range(1, iColCnt).Font.Color), 6)
            thColor = Right(thColor, 2) & Mid(thColor, 3, 2) & Left(thColor, 2)
        Next iColCnt
        
        ' ***** GET TABLE DATA.
        Dim tdBkColor, tdColor As String    ' For cell background and fore color.
        Dim iRowsCount, iRows As Integer    ' Row counters.
        Dim sTableData As String
        
        iRowsCount = oList.Range.Rows.Count
        
        sTableData = "<tr>"       ' HTML <tr> tag to create table row.
        For iRows = 2 To iRowsCount
            For iColCnt = 1 To iTotalColumns
            
                oList.Range(iRows, iColCnt).Select
                
                ' Now, get the background and font color of the ROW cells.
                ' Since a table uses "conditional formating", I am using ".DisplayFormat.Interior.Color" to get background color.

                tdBkColor = Right("000000" & Hex(oList.Range(iRows, iColCnt).DisplayFormat.Interior.Color), 6)
                tdBkColor = Right(tdBkColor, 2) & Mid(tdBkColor, 3, 2) & Left(tdBkColor, 2)
                
                tdColor = Right("000000" & Hex(oList.Range(iRows, iColCnt).Font.Color), 6)
                tdColor = Right(tdColor, 2) & Mid(tdColor, 3, 2) & Left(tdColor, 2)
            
                ' Table data added to HTML <td> tags.
                sTableData = sTableData & "<td style='background-color: #" & tdBkColor & "; " & _
                    "color : #" & tdColor & "'>" & oList.Range(iRows, iColCnt) & "</td>"
            Next iColCnt
    
            sTableData = sTableData & "</tr>"
        Next iRows
        ' *****
        
        ' Create CSS for the table.
        sTableStyle = "<style> " & _
            "h2 { text-align: left; font-size: 20px; } " & _
            "table.edTable { width: 30%; font-family: Calibri; } " & _
            "table, table.edTable th, table.edTable td { " & _
                "border: solid 1px #494960; border-collapse: collapse; padding: 3px; margin: 0; text-align: center; } " & _
                "table.edTable th { background-color : #" & thBkColor & "; color: #" & thColor & "; } " & _
                "tr:hover td { background-color: #494960; color: #ddd; } " & _
            "</style>"
    
        ' The body (in HTML format) of the email. The table has a CSS class.
        If Trim(sHTMLBody) = "" Then
            sHTMLBody = sTableStyle & "<h2>" & Replace(oList.Name, "tab_", "") & "</h2><table class='edTable'><tr>" & sTableHeads & "</tr>" & _
                "<tr>" & sTableData & "</tr></table>"
            sTableHeads = ""
        Else
            sHTMLBody = sHTMLBody & _
                sTableStyle & "<h2>" & Replace(oList.Name, "tab_", "") & "</h2>" & "<table class='edTable'><tr>" & sTableHeads & "</tr>" & _
                "<tr>" & sTableData & "</tr></table>"
        End If
    Next objCount
    
    sendEmail
End Sub

Private Sub sendEmail()
On Error GoTo ErrHandler
    ' All set, now create outlook and email objects.
    
    ' Set outlook object.
    Dim oOutLook As Object
    Set oOutLook = CreateObject("Outlook.Application")
    
    ' Create email object.
    Dim oEmail As Object
    Set oEmail = oOutLook.CreateItem(olMailItem)
       

    With oEmail
       ' Assign all email ids to the property. In-addition, you can add CC or BCC.
       .To = "abc@gmail.com"
       .Subject = "This is a test email"
       .HTMLBody = sHTMLBody
       .Display          ' Display outlook message window.
       '.Send            ' Send the email, when you are done.
    End With

    ' Clear all objects.
    Set oEmail = Nothing:    Set oOutLook = Nothing
ErrHandler:
    '
End Sub

Press "F5" and run the macro.

The first procedure extracts (or gets) data from tables. The second procedure emails the tables that are formatted as HTML tables.

The table extraction from the worksheet it important. The macro looks for objects (tables, in this case), loops through each table object to get the data.

First the macro extracts table header values, followed by data from each table row.

Almost everything as it is is extracted, including the background (interior color), font color etc.

Since a table uses conditional formatting, the macro uses .DisplayFormat.Interior.Color to get the background color of each row cell.

You can email any number of tables in your worksheet as HTML body, add attachments if you want.

You can make this a little more Dynamic. In the above example, I have hardcoded the “To” address while sending the email. You can however, extract email ids from your worksheet (if you a have list). See this example.

That’s it. Happy coding. 🙂

← PreviousNext →