Macro 86: Mailing a Specific Range as Attachment
You may not always want to send your entire workbook through e-mail. This macro demonstrates
how to send a specific range 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 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 Macro86()
‘Step 1: Declare our variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
‘Step 2: Copy range, paste to new workbook, and save it
Sheets(“Revenue Table”).Range(“A1:E7”).Copy
Workbooks.Add
Range(“A1”).PasteSpecial xlPasteValues
Range(“A1”).PasteSpecial xlPasteFormats
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 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.
The macro then saves 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. This step notes that the
recipients are entered in quotes and separates recipients by a semicolon.
Here 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,
Step 5 deletes 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. In Step
6, 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.
