Did you know that you probably integrate Excel and Outlook all the time? It's true. If you've sent or received an Excel workbook through Outlook, you've integrated the two programs; albeit manually. In this Part, we show you a few examples of how you can integrate Excel and Outlook in a more automated fashion.
Note that the macros in this Part automate Microsoft Outlook. For these macros to work, you need to have Microsoft Outlook installed on your system.
The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.
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.
Some of you may notice that we are not using the SendMail command native to Excel. With the SendMail command, you can send simple e-mail messages directly from Excel. However, the SendMail command is not as robust as Outlook automation. SendMail does not allow you to attach files, or use the CC and BCC fields in the e-mail. This makes the technique used by this macro a superior method.
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 = “[email protected]; [email protected]”
.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) session 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.
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 = “[email protected]; [email protected]”
.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.
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 = “[email protected]; [email protected]”
.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.
Macro 88: Send Mail with a Link to Our Workbook
Sometimes, you don't need to send an attachment at all. Instead, you simply want to send an automated e-mail with a link to a file. This macro does just that.
Note that your users or customers will have to have at least read access to the network or location that is tied to the link.
How it works
Keep in mind that 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 we 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 Macro88()
‘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 = “[email protected]; [email protected]”
.CC = “”
.BCC = “”
.Subject = “Monthly Report Email with Link”
.HTMLBody = _
“<p>Monthly report is ready. Click to Link to get it.</p>” & _
“<p><a href=” & Chr(34) & “Z:DownloadsMonthlyReport.xlsx” & _
Chr(34) & “>Download Now</a></p>”
.Display
End With
‘Step 4: 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 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. This step 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, and the HTMLBody.
To create the hyperlink, we need to use the HTMLBody property to pass HTML tags. We can replace the file path address shown in the macro with the address for our file. Note this macro is using the .Display method, which opens the e-mail for our review. We can replace .Display with .Send to automatically fire the e-mail without reviewing.
4. 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 4, 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 on our keyboard.
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.
Macro 89: Mailing All E-Mail Addresses in Our Contact List
Ever need to send out a mass mailing such as a newsletter or a memo? Instead of manually entering each of our contacts' e-mail address, we can run the following procedure. In this procedure, we send out one e-mail, automatically adding all the e-mail addresses in our contact list to our e-mail.
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 we 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 Macro89()
‘Step 1: Declare our variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
Dim MyCell As Range
Dim MyContacts As Range
‘Step 2: Define the range to loop through
Set MyContacts = Sheets(“Contact List”).Range(“H2:H21”)
‘Step 3: Open Outlook
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
‘Step 4: Add each address in the contact list
With OLMail
For Each MyCell In MyContacts
.BCC = .BCC & Chr(59) & MyCell.Value
Next MyCell
.Subject = “Sample File Attached”
.Body = “Sample file is attached”
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
‘Step 5: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
1. Step 1 declares four variables: OLApp is an object variable that exposes the Outlook Application object. OLMail is an object variable that holds a mail item. MyCell is an object variable that holds an Excel range. MyContacts is an object variable that holds an Excel range.
2. Step 2 points to the MyContacts variable to the range of cells that contains our e-mail addresses. This is the range of cells we loop through to add e-mail addresses to our e-mail.
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 our mail item. We note that we are looping through each cell in the MyContacts range and adding the contents (which are e-mail addresses) to the BCC. Here, we are using the BCC property instead of To or CC so that each recipient gets an e-mail that looks as though it was sent only to him. Our recipients won't be able to see any of the other e-mail addresses because they have been sent with BCC (Blind Courtesy Copy). Note this macro is using the .Display method, which opens the e-mail for our review. We can replace .Display with .Send to automatically fire the e-mail without reviewing.
5. 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 5, we simply set the 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 on our keyboard.
2. Right-click project/workbook name in the Project window.
3. Choose Insert⇒Module.
4. Type or paste the code into the newly created module.
Macro 90: Saving All Attachments to a Folder
You may often find that certain processes lend themselves to the exchange of data via e-mail. For example, you may send a budget template out for each branch manager to fill out and send back to you via e-mail. Well, if there are 150 branch members, it could be a bit of a pain to bring down all those e-mail attachments.
The following procedure demonstrates one solution to this problem. In this procedure, we use automation to search for all attachments in the inbox and save them to a specified folder.
How it works
Because this code will be run from Excel, we need to set a reference to the Microsoft Outlook Object Library. You 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 Macro90()
‘Step 1: Declare our variables
Dim ns As Namespace
Dim MyInbox As MAPIFolder
Dim MItem As MailItem
Dim Atmt As Attachment
Dim FileName As String
‘Step 2: Set a reference to our inbox
Set ns = GetNamespace(“MAPI”)
Set MyInbox = ns.GetDefaultFolder(olFolderInbox)
‘Step 3: Check for messages in our inbox; exit if none
If MyInbox.Items.Count = 0 Then
MsgBox “No messages in folder.”
Exit Sub
End If
‘Step 4: Create directory to hold attachments
On Error Resume Next
MkDir “C:TempMyAttachments”
‘Step 5: Start to loop through each mail item
For Each MItem In MyInbox.Items
‘Step 6: Save each attachment then go to the next attachment
For Each Atmt In MItem.Attachments
FileName = “C:TempMyAttachments” & Atmt.FileName
Atmt.SaveAsFile FileName
Next Atmt
‘Step 7: Move to the next mail item
Next MItem
‘Step 8: Memory cleanup
Set ns = Nothing
Set MyInbox = Nothing
End Sub
1. Step 1 declares five variables. ns is an object used to expose the MAPI namespace. MyInbox is used to expose the target mail folder. MItem is used to expose the properties of a mail item. Atmt is an object variable that holds an Attachment object. FileName is a string variable that holds the name of the attachment.
2. Step 2 sets the MyInbox variable to point to the inbox for the default mail client.
3. Step 3 performs a quick check to make sure there are actually messages in the inbox. If there are no messages, the macro exits the procedure with a message box stating that there are no messages.
4. Step 4 creates a directory to hold the attachments we find. Although you could use an existing directory, using a directory dedicated specifically for the attachments you bring down is usually best. Here, we are creating that directory on the fly. Note we are using On Error Resume Next. This ensures that the code does not error out if the directory we are trying to create already exists.
5. Step 5 starts the loop through each mail item in the target mail folder.
6. Step 6 ensures that each mail item we loop through gets checked for attachments. As we loop, we save each attachment we find into the specified directory we created.
7. Step 7 loops back to Step 5 until there are no more mail items to go through.
8. Releasing the objects assigned to our variables is good general practice. This reduces the chance of any problems caused by rogue objects that may remain open in memory. Step 8 simply sets the 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.
Macro 91: Saving Certain Attachments to a Folder
In the previous procedure, we showed you how to use automation to search for all attachments in your inbox and save them to a specified folder. However, in most situations, you probably only want to save certain attachments; for example, those attachments attached to e-mails that contain a certain Subject. In this example, we get a demonstration of how to check for certain syntax and selectively bring down attachments.
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 we 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 Macro91()
‘Step 1: Declare our variables
Dim ns As Namespace
Dim MyInbox As MAPIFolder
Dim MItem As Object
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer
‘Step 2: Set a reference to our inbox
Set ns = GetNamespace(“MAPI”)
Set MyInbox = ns.GetDefaultFolder(olFolderInbox)
‘Step 3: Check for messages in our inbox; exit if none
If MyInbox.Items.Count = 0 Then
MsgBox “No messages in folder.”
Exit Sub
End If
‘Step 4: Create directory to hold attachments
On Error Resume Next
MkDir “C:OffTheGridMyAttachments”
‘Step 5: Start to loop through each mail item
For Each MItem In MyInbox.Items
‘Step 6: Check for the words Data Submission in Subject line
If InStr(1, MItem.Subject, “Data Submission”) < 1 Then
GoTo SkipIt
End If
‘Step 7: Save each with a log number; go to the next attachment
i = 0
For Each Atmt In MItem.Attachments
FileName = _
“C:TempMyAttachmentsAttachment-” & i & “-” & Atmt.FileName
Atmt.SaveAsFile FileName
i = i + 1
Next Atmt
‘Step 8: Move to the next mail item
SkipIt:
Next MItem
‘Step 9: Memory cleanup
Set ns = Nothing
Set MyInbox = Nothing
End Sub
1. Step 1 first declares six variables. ns is an object used to expose the MAPI namespace. MyInbox is used to expose the target mail folder. MItem is used to expose the properties of a mail item. Atmt is an object variable that holds an Attachment object. FileName is a string variable that holds the name of the attachment. i is an integer variable used to ensure each attachment is saved as a unique name.
2. Step 2 sets the MyInbox variable to point to the inbox for our default mail client.
3. Step 3 performs a quick check to make sure there are actually messages in our inbox. If there are no messages, it exits the procedure with a message box stating that there are no messages.
4. Step 4 creates a directory to hold the attachments we find. Note that it uses On Error Resume Next. This ensures that the code does not error out if the directory we are trying to create already exists.
5. Step 5 starts the loop through each mail item in the target mail folder.
6. In Step 6, we use the Instr function to check whether the string Data Submission is in the Subject line of the e-mail. If that string does not exist, we don't care about any attachments to that message. Therefore, we force the code to go to the SkipIt reference (in Step 8). Because the line of code immediately following the SkipIt reference is essentially a Move Next command, this has the effect of telling the procedure to move to the next mail item.
7. Step 7 loops through and saves each attachment into the specified directory we created. Note that we are adding a running integer to the name of each attachment. This is to ensure that each attachment is saved as a unique name, helping us to avoid overwriting attachments.
8. Step 8 loops back to Step 5 until there are no more mail items to go through.
9. 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. In Step 9, 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.