VBA Macro to Send Email from Excel with Table in Body

excel vba

← PrevNext →

Tools you can use

I recently received an email from a visitor asking me how to send email from Excel with an HTML Table in Body (of the email) using VBA. The email body usually contains plain text data, in paragraphs, and you can attach files. You can also send emails in other formats, like in the form of an HTML Table. I’ll show how you can do this.

Click to enlarge the image!

Send email from Excel with Table in Body using VBA

Let us assume I have some data in tabular format. Its not a Excel table. But, it has borders. It’s a sample report, which has five columns, and the last column has a list of email ids. See the above image.

What this Email will have?

The Subject of the email will be the report header. The data (the body of the email) will be in HTML table format. A mail each will be sent to all the email ids (in the last column).

The Macro

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.

Here’s the code.

Option Explicit

Private Sub CommandButton1_Click()
    Call sendEmail_in_html_format
End Sub

Private Sub sendEmail_in_html_format()

On Error GoTo ErrHandler
    
    ' Set Outlook object.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' Create email object.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)
    
    ' *** We'll now set a range (to extract email ids).
    Dim myDataRng, cell As Range
    Set myDataRng = Worksheets("sheet1").Range("E1:E10" & Cells(Rows.Count, "B").End(xlUp).Row)
    
    Dim sMail_Ids As String         ' Variable to store recipients email ids.
    
    ' Run a loop to extract email ids from the 5nd column.
    For Each cell In myDataRng
        If Trim(cell.Offset(2, 0).Value) <> "" Then
            If Trim(sMail_Ids) = "" Then
                sMail_Ids = cell.Offset(2, 0).Value
            Else
                sMail_Ids = sMail_Ids & vbCrLf & ";" & cell.Offset(2, 0).Value
            End If
        End if
    Next cell
    
    Set myDataRng = Nothing     ' Clear the range.
    ' ***
    
    ' ** Now, let's get the columns for the table header.
    Dim iColumnsCount, iColCnt As Integer      ' Column counters.
    Dim sTableHeads As String
    
    iColumnsCount = Worksheets("sheet1").UsedRange.Columns.Count - 1
    
    For iColCnt = 1 To iColumnsCount
        ' Table header concatenated with HTML <th> tags.
        If (sTableHeads) = "" Then
            sTableHeads = "<th>" & Worksheets("sheet1").Cells(2, iColCnt) & "</th>"
        Else
            sTableHeads = sTableHeads & "<th>" & Worksheets("sheet1").Cells(2, iColCnt) & "</th>"
        End If
    Next iColCnt
    ' **
    
    ' ***** Finally, get the table data.
    Dim iRowsCount, iRows As Integer          ' Row counters.
    Dim sTableData As String
    iRowsCount = Worksheets("sheet1").UsedRange.Rows.Count
    
    sTableData = "<tr>"       ' HTML <tr> tag to create table row.
    For iRows = 3 To iRowsCount
        For iColCnt = 1 To iColumnsCount
            ' Table data concatenated with HTML <td> tags.
            If (sTableData) = "" Then
                sTableData = "<td>" & Worksheets("Sheet1").Cells(iRows, iColCnt) & "</td>"
            Else
                sTableData = sTableData & "<td>" & Worksheets("Sheet1").Cells(iRows, iColCnt) & "</td>"
            End If
        Next iColCnt

        sTableData = sTableData & "</tr>"
    Next iRows
    ' *****
    
    Dim sSubject As String         ' The subject for the email.
    sSubject = Worksheets("sheet1").Cells(1, 1).Value
    
    ' Add CSS style to the table.
    Dim sTableStyle As String
    sTableStyle = "<style> table.edTable { width: 50%; font: 18px calibri; } table, table.edTable th, table.edTable td { border: solid 1px #494960; border-collapse: collapse; padding: 3px; text-align: center; } table.edTable td { background-color: #5a5f6f; color: #ffffff; font-size: 14px; } table.edTable th { background-color : #494960; color: #ffffff; } tr:hover td { background-color: #494960; color: #dddddd; } </style>"
    
    Dim sHTMLBody As String            ' The body (in HTML format) of the email. The table has a CSS class.
    sHTMLBody = sTableStyle & "<table class='edTable'><tr>" & sTableHeads & "</tr>" & _
            "<tr>" & sTableData & "</tr></table>"
            
    With objEmail
        ' Assign all email ids to the property. In-addition, you can add CC or BCC.
        .To = sMail_Ids
        .Subject = sSubject
        .HTMLBody = sHTMLBody
        .Display                   ' Display outlook message window.
        '.Send                     ' Send the email, when you are done.
    End With
    
    ' Clear all objects.
    Set objEmail = Nothing:    Set objOutlook = Nothing
ErrHandler:
    '
End Sub

👉 In-addition, you can send multiple tables from Excel as HTML table in body.

How this Macro works?

A procedure is called when you click a button. The button is an ActiveX control. You can remove the button event and run the macro when the workbook loads.

I have created two objects ("objOutlook" and "objEmail") in the beginning of the procedure.

01) Object objOutlook: Using the CreateObject() function, I have initialized Outlook. I can now access the email properties with the CreateItem() method.

02) Object objEmail: Using this object, I'll create an Outlook item. This will give access to properties such as to, body, subject etc.

Next, I am extracting critical data like, email ids, the headers for the HTML table and finally the data in the table.

🚀 Use our HTML table Generator to create tables on the fly and then email it.

Once, I have the data, I’ll prepare the mail body. Since, the body of the email will have data in an HTML table format, I’ll create a table structure using tags like <table>, <tr>, <td> etc.

The HTML table can be styled using CSS. This is optional though. However, styling an HTML object will give it a clean look.

👉 Use this simple tool to create Table with HTML and CSS code with the click of a button.

Finally, the mail is send. There is one important property that you need to understand. I am not using the ".body" property of the email object, but the .HTMLBody property. So, I can format the body content anyway I want.

With objEmail
    .To = sMail_Ids
    .Subject = sSubject
    .HTMLBody =  sHTMLBody
    .Send                     ' Send the email.
End With

I am hoping that the above code is working fine in your Excel workbook. In case of any query, mail me at arunbanik21[@]rediffmail.com.

Send Excel table in Email as HTML table in Body using VBA

Here’s another scenario. In the above example, the data is in tabular format (rows and columns with borders) but not a table. In Excel, you can create tables. I am sure you know this. But do you know, you can email the entire table (or multiple tables) as an HTML table in the email body.

I'll show you how to read or extract data from an Excel table using VBA and email it as HTML table in Body (email body). This will be my next blog post. I'll post it soon.

That’s it. Happy coding. 🙂

← PreviousNext →