Harvesting Addresses from an Outlook Meeting or Appointment


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.

  1. Start Outlook
  2. Click Tools > Macro > Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. If not already expanded, expand Modules
  5. 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.
  6. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Click the diskette icon on the toolbar to save the changes
  8. 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.

Advertisements

9 comments on “Harvesting Addresses from an Outlook Meeting or Appointment

  1. Pingback: Harvesting All Email Addresses From an Outlook Mailbox | TechnicLee

  2. Hi, John.

    The code works fine in Outlook 2010. The problem is most likely a result of cutting and pasting between the internet and Outlook. Look at line 13. If you see an ampersand followed by the word “amp” and a semi-colon, then that’s the problem. On a web page that represents an ampersand. Outlook sees it as garbage and throws the error. Change any occurrences of that character string to an actual ampersand and you should be in business.

  3. I am using Outlook 2010, 32-bit version. I tried implementing the code above, however I got a pop-up message indicating a syntax error on line# 13:

    strAddresses = strAddresses & olkRpnt.Address & vbCrLf

    The debugger came on and line# 13 was highlighted in red. However the pop-up error message did not indicate where the problem or syntax error was.

    Can you provide any suggestions or pointers on how to handle this?
    Have you tested the above code with Outlook 2010 and found it to work?
    Thanks.

  4. Hi! I would love to know how to harvest email addresses from an email! I just discovered you today. Thanks for the code for autoreminders for appointments. 🙂 Cheers from down the tobacco road!

    • Hi!

      It’s always good to meet someone in the tobacco road area. To harvest address from an email instead of an appointment, simply change AppointmentItem to MailItem on line #2. Of course this is the quick way. In the long-run the variable names should be adjusted for clarity’s sake.

      Hope that helps.

      Happy New Year!

      David

  5. I want to harvest addresses from all fields (To: Cc:, etc.)in a messages. Often a message field shows only a Display name. How do I modify your script to harvest addresses from all message fields even from fields containing Display names?

    • Hi, Noel.

      Sorry to have taken so long to respond to your post. The script already harvests those addresses. The code doesn’t actually pay attention to what you see in the To, CC, or BCC lines. Each of those entries is represented by a recipient object. The script reads the underlying address property from that recipient object. So, even if the display name says “Bill Smith” the code will read Bill’s address(e.g. Bill.Smith@company.com).

      Does that help?

      Happy New Year!

      David

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s