outlook users

Get Outlook recipients information via VBA (Outlook users data)

Being a corporate Consultant I always thought of the vast amount of data stored in various corporate databases that could be put to good use. Lot of data is stored and is accessible via Outlook – although mostly not appreciated.

I once received an assignment which required analyzing the data of all employees in our company. One way to approach this assignment was to contact HR, which would then need to contact regional HR … and so on. A process that would last at least a week before I could get my hands on the actual data. And even then this would require verifying the data and some cleansing. The other easier way – the corporate Outlook correspondence group containing all active employees and to somehow dump this data to an Excel/CSV file for analysis. Hence, I conducted a short research resulting in the following VBA function:

VBA to extract Outlook recipient information

Here is the VBA code:

Sub DumpOutlookData()
    Dim NewMail As Outlook.MailItem
    Set NewMail = Application.ActiveInspector.CurrentItem
    Dim s As String, filePath As String
    s = ""
    On Error Resume Next
    For Each Recipient In NewMail.Recipients
      s = s & Recipient.AddressEntry.GetExchangeUser.Alias
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.Name
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.JobTitle
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.City
      s = s & ";" & Recipient.AddressEntry.GetExchangeUser.Department & vbNewLine
    Next Recipient
    Set FSO = CreateObject("Scripting.FileSystemObject")
    filePath = Date & "-Outlook Data.txt"
    Set oFile = FSO.CreateTextFile(filePath)
    oFile.WriteLine s
    oFile.Close
    Call MsgBox("Saved to: " & filePath)
End Sub

Notice that what it does is locate the current open email and inspect its recipients. Next it traverses the whole list of recipients and dumps that data to your default VBA directory.

How to dump Outlook recipient information – Step by step

To make sure you are using the code above correctly be sure to follow the instructions below:

Insert the function to your Outlook Visual Basic project

Go to Developer ribbon tab and select Visual Basic. In the VBE (Visual Basic Editor) right click on your VBA Project and select New Module. Insert the macro above.

Add recipients to Outlook draft email

Open a new email message in Outlook and add the desired recipients to the To: section e.g. if adding a correspondence group be sure to expand the list to list all recipients individually. This is an important step – be sure to list INDIVIDUAL recipients as the macro will not expand any group emails

Execute the macro

Go to Developer ribbon tab, select Macros and run the DumpOutlookData procedure. Wait for it to dump the data to the txt file (to your default folder – Documents, read more how to change default directory.). This might take some time depending on how many email recipients you want to dump to your file.
outlook recipients dump
You can also extend the macro to get additional information on Outlook recipients like:

  • Manager (Recipient.AddressEntry.Manager.Alias)
  • Office Location (Recipient.AddressEntry.GetExchangeUser.OfficeLocation)
  • Phone Numbers (Recipient.AddressEntry.GetExchangeUser.MobileTelephoneNumber )

..and other information.

Where to go from here?

Ok so you got the data in a semicolon ; separated CSV file. What next?

Cool ideas to utilize this data

There are tons of useful things you can do with this Outlook recipient information. Here are just some:

Hopefully you see what an incredible resource everyone in a company has – which you can utilize for tons of internal company projects without needing to approach HR.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.