Composing an Email in Excel VBA

All elements of an email can also be created and composed entirely via VBA code. The following example shows how to create an email with recipient, subject, body content, and an attachment, and then send it:

Sub ComposeEmail()
    Dim appOutlook As Outlook.Application
    Dim MailItem As Outlook.MailItem
    ' Start Outlook application
    Set appOutlook = CreateObject("Outlook.Application")
    ' Create a new mail item
    Set MailItem = appOutlook.CreateItem(olMailItem)
    ' Set properties
    MailItem.To = "moyofranck@gmail.com"
    MailItem.Subject = "Test"
    MailItem.Body = "Hello" & vbCrLf & "World"
    ' Add attachment with error handling
    On Error GoTo ErrorHandler
    MailItem.Attachments.Add "C:\Users\POPOLY\Desktop\fleur.jpeg"
    ' Send the email
    MailItem.Send
    ' Quit Outlook and release memory
    appOutlook.Quit
    Set MailItem = Nothing
    Set appOutlook = Nothing
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
    appOutlook.Quit
    Set MailItem = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:

• The variable MailItem is declared as an object of type Outlook.MailItem.
• Using the CreateObject() function, an object of type Outlook.Application is created and a reference to it is returned. This reference is used to interact with the Outlook application.
• The CreateItem() method of the Application object creates a new item—in this case, a mail item. Different types of items have different available properties and methods.
• The properties To, Subject, and Body assign the email recipient, subject, and message body respectively. Multiple recipients can be specified in a single string, separated by semicolons. Unlike the SendMail() method, recipients here are not provided as an array.
• The Attachments collection holds all attachments of the email. The Add() method adds files to this collection. Since the file might not exist, error handling is used to catch such exceptions.
• The Send() method places the email into the Outlook Outbox for sending.
• Alternatively, you could use the Display() method instead of Send(). This shows the email to the user for review and prompts whether to save it. If the user agrees, the email is saved in the Outlook Drafts folder and can be sent later.
• At the end, the Outlook application is closed using the Quit() method.