10.3 Sending Emails with Outlook.

In this section, you'll learn how to automate the process of sending emails using Outlook directly from Excel or any other Office application via VBA. This capability is useful for automating reporting tasks, notifications, or any process that involves sending personalized emails from your application.

VBA provides integration with Outlook, allowing you to create and send emails programmatically, set recipients, subject lines, body content, and even attach files.

1. Setting Up Outlook Automation in VBA

Before automating Outlook, ensure you have the Microsoft Outlook Object Library referenced in your VBA environment.

Steps to Enable Outlook Object Library:

1. Open the VBA editor (press Alt + F11).
2. Go to Tools > References.
3. Scroll down and check Microsoft Outlook xx.x Object Library (where "xx.x" represents the version number).
4. Click OK.

2. Creating an Outlook Application Object

To start automating Outlook, you need to create an instance of the Outlook application using the CreateObject method. This object lets you access various features of Outlook, such as composing and sending emails.

Example: Create Outlook Application Object

Sub CreateOutlookApp()
    Dim OutlookApp As Object
    
    ' Create Outlook application object
    Set OutlookApp = CreateObject("Outlook.Application")
    
    ' Display a message to confirm that Outlook is open
    MsgBox "Outlook is open and ready!"
End Sub

In this example:

• CreateObject("Outlook.Application") creates an instance of Outlook.

3. Sending an Email from Excel Using Outlook

You can use the Outlook.Application object to create an email, define the recipients, subject, body, and attachments, and then send the email.

Example: Sending a Basic Email

Sub SendEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    
    ' Create Outlook application object
    Set OutlookApp = CreateObject("Outlook.Application")
    
    ' Create a new email item
    Set OutlookMail = OutlookApp.CreateItem(0) ' 0 = olMailItem
    
    ' Define the properties of the email
    With OutlookMail
        .To = "recipient@example.com" ' Recipient email address
        .Subject = "Test Email from VBA"
        .Body = "Hello, this is a test email sent from VBA."
        .Send ' Send the email
    End With
    
    ' Display a confirmation message
    MsgBox "Email sent successfully!"
End Sub

In this example:

• OutlookApp.CreateItem(0) creates a new email item.
• .To sets the recipient's email address.
• .Subject and .Body define the subject and body content of the email.
• .Send sends the email.

4. Adding CC, BCC, and Multiple Recipients

You can specify additional recipients by using the CC (carbon copy) and BCC (blind carbon copy) properties. Multiple recipients can be added by separating email addresses with semicolons.

Example: Adding CC and BCC

Sub SendEmailWithCCBCC()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    
    ' Create Outlook application object
    Set OutlookApp = CreateObject("Outlook.Application")
    
    ' Create a new email item
    Set OutlookMail = OutlookApp.CreateItem(0) ' 0 = olMailItem
    
    ' Define the properties of the email
    With OutlookMail
        .To = "recipient@example.com" ' Primary recipient
        .CC = "ccrecipient@example.com" ' CC recipient
        .BCC = "bccrecipient@example.com" ' BCC recipient
        .Subject = "Email with CC and BCC"
        .Body = "This email demonstrates how to use CC and BCC in VBA."
        .Send ' Send the email
    End With
    
    ' Display a confirmation message
    MsgBox "Email with CC and BCC sent successfully!"
End Sub

In this example:

• .CC and .BCC add CC and BCC recipients, respectively.

5. Attaching Files to Emails

You can attach files to an email using the Attachments.Add method. This allows you to send reports, documents, or any files along with the email.

Example: Attaching a File

Sub SendEmailWithAttachment()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    
    ' Create Outlook application object
    Set OutlookApp = CreateObject("Outlook.Application")
    
    ' Create a new email item
    Set OutlookMail = OutlookApp.CreateItem(0) ' 0 = olMailItem
    
    ' Define the properties of the email
    With OutlookMail
        .To = "recipient@example.com" ' Recipient email address
        .Subject = "Email with Attachment"
        .Body = "Please find the attached document."
        
        ' Attach a file
        .Attachments.Add "C:\path\to\your\file.txt"
        
        .Send ' Send the email
    End With
    
    ' Display a confirmation message
    MsgBox "Email with attachment sent successfully!"
End Sub

In this example:

• .Attachments.Add attaches the specified file to the email.

6. Sending HTML Emails

You can send emails with HTML content by using the HTMLBody property instead of the Body property. This allows you to format the email with colors, fonts, and links.

Example: Sending an HTML Email

Sub SendHTMLEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    
    ' Create Outlook application object
    Set OutlookApp = CreateObject("Outlook.Application")
    
    ' Create a new email item
    Set OutlookMail = OutlookApp.CreateItem(0) ' 0 = olMailItem
    
    ' Define the properties of the email
    With OutlookMail
        .To = "recipient@example.com" ' Recipient email address
        .Subject = "HTML Email from VBA"
        
        ' Set the email body as HTML content
        .HTMLBody = "<h2>This is an HTML email</h2>" & _
                    "<p><b>Here is a link to <a href='https://www.example.com'>Example</a></b></p>"
        
        .Send ' Send the email
    End With
    
    ' Display a confirmation message
    MsgBox "HTML email sent successfully!"
End Sub

In this example:

• .HTMLBody is used to insert HTML code into the email body.

7. Handling Errors in Sending Emails

When automating email sending, it's important to handle any errors gracefully, such as invalid email addresses or issues with Outlook.

Example: Error Handling

Sub SendEmailWithErrorHandling()
    On Error GoTo ErrorHandler
    
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    
    ' Create Outlook application object
    Set OutlookApp = CreateObject("Outlook.Application")
    
    ' Create a new email item
    Set OutlookMail = OutlookApp.CreateItem(0) ' 0 = olMailItem
    
    ' Define the properties of the email
    With OutlookMail
        .To = "recipient@example.com" ' Recipient email address
        .Subject = "Email with Error Handling"
        .Body = "This email is being sent with error handling enabled."
        .Send ' Send the email
    End With
    
    MsgBox "Email sent successfully!"
    Exit Sub
    
ErrorHandler:
    MsgBox "Error occurred: " & Err.Description
End Sub

In this example:

• On Error GoTo ErrorHandler directs VBA to jump to the ErrorHandler label in case of an error.
• The error message is displayed using Err.Description.

8. Summary of Sending Emails with Outlook Using VBA

• Creating and sending emails: Use VBA to create and send emails, define recipients, subject, body, and attachments.
• Multiple recipients: Add CC, BCC, and multiple email addresses.
• Attachments: Send files along with emails using the Attachments.Add method.
• HTML content: Send formatted emails using HTML with the HTMLBody property.
• Error handling: Handle any potential errors during email sending for robust code.

Automating the sending of emails with VBA helps streamline communication processes, especially when dealing with multiple recipients or large amounts of data. By using VBA with Outlook, you can easily create customized emails and automate regular tasks, such as sending reports or notifications.