VBA insert Access table into Email Body

VBA insert Access table into Email Body

The below VBA will insert a Access Table into the body of an email message using an HTML table.  This will loop a designated Access table to create the HTML table for the body of the email.

 

Make sure to enable Microsoft Outlook 14.0 Object Library in the VBA window -> Tools -> References.

' Insert Access table in outlook Acccess Database
 Dim objOutlook As New Outlook.Application
 Dim objEmail As Outlook.MailItem
 Dim strLtrContent As String

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)

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;'>"
 
 html = html & "<thead><tr>"
 html = html & "<th>Name</th>"
 html = html & "<th>Email</th>"
 html = html & "<th>Phone</th>"
 html = html & "<th>Address</th>"
 html = html & "</thead></tr>"
 While Not rs.EOF
 html = html & "<tbody><tr>"
 html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("Field1") & "</td>"
 html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("Field2") & "</td>"
 html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("Field3") & "</td>"
 html = html & "</tbody></tr>"
 rs.MoveNext
 Wend
 html = html & "</table></div></body></html>"
 
 Set objEmail = objOutlook.CreateItem(olMailItem)
 objEmail.Subject = "Our address has changed."
 objEmail.HTMLBody = html
 objEmail.Display
'----'

Interested in learning new skills?