Send HTML Email from Excel using VBA¶
In previous section, I introduced how to send email in a Excel VBA project. In this section, I will introduce how to send HTML email to specified recipients based on Excel sheet data.
Sections:
Build HTML Template¶
Press Alt+F11 to open VBA IDE, click ThisWorkbook add a function like this:
Function BuildHtmlBody()
Dim oSheet
Set oSheet = ThisWorkbook.Sheets(1)
Dim i, rows
rows = oSheet.UsedRange.rows.Count
Dim html, name, address, age, department
html = "<!DOCTYPE html><html><body>"
html = html & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
html = html & "Dear {name}, <br /><br />This is a test email from Excel using VBA. <br />"
html = html & "Here is sheet1 data:<br /><br />"
html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
' Build a html table based on rows data
For i = 1 To rows
name = Trim(oSheet.Cells(i, 1))
address = Trim(oSheet.Cells(i, 2))
age = Trim(oSheet.Cells(i, 3))
department = Trim(oSheet.Cells(i, 4))
html = html & "<tr>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & name & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & address & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & age & "</td>"
html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & department & "</td>"
html = html & "</tr>"
Next
html = html & "</table></div></body></html>"
BuildHtmlBody = html
End Function
Above codes can generate a HTML table based on current Sheet1 data like this:
Now we can add another function like this:
Public Sub SendHtmlMailFromWorkBook()
Dim oSheet
Set oSheet = ThisWorkbook.Sheets(1)
Dim i, rows
rows = oSheet.UsedRange.rows.Count
Dim sender, name, address, subject, bodyTemplate, body, bodyFormat
bodyFormat = 1 'HTML body format
' Please change sender address to yours
sender = "test@emailarchitect.net"
subject = "Test email from Excel and VBA"
' Use a body template to build body text based on current workbook
bodyTemplate = BuildHtmlBody()
Dim emailSent
emailSent = 0
For i = 2 To rows
name = Trim(oSheet.Cells(i, 1))
address = Trim(oSheet.Cells(i, 2))
body = Replace(bodyTemplate, "{name}", name)
If Not SendMailTo(sender, name, address, subject, body, bodyFormat) Then
Exit Sub
End If
emailSent = emailSent + 1
Next
Application.StatusBar = "Total " & emailSent & " email(s) sent."
End Sub
Note
Remarks: All of samples in this section are based on first section: Send email from Excel using VBA and VBScript. To compile and run the following example codes successfully, please click here to learn how to create the test project and add reference of EASendMail to your project.
Run VBA Macro¶
Close VBA IDE and back to Excel, press Alt+F8 to open Macro dialog box,
select ThisWorkbook.SendHtmlMailFromWorkBook and click Run.
You will see the status and result at Excel status bar.
Send Email to Specified Recipients based on Sheet Data¶
If you only want to send email to specified recipients, you can add some conditions in your codes. For example, you can only send email to the person in IT department.
Press Alt+F11 to open VBA IDE, click ThisWorkbook add a function like this:
Public Sub SendMailFromWorkBookToIT()
Dim oSheet
Set oSheet = ThisWorkbook.Sheets(1)
Dim i, rows
rows = oSheet.UsedRange.rows.Count
Dim sender, name, address, subject, bodyTemplate, body, bodyFormat, department
bodyFormat = 1 'HTML body format
' Please change sender address to yours
sender = "test@emailarchitect.net"
subject = "Test email from Excel and VBA"
' Use a body template to build body text based on current workbook
bodyTemplate = BuildHtmlBody()
Dim emailSent
emailSent = 0
For i = 2 To rows
department = Trim(oSheet.Cells(i, 4))
'only send email to the person in IT department
If UCase(department) = "IT" Then
name = Trim(oSheet.Cells(i, 1))
address = Trim(oSheet.Cells(i, 2))
body = Replace(bodyTemplate, "{name}", name)
If Not SendMailTo(sender, name, address, subject, body, bodyFormat) Then
Exit Sub
End If
emailSent = emailSent + 1
End If
Next
Application.StatusBar = "Total " & emailSent & " email(s) sent."
End Sub
Close VBA IDE and back to Excel, press Alt+F8 to open Macro dialog box,
select ThisWorkbook.SendMailFromWorkBookToIT and click Run.
You will see the status and result at Excel status bar.
Download Sample Excel VBA Project¶
You can download Sample.xlsm here, All example codes in this tutorial are included. Please enable Macro after you opened this file, otherwise the codes cannot be executed.
Important
You need to access the Trust Center in the Excel Options dialog box. Click the Microsoft Office Button, and then click Excel Options. In the Trust Center category, click Trust Center Settings, and then click the Macro Settings category.
Next Section
In this section, I introduced how to send HTML email from Excel VBA project. By default, Macro is disabled by Excel. At next section I will introduce how to send email from VBScript directly without Excel Macro.