1. Home
  2. EASendMail SMTP Component
  3. KB
  4. VBA/VBScript Access
  5. Send HTML Email from Access using VBA

Tutorial Index

Send HTML Email from Access using VBA

In previous section, I introduced how to send email in a MS Access VBA project. In this section, I will introduce how to send HTML email to specified recipients based on Access data.

Build HTML Template

Press Alt+F11 to open VBA IDE, click Form_Users_Form add a function like this:

Function BuildHtmlBody()

    Dim html, ID, 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 MS Access using VBA. <br />"
    html = html & "Here is current recordset data:<br /><br />"
    html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
    Dim rs, sql
    Set rs = CreateObject("ADODB.Recordset")

    sql = Me.RecordSource
    If Me.FilterOn Then
        sql = sql & " WHERE " & Me.Filter
    End If

    rs.Open sql, CurrentProject.Connection
    rs.MoveFirst

    Do While Not rs.EOF

        ID = Trim(rs!ID)
        Name = Trim(rs!Name)
        address = Trim(rs!Email)
        Age = Trim(rs!Age)
        Department = Trim(rs!Department)

        html = html & "<tr>"
        html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & ID & "</td>"
        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>"

        rs.MoveNext
    Loop

    html = html & "</table></div></body></html>"
    BuildHtmlBody = html

End Function

Above codes can generate a HTML table based on current Recordset data like this:

access html template

Now we can add another function like this:

Public Sub SendHtmlMailFromAccess()

    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 MS Access and VBA"

    ' Use a body template to build body text based on recipient's name
    bodyTemplate = BuildHtmlBody()

    Dim rs, sql
    Set rs = CreateObject("ADODB.Recordset")

    sql = Me.RecordSource
    If Me.FilterOn Then
        sql = sql & " WHERE " & Me.Filter
    End If

    rs.Open sql, CurrentProject.Connection
    rs.MoveFirst

    Dim emailSent
    emailSent = 0

    Do While Not rs.EOF

        Name = Trim(rs!Name)
        address = Trim(rs!Email)
        body = Replace(bodyTemplate, "{name}", Name)

        If Not SendMailTo(sender, Name, address, subject, body, bodyFormat) Then
            Exit Sub
        End If

        emailSent = emailSent + 1
        rs.MoveNext

    Loop

    SysCmd acSysCmdSetStatus, "Total " & emailSent & " email(s) sent."

End Sub

Please also change btnSEnd_Click() codes like this:

Private Sub btnSend_Click()
    btnFocus.SetFocus
    btnCancel.Enabled = True
    btnSend.Enabled = False

    ' SendMailFromAccess
    SendHtmlMailFromAccess

    btnCancel.Enabled = False
    btnSend.Enabled = True
End Sub

Note

Remarks: All of samples in this section are based on first section: Send email from MS Access 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 Codes

Close VBA IDE and back to Access, double click this form to display the form, and then click Send Mail.

You will see the status and result at Access status bar.

access status bar

Send Email based on Data Filter

Because above VBA codes use current Access Data Connection and Recordset, so you can send email to specified recipients based on Access Filter.

Go to Menu -> Home -> Filter Tab -> Advanced -> Advanced Filter/Sort

  • Field -> Department
  • Criteria -> IT
  • Close Filter Settings
  • Click Toggle Filter

You can see the form only displays the persons in IT department. If you click Send Email, only person in IT department will receive the email.

access filter

Download Sample MS Access VBA Project

You can download Sample.accdb here, all codes in this tutorial are included. Please enable Macro if you open this database, otherwise the codes cannot be executed.

access enable macro

Important

You need to access the Trust Center in the Access Options dialog box. Click the Microsoft Office Button, and then click Access 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 Access VBA project. By default, Macro is disabled by Access. At next section, I will introduce how to send email from VBScript directly without Access Macro.

Appendix

See Also