Chapter 9
In This Chapter
Emailing your workbooks as attachments
Emailing specific sheets or ranges as attachments
Sending emails to all addresses in your contact list
Saving email attachments to a specified folder
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 chapter, I show you a few examples of how you can integrate Excel and Outlook in a more automated fashion.
The most fundamental Outlook task you can perform through automation is sending an email. In the sample code in this section, the active workbook is sent to two email recipients as an attachment.
Because this code will be run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose 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 Macro1()
'Step 1: Declare your variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
'Step 2: Open Outlook and start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 3: Build your 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
Step 1 declares two variables. OLApp is an Object variable that exposes the Outlook Application object, and OLMail is an Object variable that holds a mail item.
Step 2 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials. Step 2 also creates a mail item, similar to manually selecting the New Message button in Outlook.
Step 3 builds the profile of your mail item, including the To recipients, CC recipients, BCC recipients, subject, body, and attachments. Note that the recipients are entered in quotes and separates recipients with a semicolon. The standard syntax for an attachment is .Attachments.Add “File Path”. In this code, you specify the current workbook’s file path with the syntax ActiveWorkbook.Fullname, effectively setting the current workbook as the attachment for the email. When the message has been built, you use the .Display method to review the email. You can replace .Display with .Send to automatically fire off the email without reviewing.
Releasing the objects assigned to your variables is generally good practice to reduce the chance of any problems caused by rouge objects that may remain open in memory. As you can see in Step 4, you simply set the variable to Nothing.
To implement this macro, you can copy and paste it into a standard module:
You may not always want to send your entire workbook through email. The macro in this section demonstrates how to send a specific range of data rather than the entire workbook.
Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools ⇒ References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.
Sub Macro1()
'Step 1: Declare your 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 and start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 4: Build your 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
Step 1 declares two variables. OLApp is an Object variable that exposes the Outlook Application object, and OLMail is an Object variable that holds a mail item.
Step 2 copies a specified range and pastes the values and formats to a temporary Excel file. The macro then saves the temporary file, giving it a file path and filename.
Step 3 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials Step 3 also creates a mail item, similar to manually selecting the New Message button in Outlook.
Step 4 builds the profile of the mail item, including the To recipients, CC recipients, BCC recipients, subject, body, and attachments. Note that the recipients are entered with quotes and separated with a semicolon.
You specify your newly created temporary Excel file path as the attachment for the email. When the message has been built, you use the .Display method to review the email. You can replace .Display with .Send to automatically fire off the email without reviewing it.
You don’t want to leave temporary files hanging out there, so after the email has been sent, Step 5 deletes the temporary Excel file you created.
It is generally good practice to release the objects assigned to your variables to reduce the chance of any problems caused by rouge objects that may remain open in memory. In Step 6, you simply set the variable to Nothing.
To implement this macro, you can copy and paste it into a standard module:
The macro in this section demonstrates how you would send a specific worksheet of data rather than the entire workbook.
Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools ⇒ References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.
Sub Macro1()
'Step 1: Declare your 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 and start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 4: Build your 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
Step 1 first declares two variables. OLApp is an Object variable that exposes the Outlook Application object, and OLMail is an Object variable that holds a mail item.
Step 2 copies a specified worksheet and pastes the values and formats to a temporary Excel file. You then save the temporary file, giving it a file path and filename.
Step 3 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials. You also create a mail item. Step 3 also creates a mail item, similar to manually selecting the New Message button in Outlook.
Step 4 builds the profile of the mail item, including the To recipients, CC recipients, BCC recipients, subject, body, and attachments. The recipients are entered in quotes and separated by a semicolon.
In this code, you specify your newly created temporary Excel file path as the attachment for the email. When the message has been built, you use the .Display method to review the email. You can replace .Display with .Send to automatically fire off the email without reviewing it.
You don’t want to leave temporary files hanging out there, so after the email has been sent, you delete the temporary Excel file you created.
It is generally good practice to release the objects assigned to your variables to reduce the chance of any problems caused by rouge objects that may remain open in memory. As you can see in the code, you simply set the variable to Nothing.
To implement this macro, you can copy and paste it into a standard module:
Sometimes, you don’t need to send an attachment. Instead, you simply want to send an automated email with a link to a file. The macro in this section does just that.
Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools ⇒ References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.
Sub Macro1()
'Step 1: Declare your variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
'Step 2: Open Outlook and start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 3: Build your 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
Step 1 declares two variables. OLApp is an Object variable that exposes the Outlook Application object, and OLMail is an Object variable that holds a mail item.
Step 2 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials. This step also creates a mail item. Step 2 also creates a mail item, similar to manually selecting the New Message button in Outlook.
Step 3 builds the profile of your mail item, including the To recipients, CC recipients, BCC recipients, subject, and HTMLBody.
To create the hyperlink, you need to use the HTMLBody property to pass HTML tags. You can replace the file path address shown in the macro with the address for your file. Note that this macro uses the .Display method, which opens the email for your review. You can replace .Display with .Send to automatically fire off the email without reviewing it.
It is generally good practice to release the objects assigned to your variables to reduce the chance of any problems caused by rouge objects that may remain open in memory. In Step 4, you simply set the variable to Nothing.
To implement this macro, you can copy and paste it into a standard module:
Ever need to send out a mass mailing such as a newsletter or a memo? Instead of manually entering each contact's email address, you can run the following macro. In this procedure, you send one email, automatically adding all the email addresses in your contact list to the email.
Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools ⇒ References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.
Sub Macro1()
'Step 1: Declare your 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
Step 1 declares four Object variables: OLApp exposes the Outlook Application object, OLMail holds a mail item, MyCell holds an Excel range, and MyContacts holds an Excel range.
Step 2 points the MyContacts variable to the range of cells that contain your email addresses. You'll be looping through this range of cells to add email addresses to your email.
Step 3 activates Outlook and starts a new session. Note that you use OLApp.Session.Logon to log in to the current MAPI session with default credentials. You also create a mail item. Step 3 also creates a mail item, similar to manually selecting the New Message button in Outlook.
Step 4 builds the profile of your mail item. Note that you are looping through each cell in the MyContacts range and adding the contents (which are email addresses) to BCC. You use the BCC property instead of To or CC so that each recipient gets an email that looks as though it was sent only to him or her. Your recipients won't be able to see the other email addresses because they are sent with BCC (Blind Courtesy Copy).
This macro uses the .Display method, which opens the email for your review. You can replace .Display with .Send to automatically fire off the email without reviewing.
It is generally good practice to release the objects assigned to your variables to reduce the chance of any problems caused by rouge objects that may remain open in memory. In Step 5, you simply set the variable to Nothing.
To implement this macro, you can copy and paste it into a standard module:
You may often find that certain processes lend themselves to the exchange of data by email. For example, you may send a budget template out for each branch manager to fill out and send back to you by email. Well, if there are 150 branch members, it could be a bit of a pain to save all those email attachments.
The following macro demonstrates one solution to this problem. In this procedure, you use automation to search for all attachments in the inbox and save them to a specified folder.
Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools ⇒ References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.
Sub Macro1()
'Step 1: Declare your 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 your inbox
Set ns = GetNamespace("MAPI")
Set MyInbox = ns.GetDefaultFolder(olFolderInbox)
'Step 3: Check for messages in your 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 and 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
Step 1 declares five variables. ns is an object that exposes the MAPI namespace. MyInbox exposes the target mail folder. MItem exposes 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.
Step 2 sets the MyInbox variable to point to the inbox for the default mail client.
Step 3 performs a quick check to make sure that the inbox contains messages. If there are no messages, the macro exits the procedure and displays a message box stating that there are no messages.
Step 4 creates a directory to hold the attachments you find. Although you could use an existing directory, creating a directory specifically for the attachments you save is usually best. Here, you create that directory on the fly. You use On Error Resume Next to ensure that the code does not error out if the directory you're trying to create already exists.
Step 5 starts the loop through each mail item in the target mail folder.
Step 6 ensures that each mail item you loop through is checked for attachments. As you loop, you save each attachment in the specified directory you created.
Step 7 loops back to Step 5 until there are no more mail items to go through.
Releasing the objects assigned to your variables is good general practice because it reduces the chance of any problems caused by rogue objects that may remain open in memory. Step 8 simply sets the variable to Nothing.
To implement this macro, you can copy and paste it into a standard module:
In the preceding macro, you used automation to search for all attachments in your inbox and to save them to a specified folder. However, in most situations, you probably want to save only certain attachments, such as attachments attached to emails that contain a certain subject. In this example, you get a demonstration of how to check for certain syntax and selectively save attachments.
Because this code is run from Excel, you need to set a reference to Microsoft Outlook Object Library. Open Visual Basic Editor in Excel and choose Tools ⇒ References. Scroll down to the Microsoft Outlook xx Object Library entry, where the xx is your version of Outlook. Select the check box next to the entry.
Sub Macro1()
'Step 1: Declare your 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 your inbox
Set ns = GetNamespace("MAPI")
Set MyInbox = ns.GetDefaultFolder(olFolderInbox)
'Step 3: Check for messages in your 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 attachment with a log number
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
Step 1 first declares six variables. ns is an object that exposse the MAPI namespace. MyInbox exposes the target mail folder. MItem exposes 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 that each attachment is saved as a unique name.
Step 2 sets the MyInbox variable to point to the inbox for your default mail client.
Step 3 performs a quick check to make sure that your inbox contains messages. If there are no messages, it exits the procedure and displays a message box stating that there are no messages.
Step 4 creates a directory to hold the attachments you find. Note that it uses On Error Resume Next to ensure that the code does not error out if the directory you're trying to create already exists.
Step 5 starts the loop through each mail item in the target mail folder.
In Step 6, you use the Instr function to check whether the string “Data Submission” is in the subject line of the email. If that string does not exist, you don't care about any attachments to that message. Therefore, you force the code to go to the SkipIt reference (in Step 8). The line of code immediately following the SkipIt reference is essentially a Move Next command, telling the procedure to move to the next mail item.
Step 7 loops through and saves each attachment in the specified directory you created. You add a running integer to the name of each attachment to ensure that each one is saved as a unique name, to avoid overwriting attachments.
Step 8 loops back to Step 5 until there are no more mail items to go through.
Releasing the objects assigned to your variables is generally good practice because it reduces the chance of any problems caused by rouge objects that may remain open in memory. In Step 9, you simply set the variable to Nothing.
To implement this macro, you can copy and paste it into a standard module:
3.142.173.89