Send Email With HTML Tables

How to Automatically Send Emails with HTML Table using Excel Macros

Pre-requisites: Outlook Configured

Note:On the VBA editor Go To Tools/References and select "Microsoft Outlook object <version> library"

Sample vba code below.

Sub sendEmailsWithHTMLTables() Dim objOutlook As Object Set objOutlook = CreateObject("Outlook.Application") strSubject = "Latest Data" strBody = "<html>" strBody = strBody & "Hi All<br><br>" strBody = strBody & "Please find the latest data given below<br>" strTable = "<br><table border=2><tbody>" strTable = strTable & "<tr>" strTable = strTable & "<th align=center>Item Name</th>" strTable = strTable & "<th align=center>Quantity</th>" strTable = strTable & "<th align=center>Cost Per Unit</th>" strTable = strTable & "<th align=center>Total Cost</th>" strTable = strTable & "</tr>" intRows = ThisWorkbook.Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row For intRowNo = 2 To intRows strItemName = Trim(ThisWorkbook.Sheets("Data").Range("B" & intRowNo).Text) strQuantity = Trim(ThisWorkbook.Sheets("Data").Range("C" & intRowNo).Text) strCostPerUnit = Trim(ThisWorkbook.Sheets("Data").Range("D" & intRowNo).Text) strTotalCost = Trim(ThisWorkbook.Sheets("Data").Range("E" & intRowNo).Text) strTable = strTable & "<tr><td>" & strItemName & "</td>" strTable = strTable & "<td>" & strQuantity & "</td>" strTable = strTable & "<td>" & strCostPerUnit & "</td>" strTable = strTable & "<td>" & strTotalCost & "</td></tr>" Next strTable = strTable & "</tbody></table><br>" strBody1 = "<br>Regards<br>" strBody1 = strBody1 & "AAAAA<br>" strHTML = strBody & strTable & strBody1 & "</html>" Set objEmail = objOutlook.CreateItem(olMailItem) With objEmail .To = "validEmailID" .Subject = strSubject .htmlBody = strHTML .Send End With End Sub