Bob van Leeuwen, known as @Capibaro on Twitter, is looking for a way to harvest email addresses from an Outlook appointment or meeting. Bob’s goal is to import the addresses into another application. While it’s simple to copy the names (i.e. copy the To line) it’s not as simple to copy the addresses.
The only solution I know for collecting those addresses is to use a small script. The code for this is below. As you can see it’s very simple. Here’s how it works. It starts by getting the open or selected appointment. Next, it loops through the recipients collection getting each one’s address. The addresses are kept in a memory variable until the script has read them all. The final step is to open an Outlook post item and insert the collected addresses in the body. Bob can cut and paste the addresses from there into another application.
This code should work with any version of Outlook. Because the script reads addresses it will trigger Outlook’s built in security in Outlook 2003 and earlier. If that happens, then Bob will have to respond to a dialog-box warning him that a script is accessing his mailbox.
These are the instructions Bob will have to follow to add this code to Outlook.
- Start Outlook
- Click Tools > Macro > Visual Basic Editor
- If not already expanded, expand Microsoft Office Outlook Objects
- If not already expanded, expand Modules
- Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
- Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
- Click the diskette icon on the toolbar to save the changes
- Close the VB Editor
Sub HarvestAddresses() Dim olkAppt As Outlook.AppointmentItem, _ olkRpnt As Outlook.Recipient, _ olkPost As Outlook.PostItem, _ strAddresses As String Select Case TypeName(Application.ActiveWindow) Case "Explorer" Set olkAppt = Application.ActiveExplorer.Selection(1) Case "Inspector" Set olkAppt = Application.ActiveInspector.CurrentItem End Select For Each olkRpnt In olkAppt.Recipients strAddresses = strAddresses & olkRpnt.Address & vbCrLf Next Set olkPost = Application.CreateItem(olPostItem) olkPost.Subject = "Harvested Addresses" olkPost.Body = strAddresses olkPost.Display Set olkAppt = Nothing Set olkRpnt = Nothing Set olkPost = Nothing End Sub
If Bob also needs to harvest addresses from messages as well as appointments, then he can accomplish that with a small modification. If Bob’s interested, then he can post a comment and I’ll explain how. Ditto for anyone who reads this post.