Automate the sending of emails via Outlook directly from Excel VBA

Here’s a detailed VBA code example for. This code uses Microsoft Outlook’s integration with VBA in Excel. You can modify it to suit your specific needs.

Prerequisites:

• Outlook must be installed and configured on your machine.
• You need to enable the « Microsoft Outlook xx.x Object Library » reference in the VBA editor (go to Tools > References and check this library).

Example VBA Code for Automating Email Sending via Outlook:

1. Open the VBA Editor in Excel by pressing Alt + F11.
2. Add a Module by clicking Insert > Module.
3. Copy and paste the following code into the module.

VBA Code:

Sub SendAutomatedEmail()
    ' Declare variables
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim recipient As String
    Dim subject As String
    Dim body As String
    Dim attachmentPath As String
    ' Initialize variables
    recipient = "recipient@example.com" ' Recipient's email address
    subject = "Subject of the email" ' Subject of the email
    body = "Hello," & vbCrLf & vbCrLf & "This is an email sent automatically from Excel using VBA." & vbCrLf & "Best regards," & vbCrLf & "Your Name" ' Email body
    attachmentPath = "C:\path\to\your\attachment.pdf" ' Path to an attachment file (optional)
    ' Create an instance of Outlook
    On Error Resume Next ' If Outlook is already open, don't show an error
    Set OutlookApp = CreateObject("Outlook.Application")
    On Error GoTo 0 ' Return to regular error handling
    ' If Outlook is not open, show an error message
    If OutlookApp Is Nothing Then
        MsgBox "Outlook is not open or installed.", vbCritical
        Exit Sub
    End If
    ' Create a new email
    Set OutlookMail = OutlookApp.CreateItem(0) ' 0 = email
    ' Set up the email
    With OutlookMail
        .To = recipient ' Recipient
        .Subject = subject ' Subject
        .Body = body ' Email body
        If attachmentPath <> "" Then .Attachments.Add attachmentPath ' Add an attachment if specified      
        ' Send the email
        .Send
    End With
    ' Confirmation message
    MsgBox "The email has been successfully sent!", vbInformation
End Sub

Code Explanation:

1. Variable Declarations:
   • OutlookApp: An instance of the Outlook application.
   • OutlookMail: An object representing an email.
   • recipient: The email address of the recipient.
   • subject: The subject of the email.
   • body: The body content of the email.
   • attachmentPath: The path to an optional file attachment.
2. Initializing Variables:
   • The recipient’s email address, subject, and email body are defined here.
   • The attachmentPath variable can be left blank if you don’t want to attach a file.
3. Creating the Outlook Application Instance:
   • CreateObject(« Outlook.Application ») creates an instance of Outlook.
   • If Outlook is not open or installed, the code handles the error and displays a message.
4. Creating the Email:
   • CreateItem(0) creates a new email (0 corresponds to email, 1 would be for an appointment, etc.).
   • The recipient, subject, and body of the email are set.
5. Sending the Email:
   • The .Send method sends the email immediately. If you want to just open the email without sending it, use .Display instead.
6. Adding an Attachment (Optional):
   • If you have a file to attach, you can set its path in the attachmentPath variable. The file will be attached using .Attachments.Add.
7. Confirmation Message:
   • A message box appears after the email is sent, confirming that the email has been sent successfully.

Usage:

• Running the Code: To run this code, you can either press F5 in the VBA editor or link the code to a button in your Excel sheet (using the « Button » form control in Excel).

Customization:

• Recipient: You can retrieve the email address from any Excel cell (e.g., Range(« A1 »).Value).
• Email Body: If you have multiple lines in Excel, you can retrieve the values from cells and insert them into the email body.

Here’s an example of retrieving the recipient and body of the email from Excel cells:

recipient = Range("A1").Value ' Email address from cell A1
body = Range("B1").Value ' Message from cell B1

This code is very flexible and can be adapted for bulk email sending from lists in Excel or automated notifications.