Click to enlarge the image!
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).
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 WithI 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.