Bulk Auto Emailer with Picture Signature and HTML body

In the article, we have created a Bulk Auto Emailer with Picture Signature and HTML body. We have given the 2 form control option button to select the send or display Email. This bulk auto emailer will work with Microsoft Outlook.

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

Watch the step by step video tutorial:

Auto Emailer with Picture Signature and HTML body

We have given the Skip column on Column A. You can put “Y” there to skip the email for a particular row.

We have used the HTML tags for the format of the mail body. It will take your pre-defined signature from outlook.

Below is the VBA code we have used to create this automation-

Option Explicit

Sub Send_Email_with_Signature()

Dim Outlook_App As Object
Dim msg As Object
Dim sign As String
Dim i As Integer
Set Outlook_App = CreateObject("Outlook.Application")
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")

For i = 3 To sh.Range("B" & Application.Rows.Count).End(xlUp).Row

If sh.Range("A" & i).Value = "" Then ''Check Skip

Set msg = Outlook_App.CreateItem(0)

With msg
.display
End With

sign = msg.htmlbody

With msg
.To = sh.Range("C" & i).Value
.Subject = "Payment Reminder"
.htmlbody = "Dear <b>" & sh.Range("B" & i).Value & "</b>,<br><br><p>Please pay your bill for below given service(s)- </p>" & _
"<ul>" & _
IIf(sh.Range("D" & i).Value <> "", "<li><b style='color:DodgerBlue'><u>" & sh.Range("D2").Value & ":</u></b> " & Format(sh.Range("D" & i).Value, "0.0") & " is pending.</li>", "") & _
IIf(sh.Range("E" & i).Value <> "", "<li><b style='color:Tomato;'><u>" & sh.Range("E2").Value & ":</u></b> " & Format(sh.Range("E" & i).Value, "0.0") & " is pending.</li>", "") & _
IIf(sh.Range("F" & i).Value <> "", "<li><b style='color:green;'><u>" & sh.Range("F2").Value & ":</u></b> " & Format(sh.Range("F" & i).Value, "0.0") & " is pending.</li>", "") & _
IIf(sh.Range("G" & i).Value <> "", "<li><b style='color:Orange;'><u>" & sh.Range("G2").Value & ":</u></b> " & Format(sh.Range("G" & i).Value, "0.0") & " is pending.</li>", "") & _
IIf(sh.Range("H" & i).Value <> "", "<li><b style='color:Blue;'><u>" & sh.Range("H2").Value & ":</u></b> " & Format(sh.Range("H" & i).Value, "0.0") & " is pending.</li>", "") & _
"</ul>" & _
sign

If sh.Range("H1").Value = 1 Then ''' check option button value
.send
Else
.display
End If
End With

Set msg = Nothing

End If

Next i


Set Outlook_App = Nothing

If sh.Range("H1").Value = 1 Then MsgBox "Done"

End Sub

See our other Email automation templates:

• Send WhatsApp and Email without VBA
• Bulk Auto Emailer with Picture Signature and HTML body
• Send bulk emails using VBA and Outlook
• Email Data Consolidation Tool in Excel
• Automated Bulk Emails Form in Excel VBA
• VBA: Send Email With Outlook Mail body Snapshot