How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! How can I get a list of all the email addresses in my Sent Items and then write those addresses to an Excel spreadsheet?

— RD

Hey, RD. As a matter of fact, yes, we are having problems focusing on work today. Granted, that’s true most days. However, at least today it’s understandable, coming on the heels of a very exciting (and very enjoyable) opening to the college football season. The fact that our very own Washington Huskies thumped Syracuse 42-12 would have, by itself, made this past weekend a good one. However, when you couple that with the fact that Michigan was upset by Appalachian State, Washington State was thumped by Wisconsin, and the fact that Florida State lost, well, could the weekend have been any better?

OK, true. But it’s unlikely that the University of Oregon will be shut down any time soon.

Of course, the one drawback to having really good things happen (did we mention that Florida State got beat?) is that everything that happens after that tends to pale by comparison. And that’s a problem: how can we ever find anything to equal the excitement of this past weekend? That’s a tough question to answer. Fortunately, though, we did think of one thing that comes awfully close to generating that same level of excitement: a script that can retrieve a list of all the email addresses in Outlook’s Sent Items folder!

What’s that? You say that’s nowhere near as exciting as Appalachian State beating Michigan? That’s because we forgot to mention one thing: this script also writes those email addresses to an Excel spreadsheet!

Ah, yes; we figured that would get your heart pounding:

Const olSentMail = 5

Set objDictionary = CreateObject(“Scripting.Dictionary”)
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
Set objOutlook = CreateObject(“Outlook.Application”)
Set objNamespace = objOutlook.GetNamespace(“MAPI”)
Set objFolder = objNamespace.GetDefaultFolder(olSentMail)
Set colItems = objFolder.Items
For Each objItem in colItems
    Set colRecipients = objItem.Recipients
    For Each objRecipient in colRecipients
        strAddress = objRecipient.Address
        If Not objDictionary.Exists(strAddress) Then
            objDictionary.Add strAddress, strAddress
        End If
    Next
Next
i = 1
For Each strKey in objDictionary.Keys
    objWorksheet.Cells(i, 1) = strKey
    i = i + 1
Next

Let’s see if we can figure out how this script works. (If this gets too exciting for you, please stop reading and consult your doctor before finishing the article.) To begin with, we define a constant named olSentMail and assign this constant the value 5; we’ll use olSentMail to tell the script which Outlook folder we want to work with. After defining the constant we then create two objects: the Scripting.Dictionary object and the Excel.Application object. It should be fairly obvious what we need the Excel.Application object for; after all, we are planning to write the retrieved email addresses to an Excel spreadsheet. But why in the world do we need an instance of the Dictionary object? Well, sit tight; we’ll explain that in just a moment.

And no, that’s not because we have to go ask the Scripting Editor why we need the Dictionary object. This is just a literary device designed to heighten the suspense and keep you on the edge of your seat.

With our first two objects in hand we turn our attention to a pair of setup chores. First, we set the Visible property of our Excel object to True; that’s done simply so we can see our spreadsheet onscreen. After that, we then execute these two lines of code, lines of code that add a new workbook to our instance of Excel and then bind us to the first worksheet in that workbook:

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

Once Excel is ready to go we then need to get Microsoft Outlook prepped and configured. To that end, we create an instance of the Outlook.Application object, then use the GetNamespace method to bind to the MAPI namespace. (As we’ve noted before, this is the only namespace you can bind to. Nevertheless, we still have to include code that calls the GetNamespace method and explicitly does this binding for us.) As soon as we’re connected to the MAPI namespace we then bind to the Sent Items folder by calling the GetDefaultFolder method, passing the constant olSentMail as a way to tell the script which Outlook folder we want to bind to:

Set objFolder = objNamespace.GetDefaultFolder(olSentMail)

Granted there’s a little typing involved here, but the first 9 or 10 lines of code in this script should be pretty straightforward.

Now it’s time to roll up our sleeves and get down to business. To begin with, we use this line of code to retrieve a collection of all the items (that is, all the email messages) found in the Sent Items folder:

