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.
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:
- Create an Organisation chart (Org Chart)
- Measure employee retention over time – extract the data at least on a monthly basis and ther you go. You have yourself enough information to count the employee retention – for the entire company, your department etc.
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.
