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
    End With
    On Error GoTo 0
    Set OutApp = Nothing
    Set OutMail = Nothing
    End Sub

    Step 6: Run Macro

    Voila, Email will send to your recipient email address.

    Bhuvan Balasubramanian
    Follow me
    Latest posts by Bhuvan Balasubramanian (see all)

    Leave a Reply

    Your email address will not be published. Required fields are marked *