Set colItems = objFolder.Items

From there we take the next logical step: we set up a For Each loop to loop us through each of the items in that collection:

For Each objItem in colItems

This is where matters get a tiny bit tricky. When we first sat down to tackle this problem, we assumed that each email message (each instance of an Outlook MailItem object) had some sort of property that indicated who the message was sent to; you know, some property like SentTo or SentToAddress, something like that. Interestingly enough, we were wrong: the MailItem doesn’t include a property that indicates who the message was sent to.

As it turns out, in order to find out who we sent an email message to we need to access the email’s Recipients collection, something we do with this line of code:

Set colRecipients = objItem.Recipients

And because this is a collection (after all, an email can be, and often-times is, addressed to more than one recipient), we need to set up a second For Each loop in order to loop through each recipient in the collection; that’s what this line of code is for:

For Each objRecipient in colRecipients

So then what do we do inside this loop? Well, the first thing we do is grab the value of the Address property and assign it to a variable named strAddress:

strAddress = objRecipient.Address

The next part of the script is optional, but – in our opinion – kind of handy. The odds are pretty good that you’re going to have duplicate recipients in your Sent Items folder; if you’ve sent 10 messages to kenmyer@fabrikam.com then you’re going to end up with 10 instances of kenmyer@fabrikam.com in your Excel spreadsheet. To avoid duplicate entries like that we decided to use –drumroll, please – the Dictionary object!

So how are we going to use the Dictionary object? Like this:

If Not objDictionary.Exists(strAddress) Then
    objDictionary.Add strAddress, strAddress
End If

What we’re doing here is using the Exists method to determine whether the recipient’s email address (represented by the variable strAddress) already exists as a Dictionary key. If the address is already in the Dictionary then we don’t do anything; that’s because, well, because the address is already in the Dictionary. (Besides, the Dictionary object doesn’t allow for duplicate keys.) If the address doesn’t exist in the Dictionary, then we use the Add method to add the address, using the variable strAddress as both the key and the item value:

objDictionary.Add strAddress, strAddress

After we’ve disposed of all the recipients for the first email message, we then loop back around (to the beginning of our first For Each loop) and repeat the process with the next email in the collection. When we’re all done, the Dictionary object will contain a list of unique recipient email addresses, as pulled from all the emails in our Sent Items folder. That means that all we have to do now is write that information to our Excel spreadsheet:

i = 1

For Each strKey in objDictionary.Keys
    objWorksheet.Cells(i, 1) = strKey
    i = i + 1
Next

As you can see, the process of writing this data to Excel begins with assigning the value 1 to a counter variable named i; we’ll use this variable to keep track of the current row in the spreadsheet. We then set up yet another For Each loop, this one designed to walk us through all the keys in our Dictionary object:

For Each strKey in objDictionary.Keys

So then what is this loop for? That’s easy: in this loop we assign the value of the Dictionary key (which, of course, is one of our recipient email addresses) to cell i, 1. The first time through the loop, that means that the email address gets written to cell row 1, column 1; remember, the value of i starts off as 1. We increment i by 1 (ensuring that the next email address will be written in cell row 2, column 1), then loop around and repeat the process with the next key in the Dictionary. When all of this is done – well, you can probably figure out for yourself what we’ll end up with when all of this is done: a spreadsheet containing all the email addresses extracted from messages in the Sent Items folder.

That should do it, RD. If you’re not totally obsessed with college football (although we don’t consider watching a game on Friday, two games on Saturday, another game on Sunday, and two more on Monday to be any kind of “obsession”) we should note that this weekend promises to be another exciting one, starting with the fact that the University of Washington hosts Boise State. (University of Idaho trivia note: If you go to the University of Idaho bookstore, you can buy a T-shirt that says “Boise is not a state.”) That should be good news for those of you who aren’t obsessed with college football: if the Huskies get thumped, the Scripting Guy who writes this column is unlikely to bore you with a play-by-play recap of the game. But if the Huskies should happen to win that one ….

ScriptingGuy1

Follow