Macro 85: Mailing the Active Workbook as an Attachment
The most fundamental Outlook task you can perform through automation is sending an e-mail.
In the sample code shown here, the active workbook is sent to two e-mail recipients as an
attachment.
How it works
Because this code will be run from Excel, we need to set a reference to the Microsoft Outlook Object
Library. We can set the reference by opening the Visual Basic Editor in Excel and selecting
Tools➜References. Scroll down until you find the entry Microsoft Outlook XX Object Library, where
the XX is your version of Outlook. Select the check box next to the entry.
Sub Macro85()
‘Step 1: Declare our variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
‘Step 2: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
‘Step 3: Build our mail item and send
With OLMail
.To = “admin@datapigtechnologies.com; mike@datapigtechnologies.com”
.CC = “”
.BCC = “”
.Subject = “This is the Subject line”
.Body = “Sample File Attached”
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
‘Step 4: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
1. Step 1 first declares two variables. OLApp is an object variable that exposes the Outlook
Application object. OLMail is an object variable that holds a mail item.
2. Step 2 activates Outlook and starts a new session. Note that we use OLApp.Session.
Logon to log on to the current MAPI (Messaging Application Programming Interface) ses-
sion with default credentials. It also creates a mail item. This is equivalent to selecting the
New Message button in Outlook.
3. Step 3 builds the profile of our mail item. This includes the To recipients, the CC recipients,
the BCC recipients, the Subject, the Body, and the Attachments. This step notes that the
recipients are entered in quotes and separates recipients with a semicolon. The standard
syntax for an attachment is .Attachments.Add “File Path”. Here in this code, we
specify the current workbook’s file path with the syntax ActiveWorkbook.Fullname.
This sets the current workbook as the attachment for the e-mail. When the message has
been built, we use the .Display method to review the e-mail. We can replace .Display
with .Send to automatically fire the e-mail without reviewing.
4. Releasing the objects assigned to our variables is generally good practice. This reduces the
chance of any problems caused by rouge objects that may remain open in memory. As we can
see in the code, we simply set variable to Nothing.
How to use it
To implement this macro, we can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created module.
