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,
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
Voila, Email will send to your recipient email address.