Email with Signature-
VBA

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.

Watch the step by step video tutorial:

 

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

Bulk Auto Emailer Template
Bulk Auto Emailer Template

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

Sample Email with HTML Body and Signature
Sample Email with HTML Body and Signature

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

Click here to download the practice file.

 

 

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com