Home>Blogs>VBA>Bulk Auto Emailer with Picture Signature and HTML body
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.

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.

Auto Emailer with Picture Signature and HTML body
Auto Emailer with Picture Signature and HTML body

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

See our other Email automation templates:

 

 

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com