...

MS Excel VBA

 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
  
Make sure to replace “your@gmail.com” and “yourpassword” with your Gmail address and password. Also, replace “recipient@example.com” with the actual email address of the recipient.
Please be cautious when including your email and password in the code. It’s recommended to use an application-specific password if you have two-factor authentication enabled for your Gmail account.

Leave a Reply

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


Scroll to Top