Macro 87: Mailing a Single Sheet as an Attachment
This example demonstrates how we would send a specific worksheet of data rather than the entire
workbook.
How it works
Because this code is 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 we find the entry Microsoft Outlook XX Object Library, where
the XX is your version of Outlook. Place a check in the check box next to the entry.
Sub Macro87()
‘Step 1: Declare our variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
‘Step 2: Copy Worksheet, paste to new workbook, and save it
Sheets(“Revenue Table”).Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & “\TempRangeForEmail.xlsx”
‘Step 3: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
‘Step 4: 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 (ThisWorkbook.Path & “\TempRangeForEmail.xlsx”)
.Display
End With
‘Step 5: Delete the temporary Excel file
ActiveWorkbook.Close SaveChanges:=True
Kill ThisWorkbook.Path & “\TempRangeForEmail.xlsx”
‘Step 6: 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 copies a specified range and pastes the values and formats to a temporary Excel file.
We then save that temporary file, giving it a file path and filename.
3. Step 3 activates Outlook and starts a new session. Note that we use OLApp.Session.
Logon to log on to the current MAPI session with default credentials. We also create a mail
item. This is equivalent to selecting the New Message button in Outlook.
4. Step 4 builds the profile of the mail item. This includes the To recipients, the CC recipients,
the BCC recipients, the Subject, the Body, and the Attachments. The recipients are entered in
quotes and separated by a semicolon.
In this code, we specify our newly created temporary Excel file path 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.
5. We don’t want to leave temporary files hanging out there, so after the e-mail has been sent,
we delete the temporary Excel file we created.
6. It is generally good practice to release the objects assigned to our variables. 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.
