Everyone I’ve Sent Mail To

For a farewell mail to my colleagues at Microsoft (I’ve decided to move on, but will continue contributing to this blog), I was worried I might miss someone.   Using the arguable presumption that the “important people” were those who I’d sent mail to, I figured I would write an Outlook macro to go through my Sent Items and pull out all the email addresses.  That turned out to be pretty straightforward; here is the code (I don't claim this is the best way to do it, but it does work):

Sub GetSentItems()

Dim eml As MailItem
Dim nsMyNameSpace As NameSpace
Dim colSentItems As Items
Dim objItem As Object
Dim fnum As Long

 
fnum = FreeFile()
Open "c:\users\xxxx\documents\recipients.txt" For Output As #fnum

Set nsMyNameSpace = Application.GetNamespace("MAPI")
Set colSentItems = nsMyNameSpace.GetDefaultFolder(olFolderSentMail).Items
For Each objItem In colSentItems
    For Each objRecip In objItem.Recipients
                Write #fnum, objRecip.Name & “;”
    Next
Next
Close #fnum

End Sub

This creates a text file in my documents folder called recipients.txt (note: substitute your user name for “xxxx” in the path).  It then navigates the Outlook namespace to find the Sent Items folder and gets all the items in there (in colSentItems – col for collection).

It then iterates that collection, pulling out from each item (which represents a sent mail message) the recipients collection – remember an email may have been sent to more than one person or group.

It iterates the recipients and pulls out for each the name.

Note that to run this you may have to tweak the Outlook macro security in Tools / Macro / Security… to allow unsigned macros to run (either “warnings for all macros” or the “No security check” (be sure to set this back)).  You’ll have to exit Outlook to get the new security setting to take effect.

Then run it using Tools / Macro / Macros and it will generate the file.

The problem is that you probably have a lot of duplicates since there are some people to whom you send mail frequently.  Excel can help out here.

Open the recipients.txt file in Excel and use Data / Remove Duplicates to eliminate those.  You can then sort the list and go through and pick out any names you don’t want.  I used Column B in Excel to put an “X” in the ones I wanted to send mail to, then when I was done sorted by column B, selected all those items and copied to the clipboard.

Finally, back in Outlook, create an Outlook mail message and just paste all the names you copied from Excel into the BCC field.  Note that there is a “;” at the end of each name to provide a separator for Outlook.