Home
How to Send Email with Table HTML in Excel VBA

How to Send Email with Table HTML in Excel VBA

Excel VBA Macros April 26, 2023

Welcome to Excel Avon

Send Email with Table HTML

DOWNLOAD USED EXCEL FILE FROM HERE>>

In today’s post, we will show you how to send email with table HTML in Excel VBA, in this post we will work with model as in previous post we created with module
We had explained in the previous post that How to Send Email with the Attachment in Excel VBA, this is something like this, there will be data in our worksheet, which we will put in the table properly, we will send it in the email with the help of VBA, that too with formatting. Together, that is, when the data go in the email, it will go along with the table, something like this can be seen in the image below.

Send Email with Table HTML

Send Email with Table HTML in Excel VBA

As we taught you how to insert a module when we were working with modules, as we have learned in many posts so far, you can insert modules. Let us teach you how we insert the module. Let’s understand, then we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as shown in the image below.

sort-data-on-excel-using-VBA

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

Sort-data-on-excel-using-vba

Go to Tools and then select References as shown in the below screenshot. scroll down in the Reference Object library and select “Microsoft Outlook 16.0 Object Library” to make it available for Excel VBA.

Send Email With Outlook automation1

Once the module is inserted, and the library is activated, we’ll write a subroutine to Sub Send HTML Email ().

Sub SendHTMLEmail()
End Sub

Declare the variable i, j and Lr as Long

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim lr As Long
End Sub

We will Declare the variable Outlook Application.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application,
End Sub

Now, to write the email, we declare one more variable as “MailItem”.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
End Sub

declare one more variable WS as Worksheet. with set worksheet as sheet1. Also we will define HTMLText variable for string.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Dim htmlText As String
Set ws = Sheet1
End Sub

Now here we will declare the last row of the table

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row

End Sub

Now here we will give value to ‘j’ which is a range after that we will make a loop for header which will be shown from i will give value in i. Htmltext for table of content and head.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
For i = 1 To 3
Next i
End Sub

Now we will once again write for table row element in htmltext. After the loop is finished, we will also close the table row content code, Use back slash to close the raw HTML content.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
Next i
htmlText = "<table><\thead>"
End Sub

Given cell value with HtmlText for table of Head and then close the table of Head.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"

End Sub

Create another Loop for table of Content.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"

For j = 2 To lr
Next j

End Sub

Here we are using tbody tag because we want body content of HTML table.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"

For j = 2 To lr
Next j

End Sub

We will create another loop here which will be for the data of the cell data table just like the header but you can copy and paste the loop containing the header, just change the HTML element. Put the Body content before the Loop. 

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"
htmlText = htmlText & "<tbody>"
For j = 2 To lr
     htmlText = htmlText & "<tr>"
     For i = 1 To 3
         htmlText = htmlText & "<td>" & WS.Cells(j, i).Value & "</td>"
     Next i
     htmlText = htmlText & "</tr>"
Next j
End Sub

When the table of content loop is complete, we will close the body content code with back slash.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"
htmlText = htmlText & "<tbody>"
For j = 2 To lr
     htmlText = htmlText & "<tr>"
     For i = 1 To 3
         htmlText = htmlText & "<td>" & WS.Cells(j, i).Value & "</td>"
     Next i
     htmlText = htmlText & "</tr>"
Next j
htmlText = htmlText & "</tbody>"
End Sub

Copy and paste the body content code below and then we will close the table by changing the body element

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"
htmlText = htmlText & "<tbody>"
For j = 2 To lr
     htmlText = htmlText & "<tr>"
     For i = 1 To 3
         htmlText = htmlText & "<td>" & WS.Cells(j, i).Value & "</td>"
     Next i
     htmlText = htmlText & "</tr>"
Next j
htmlText = htmlText & "</tbody>"
htmlText = htmlText & "</table>"

End Sub

Now we have some HTML elements which are not closed, we close them (bold)

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"
htmlText = htmlText & "</thead>"

htmlText = htmlText & "<tbody>"
For j = 2 To lr
     htmlText = htmlText & "<tr>"
     For i = 1 To 3
         htmlText = htmlText & "<td>" & WS.Cells(j, i).Value & "</td>"
     Next i
     htmlText = htmlText & "</tr>"
Next j
htmlText = htmlText & "</tbody>"
htmlText = htmlText & "</table>"

End Sub

Here we are going to launch the Outlook application.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"
htmlText = htmlText & "</thead>"

