Skip to content Email Automation Excel
Certainly! Below is a detailed example of VBA code for email automation in Excel. This example assumes you want to send an email with a summary report as an attachment to a specified list of recipients.
Content: Email automation in Excel VBA allows you to streamline communication by automatically sending emails with relevant information. In this example, we’ll create a VBA script that sends an email containing a summary report as an attachment to a predefined list of recipients.
Sub EmailAutomation()
' Declare variables
Dim outlookApp As Object
Dim outlookMail As Object
Dim wsSummary As Worksheet
Dim fileName As String
Dim recipientList As String
Dim subject As String
Dim body As String
' Set references
Set wsSummary = ThisWorkbook.Sheets("SummaryReport") ' Change "SummaryReport" to your summary sheet name
' Define the attachment file name
fileName = ThisWorkbook.Path & "\SummaryReport_" & Format(Now, "yyyymmdd_hhmmss") & ".pdf"
' Save the summary report as a PDF file
wsSummary.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName, Quality:=xlQualityStandard
' Define recipients, subject, and email body
recipientList = "email@example.com" ' Add your email recipients, separated by semicolons
subject = "Monthly Summary Report"
body = "Dear Team," & vbCrLf & vbCrLf & "Please find attached the monthly summary report."
' Create Outlook objects
Set outlookApp = CreateObject("Outlook.Application")
Set outlookMail = outlookApp.CreateItem(0)
' Compose the email
With outlookMail
.To = recipientList
.Subject = subject
.Body = body
.Attachments.Add fileName ' Attach the summary report
.Send ' Uncomment this line to send the email automatically
End With
' Display a completion message
MsgBox "Email automation completed! Check your Outlook outbox for the email."
' Clean up - delete the PDF file
Kill fileName
End Sub
In this example, the code generates a PDF file from the “SummaryReport” sheet, creates an Outlook email, attaches the PDF, and optionally sends the email automatically. Adapt the code to your specific needs and update the recipient list accordingly.
Certainly! If you want to send emails directly through Gmail without using Outlook, you can use the “CDO.Message” object. This method requires enabling “CDO.Message” in the References.
Here’s an example VBA code Gmail Email VBA
Sub SendEmailDirectGmail()
Dim iMsg As Object
Dim iConf As Object
Dim Flds As Object
' Create the Email message
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields
' Set up configuration fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your@gmail.com" ' Replace with your Gmail address
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword" ' Replace with your Gmail password
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Update
End With
' Create the email message
With iMsg
Set .Configuration = iConf
.To = "recipient@example.com" ' Replace with the recipient's email address
.Subject = "Subject of your email"
.TextBody = "Body of your email"
.Send
End With
' Release resources
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
End Sub