{"id":64083,"date":"2007-09-06T01:53:00","date_gmt":"2007-09-06T01:53:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/09\/06\/how-can-i-list-the-addresses-in-my-sent-items-folder-in-an-excel-spreadsheet\/"},"modified":"2007-09-06T01:53:00","modified_gmt":"2007-09-06T01:53:00","slug":"how-can-i-list-the-addresses-in-my-sent-items-folder-in-an-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-list-the-addresses-in-my-sent-items-folder-in-an-excel-spreadsheet\/","title":{"rendered":"How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, RD. As a matter of fact, yes, we are<\/I> having problems focusing on work today. Granted, that\u2019s true most<\/I> days. However, at least today it\u2019s 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<\/I> the fact that Florida State lost, well, could the weekend have been<\/I> any better?<\/P>\n

OK, true. But it\u2019s unlikely that the University of Oregon will be shut down any time soon.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. This is about the time the Scripting Editor slips in an editor\u2019s note stating that the Scripting Guy who writes this column is just joking, and that he really has nothing against the University of Oregon. Don\u2019t listen to her! This is also about the time when she says something like, \u201cRemember, despite what you might think, very few people in the world care about college football.\u201d We repeat: don\u2019t listen to her<\/I>. After all, the Scripting Editor thinks that car racing<\/I> is a sport. Car racing?!? Next she\u2019ll be telling us that soccer<\/I> is a sport! (<\/I>Ed<\/I>itor\u2019s Note: <\/I>It is.)<\/I><\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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\u2019s a problem: how can we ever find anything to equal the excitement of this past weekend? That\u2019s a tough question to answer. Fortunately, though, we did<\/I> 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\u2019s Sent Items folder!<\/P>\n

What\u2019s that? You say that\u2019s nowhere near<\/I> as exciting as Appalachian State beating Michigan? That\u2019s because we forgot to mention one thing: this script also writes those email addresses to an Excel spreadsheet!<\/P>\n

Ah, yes; we figured that<\/I> would get your heart pounding:<\/P>

Const olSentMail = 5<\/p>\n
Set objDictionary = CreateObject(“Scripting.Dictionary”)\nSet objExcel = CreateObject(“Excel.Application”)<\/p>\n
objExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
Set objOutlook = CreateObject(“Outlook.Application”)\nSet objNamespace = objOutlook.GetNamespace(“MAPI”)\nSet objFolder = objNamespace.GetDefaultFolder(olSentMail)<\/p>\n
Set colItems = objFolder.Items<\/p>\n
For Each objItem in colItems\n    Set colRecipients = objItem.Recipients\n    For Each objRecipient in colRecipients\n        strAddress = objRecipient.Address\n        If Not objDictionary.Exists(strAddress) Then\n            objDictionary.Add strAddress, strAddress\n        End If\n    Next\nNext<\/p>\n
i = 1<\/p>\n
For Each strKey in objDictionary.Keys\n    objWorksheet.Cells(i, 1) = strKey\n    i = i + 1\nNext\n<\/PRE>\n
Let\u2019s see if we can figure out how this script works. (If this gets too<\/I> 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\u2019ll 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<\/B> object and the Excel.Application<\/B> object. It should be fairly obvious what we need the Excel.Application object for; after all, we are<\/I> planning to write the retrieved email addresses to an Excel spreadsheet. But why in the world<\/I> do we need an instance of the Dictionary object? Well, sit tight; we\u2019ll explain that in just a moment.<\/P>\n
And no, that\u2019s 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.<\/P>\n
With our first two objects in hand we turn our attention to a pair of setup chores. First, we set the Visible<\/B> property of our Excel object to True; that\u2019s 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:<\/P>
Set objWorkbook = objExcel.Workbooks.Add\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
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<\/B> object, then use the GetNamespace<\/B> method to bind to the MAPI namespace. (As we\u2019ve noted before, this is the only<\/I> 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\u2019re connected to the MAPI namespace we then bind to the Sent Items folder by calling the GetDefaultFolder<\/B> method, passing the constant olSentMail as a way to tell the script which Outlook folder we want to bind to:<\/P>
Set objFolder = objNamespace.GetDefaultFolder(olSentMail)\n<\/PRE>\n
Granted there\u2019s a little typing involved here, but the first 9 or 10 lines of code in this script should be pretty straightforward.<\/P>\n
Now it\u2019s 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:<\/P>
Set colItems = objFolder.Items\n<\/PRE>\n
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:<\/P>
For Each objItem in colItems\n<\/PRE>\n
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<\/B> 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\u2019t<\/I> include a property that indicates who the message was sent to. <\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. So is that the first time the Scripting Guys were ever wrong about something? To the best of our recollection, yes, it is.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
As it turns out, in order to find out who we sent an email message to we need to access the email\u2019s Recipients<\/B> collection, something we do with this line of code:<\/P>
Set colRecipients = objItem.Recipients\n<\/PRE>\n
And because this is<\/I> 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\u2019s what this line of code is for:<\/P>
For Each objRecipient in colRecipients\n<\/PRE>\n
So then what do we do inside this<\/I> loop? Well, the first thing we do is grab the value of the Address<\/B> property and assign it to a variable named strAddress:<\/P>
strAddress = objRecipient.Address\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Incidentally, when you try to access the Address property Outlook will pop up a security dialog box that says, hey, someone is trying to access email addresses, is that OK with you? You must<\/I> check the Allow Access for<\/B> box and then click Yes<\/B> in order for the script to grab those email addresses; if you click No<\/B> or if you try to ignore the dialog box your script will fail. This means that you can\u2019t run this script in fully-automated fashion; if you start the script but don\u2019t stick around long enough to address the dialog box the script will fail.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
The next part of the script is optional, but \u2013 in our opinion \u2013 kind of handy. The odds are pretty good that you\u2019re going to have duplicate recipients in your Sent Items folder; if you\u2019ve sent 10 messages to kenmyer@fabrikam.com then you\u2019re 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 \u2013drumroll, please \u2013 the Dictionary object!<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. OK, so maybe revealing the use of the Dictionary object isn\u2019t quite as suspenseful and exciting as we thought it would be. But, then again, maybe that\u2019s your<\/I> fault for assuming that there could be suspense and excitement in a daily column about system administration scripting.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
So how<\/I> are we going to use the Dictionary object? Like this:<\/P>
If Not objDictionary.Exists(strAddress) Then\n    objDictionary.Add strAddress, strAddress\nEnd If\n<\/PRE>\n
What we\u2019re doing here is using the Exists<\/B> method to determine whether the recipient\u2019s email address (represented by the variable strAddress) already exists as a Dictionary key. If the address is already in the Dictionary then we don\u2019t do anything; that\u2019s because, well, because the address is already in the Dictionary. (Besides, the Dictionary object doesn\u2019t allow for duplicate keys.) If the address doesn\u2019t<\/I> exist in the Dictionary, then we use the Add<\/B> method to add the address, using the variable strAddress as both the key and the item value:<\/P>
objDictionary.Add strAddress, strAddress\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. You say you don\u2019t know what we mean when we throw out terms like \u201ckey\u201d and \u201citem value?\u201d Then you need to take a look at this section<\/B><\/A> of the Microsoft Windows 2000 Scripting Guide<\/I>, or the Sesame Script article<\/B><\/A> on using the Dictionary object.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After we\u2019ve 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\u2019re 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:<\/P>
i = 1<\/p>\n
For Each strKey in objDictionary.Keys\n    objWorksheet.Cells(i, 1) = strKey\n    i = i + 1\nNext\n<\/PRE>\n
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\u2019ll 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:<\/P>
For Each strKey in objDictionary.Keys\n<\/PRE>\n
So then what is this<\/I> loop for? That\u2019s 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 \u2013 well, you can probably figure out for yourself what we\u2019ll end up with when all of this is done: a spreadsheet containing all the email addresses extracted from messages in the Sent Items folder.<\/P>\n
That should do it, RD. If you\u2019re not totally obsessed with college football (although we don\u2019t consider watching a game on Friday, two games on Saturday, another game on Sunday, and two more on Monday to be any kind of \u201cobsession\u201d) 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<\/B> of <\/B>Idaho<\/B> trivia note<\/B>: If you go to the University of Idaho bookstore, you can buy a T-shirt that says \u201cBoise is not a state.\u201d) That should be good news for those of you who aren\u2019t<\/I> 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<\/I> that one \u2026.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s true most days. However, at least today it\u2019s understandable, […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,212,49,3,5],"class_list":["post-64083","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-microsoft-outlook","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019s true most days. However, at least today it\u2019s understandable, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64083","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64083"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64083\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64083"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64083"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64083"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}