htmlText = htmlText & "<tbody>"
For j = 2 To lr
     htmlText = htmlText & "<tr>"
     For i = 1 To 3
         htmlText = htmlText & "<td>" & WS.Cells(j, i).Value & "</td>"
     Next i
     htmlText = htmlText & "</tr>"
Next j
htmlText = htmlText & "</tbody>"
htmlText = htmlText & "</table>"

Set olApp = New Outlook.Application

End Sub

To launch a new email, we need to set the reference to our previous variable as “mitem.”

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"
htmlText = htmlText & "</thead>"

htmlText = htmlText & "<tbody>"
For j = 2 To lr
     htmlText = htmlText & "<tr>"
     For i = 1 To 3
         htmlText = htmlText & "<td>" & WS.Cells(j, i).Value & "</td>"
     Next i
     htmlText = htmlText & "</tr>"
Next j
htmlText = htmlText & "</tbody>"
htmlText = htmlText & "</table>"

Set olApp = New Outlook.Application
Set mItem = olApp.CreateItem(olMailItem)

End Sub

If we have to send email then we will use ‘to’ property of email. The email that is written is just a dummy email, we cannot show you the real email due to privacy. We also have to include the subject because what we are sending will also be written as subject

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"
htmlText = htmlText & "</thead>"

htmlText = htmlText & "<tbody>"
For j = 2 To lr
     htmlText = htmlText & "<tr>"
     For i = 1 To 3
         htmlText = htmlText & "<td>" & WS.Cells(j, i).Value & "</td>"
     Next i
     htmlText = htmlText & "</tr>"
Next j
htmlText = htmlText & "</tbody>"
htmlText = htmlText & "</table>"

Set olApp = New Outlook.Application
Set mItem = olApp.CreateItem(olMailItem)
mItem.To = "Excelavon@gmail.com"
mItem.Subject = "This is html email"
End Sub

We need to write the email body using HTML body Type. Before sending the email, once we preview how our email will look, so we will use the display command.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"
htmlText = htmlText & "</thead>"

htmlText = htmlText & "<tbody>"
For j = 2 To lr
     htmlText = htmlText & "<tr>"
     For i = 1 To 3
         htmlText = htmlText & "<td>" & WS.Cells(j, i).Value & "</td>"
     Next i
     htmlText = htmlText & "</tr>"
Next j
htmlText = htmlText & "</tbody>"
htmlText = htmlText & "</table>"

Set olApp = New Outlook.Application
Set mItem = olApp.CreateItem(olMailItem)
mItem.To = "Excelavon@gmail.com"
mItem.Subject = "This is html email"
mItem.HTMLBody = htmlText
mItem.Display
End Sub

Click on Run button

Send Email with Table HTML1

After running we will go to outlook application and then there we will see the preview of your created email, here you will see subject ‘This is html email’

Send Email with Table HTML

Back to VBE, here we can send email by replacing display command with send command.

Sub SendHTMLEmail()
Dim i As Long,j As Long
Dim olApp As Outlook.Application
Dim mItem As MailItem
Dim WS As Worksheet
Set ws = Sheet1
lr = WS.Range("A" & Rows.Count).End(xlUp).Row
htmlText = "<table><thead>"
j = 1
htmlText = htmlText & "<tr>"
For i = 1 To 3
    htmlText = htmlText & "<th>" & WS.Cells(j, i).Value & "</th>"
Next i
htmlText = htmlText & "</tr>"
htmlText = htmlText & "</thead>"

htmlText = htmlText & "<tbody>"
For j = 2 To lr
     htmlText = htmlText & "<tr>"
     For i = 1 To 3
         htmlText = htmlText & "<td>" & WS.Cells(j, i).Value & "</td>"
     Next i
     htmlText = htmlText & "</tr>"
Next j
htmlText = htmlText & "</tbody>"
htmlText = htmlText & "</table>"

Set olApp = New Outlook.Application
Set mItem = olApp.CreateItem(olMailItem)
mItem.To = "Excelavon@gmail.com"
mItem.Subject = "This is html email"
mItem.HTMLBody = htmlText
mItem.send
End Sub

Therefore, I hope that you have understood How to Send Email with Table HTML in Excel VBA, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.

LEARN MORE TOPIC IN VBA HERE

DOWNLOAD USED EXCEL FILE FROM HERE>>

You can also see well-explained video here about How to Send Email with Table HTML in Excel VBA 

Related Posts

Leave a Reply