Send Email from Excel with Excel VBA

Objective:

We are going to use VBA (Visual Basic for Applications) within Excel to send emails automatically through Outlook. This process can be useful in automating tasks such as sending reports or notifications from Excel directly.

Pre-requisite:

1. Outlook Setup: Ensure that Microsoft Outlook is installed and configured on your system. This code will use Outlook to send emails.
2. Security Settings: Ensure macros are enabled in Excel. You might need to adjust the security settings to allow macros to run.

Step-by-Step Guide:

1. Enable Outlook Reference in VBA:

Before writing the code, make sure you set the reference to Outlook in your VBA environment. This step ensures that Excel VBA can interact with Outlook.

• Open Excel.
• Press ALT + F11 to open the VBA editor.
• Click Tools in the menu and then select References.
• In the dialog box that appears, search for Microsoft Outlook XX.0 Object Library (where XX is the version number, e.g., 16.0 for Office 2016).
• Check the box next to this reference to enable it.
• Click OK to confirm.

2. Write the VBA Code to Send an Email:

Now you are ready to write the VBA code. The code below will send an email using Outlook, and it includes several customizable parameters like the subject, body, recipient, and attachment.

Sub SendEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim EmailSubject As String
    Dim EmailBody As String
    Dim EmailTo As String
    Dim EmailCC As String
    Dim EmailBCC As String
    Dim AttachmentPath As String
    ' Create a new Outlook application instance
    Set OutlookApp = CreateObject("Outlook.Application")   
    ' Create a new mail item (Email)
    Set OutlookMail = OutlookApp.CreateItem(0)  ' 0 represents a Mail Item   
    ' Set the parameters of the email
    EmailSubject = "Your Subject Here"
    EmailBody = "Hello," & vbCrLf & vbCrLf & _
               "This is an automated email sent from Excel." & vbCrLf & _
               "Please find the details below." & vbCrLf & _
               "Best regards," & vbCrLf & _
               "Your Name"
    EmailTo = "recipient@example.com"  ' Add the recipient's email address
    EmailCC = ""  ' CC Email address (optional)
    EmailBCC = ""  ' BCC Email address (optional)
    AttachmentPath = "C:\path\to\your\attachment.xlsx"  ' Optional attachment   
    ' Compose the email
    With OutlookMail
        .Subject = EmailSubject
        .Body = EmailBody
        .To = EmailTo  ' Recipient email
        .CC = EmailCC  ' CC email
        .BCC = EmailBCC  ' BCC email       
        ' Attach a file (if needed)
        If Len(AttachmentPath) > 0 Then
            .Attachments.Add AttachmentPath
        End If      
        ' Send the email
        .Send
    End With
    ' Clean up objects
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing   
    MsgBox "Email has been sent successfully!", vbInformation
End Sub

Explanation of the Code:

1. Declare Objects:
   • OutlookApp: An object to hold the Outlook application instance.
   • OutlookMail: An object to represent the email we are creating.
   • EmailSubject, EmailBody, EmailTo, etc.: These variables will hold the details of the email such as the subject, body text, recipient address, etc.
2. Create Outlook Application Object:
   • Set OutlookApp = CreateObject(« Outlook.Application »): This line creates an instance of Outlook that we can interact with.
3. Create a Mail Item:
   • Set OutlookMail = OutlookApp.CreateItem(0): This creates a new email item (message) within Outlook. The parameter 0 represents a standard email item.
4. Set Email Parameters:
   • The next few lines assign values to various properties like subject, body text, and recipient emails. You can customize these variables to fit your needs.
   • EmailTo represents the recipient’s email address.
   • EmailCC is optional for sending a copy of the email to other people.
   • EmailBCC is optional for sending a blind copy.
   • AttachmentPath holds the full path of any file you want to attach to the email. If no attachment is needed, leave it as an empty string «  ».
5. Compose the Email:
   • Using the With OutlookMail block, we specify all the details of the email such as the subject, body, recipient, etc. The .Send method sends the email.
6. Clean up Objects:
   • Set OutlookMail = Nothing and Set OutlookApp = Nothing clean up the Outlook objects after the email has been sent.
7. Confirmation:
   • MsgBox « Email has been sent successfully! »: A message box pops up to confirm that the email has been sent.

Optional Customizations:

• Adding multiple recipients: You can separate multiple email addresses by a semicolon:
• EmailTo = « recipient1@example.com; recipient2@example.com »
• Formatting the Email Body (HTML): If you want to send an HTML email with formatting like bold, italics, colors, etc., you can set the .HTMLBody property instead of .Body.
• .HTMLBody = « <html><body><b>This is a bold text</b><br><i>This is italicized text</i></body></html> »
• Adding Multiple Attachments: If you have multiple files to attach, you can add more .Attachments.Add lines:
• .Attachments.Add « C:\path\to\file1.xlsx »
• .Attachments.Add « C:\path\to\file2.xlsx »

Final Thoughts:

• Security Warning: If you’re running this script on a network or with a security-focused environment, some anti-virus software or security settings may block automatic sending of emails. It’s essential to check those settings if you encounter errors.
• Outlook Configuration: This method assumes that Outlook is installed and configured on the machine running the VBA script. If you’re using a different email client (like Gmail), you would need a different approach.

This VBA code will help you automate the process of sending emails from Excel, making repetitive tasks easier and more efficient.