Send Email through MS Excel file using VBA Macro script

In this article, I would like to share how we can send an email through Microsoft Excel using Macro script. You can add an attachment, subject and body content using macro script.

Step 1: Configure the email address(From email address which you would want to send from) in the Microsoft Outlook.

Step 2: Open Microsoft Excel, Enter the From and To email address in below format.

Step 3: Goto Developer Tab -> View Code

Step 4: VBA editor will open up, click on “Module 1” on left side explorer,

Step 5:

Copy the below VBA code snippet and paste it in the editor

Option Explicit
Public Pws As Worksheet, ParticipantName As String

Sub sendCertEMail()
Dim count As Integer, NoOfParticipants As Integer, ToEmail As String, ASMEmail As String

Set Pws = Worksheets("Phase")
NoOfParticipants = 3 'Set the no. of participants to send the certificate

For count = 2 To NoOfParticipants
ParticipantName = Pws.Range("A" & count).Value
ToEmail = Pws.Range("B" & count).Value
ASMEmail = Pws.Range("C" & count).Value

'Send email
Call Send_Mail(ASMEmail, ParticipantName, ToEmail)
Next count

MsgBox Title:="Task Box", Prompt:="Email sent successfully!"

End Sub

Private Sub Send_Mail(ASMEmail As String, ParticipantName As String, ToEmail As String)
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
strbody = "<html><p>Dear " & ParticipantName & ",</p>" & _
"<p>This is body message.</p>" & _
"<p>Best Regards,<br/>Admin</p>"

.To = ToEmail
.CC = ASMEmail
.Subject = "Subject line"
.HTMLBody = strbody
.Display
End With
On Error GoTo 0

Set OutApp = Nothing
Set OutMail = Nothing
End Sub

Step 6: Run Macro