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.
