{"id":13831,"date":"2011-05-25T00:01:00","date_gmt":"2011-05-25T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2011\/05\/25\/use-powershell-to-data-mine-your-outlook-sent-items\/"},"modified":"2011-05-25T00:01:00","modified_gmt":"2011-05-25T00:01:00","slug":"use-powershell-to-data-mine-your-outlook-sent-items","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-to-data-mine-your-outlook-sent-items\/","title":{"rendered":"Use PowerShell to Data Mine Your Outlook Sent Items"},"content":{"rendered":"

Summary<\/b>: Microsoft Scripting Guy, Ed Wilson, teaches you how to use Windows PowerShell to data mine your Sent Items folder in Microsoft Outlook.<\/p>\n

\"Hey, Hey, Scripting Guy! I was wondering if I would use Windows PowerShell to process my Sent Items in Outlook. I mean, I think there is some interesting data I could obtain if I could find an easy way to work with the information. Is this something that can be done with Outlook and with Windows PowerShell?<\/p>\n

—SH<\/p>\n

\"Hey, Hello SH, <\/p>\n

Microsoft Scripting Guy, Ed Wilson, is here. If you use Microsoft Outlook nearly as much as I do, you have an extensive data repository in your Sent Items folder. For example, every Live Meeting I schedule with various user groups comes through Outlook at some time or another. If I want to know how many Live Meetings I participate in during a year, I can search my Outlook Sent Items. <\/p>\n

Because the Sent Items folder contains so much data, and because I may want to parse the data in many different fashions, it is a perfect candidate to turn into a reusable Windows PowerShell function that returns an object. <\/p>\n

The actual code that retrieves the Sent Items is about eight lines long. The remainder of the code is comment-based Help. I am not going to discuss the comment-based Help. However, I will show you a couple of things. After I have loaded the function into memory (in the Windows PowerShell ISE, I run the script file that contains the function and it loads the function for me), I can use the Get-Help<\/b> cmdlet to obtain information about the function. The basic command is shown here:<\/p>\n

\n

Get-Help Get-OutlookSentItems<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

The command and its associated output are shown in the following image.<\/p>\n

\"Image<\/a><\/p>\n

If I need to see the complete Help information, I use the Full<\/b> switch with Get-Help<\/b>. This command is shown here:<\/p>\n

\n

Get-Help –full Get-OutlookSentItems<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

When I have read through the Help and I understand how the function works, I may need to be reminded of helpful syntax. To do this, I use the Examples<\/b> <\/i>switch with the function. This command is shown here:<\/p>\n

\n

Get-Help –examples Get-OutlookSentItems<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

The main working code in the Get-OutlookSentItems<\/b> function loads the interop assembly, and creates the olDefaultFolders enumeration type<\/a>. Next, I create an instance of the Outlook.Application object<\/a>, and connect to the MAPI namespace. These are common steps when working with Microsoft Outlook. This portion of the code is shown here: <\/p>\n

\n

Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null<\/span><\/span><\/span><\/p>\n

 <\/span><\/span>$olFolders = “Microsoft.Office.Interop.Outlook.olDefaultFolders” -as [type] <\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>$outlook = new-object -comobject outlook.application<\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>$namespace = $outlook.GetNameSpace(“MAPI”)<\/span><\/span><\/span><\/span><\/p>\n<\/blockquote>\n

After I have created the objects, enumerations, and interop assemblies, and connected to the MAPI namespace, it is time to call the GetDefaultFolder<\/b> method and connect to the sent mail folder. I store the returned folder object in the $folder<\/b> variable. <\/p>\n

\n

$folder = $namespace.getDefaultFolder($olFolders::olFolderSentMail)<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

Now I call the items<\/b> method from the folder<\/b> object, and I choose the four properties with which I want to work. The Select-Object<\/b> cmdlet creates a custom object for me and returns a custom object for each mail item in the folder. This portion of the script is shown here:<\/p>\n

\n

$folder.items | <\/span><\/span><\/span><\/p>\n

 <\/span><\/span>Select-Object -Property Subject, SentOn, Importance, To<\/span><\/span><\/span><\/span><\/p><\/blockquote>\n

The complete Get-OutlookSentItems<\/b> function is shown here. (I know that copying and pasting from the blog format can often be a challenge—for this reason I uploaded the complete Get-OUtlookSentItems function<\/a> to the Scripting Guys Script Repository).<\/p>\n

\n

Function Get-OutlookSentItems<\/span><\/span><\/span><\/p>\n

{<\/span><\/span><\/span><\/p>\n

  <\/span><\/span><#<\/span><\/span><\/span><\/span><\/p>\n

   <\/span><\/span>.Synopsis<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>This function returns sent items from default Outlook profile<\/span><\/span><\/span><\/span><\/p>\n

   <\/span><\/span>.Description<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>This function returns sent items from default Outlook profile. It<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>uses the Outlook interop assembly to use the olFolderSentMail enumeration.<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>It creates a custom object consisting of Subject, SentOn, Importance, To<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>for each sent item. <\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>*** Important *** depending on the size of your sent items this function<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>may take several minutes to gather your sent items. If you anticipate <\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>doing multiple analysis of the data, you should consider storing the <\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>results into a variable, and using that. <\/span><\/span><\/span><\/span><\/p>\n

   <\/span><\/span>.Example<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>Get-OutlookSentItems | <\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>where { $_.SentOn -gt [datetime]”5\/5\/11″ -AND $_.SentOn -lt `<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>[datetime]”5\/10\/11″ } | sort importance <\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>Displays Subject, SentOn, Importance, To for all sent items that were sent<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>between 5\/5\/11 and 5\/10\/11 and sorts by importance of the email.<\/span><\/span><\/span><\/span><\/p>\n

   <\/span><\/span>.Example<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>Get-OutlookSentItems | Group-Object -Property To | sort-Object Count <\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>Displays Count, To and grouping information for all sent items. The most<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>frequently used contacts appear at bottom of list. <\/span><\/span><\/span><\/span><\/p>\n

   <\/span><\/span>.Example<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>$sentItems = Get-OutlookSentItems<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>Stores Outlook sent items into the $sentItems variable for further<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>“offline” processing.<\/span><\/span><\/span><\/span><\/p>\n

   <\/span><\/span>.Example<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>($sentItems | Measure-Object).count<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>Displays the number of messages in Sent Items<\/span><\/span><\/span><\/span><\/p>\n

   <\/span><\/span>.Example<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>$sentItems | where { $_.subject -match ‘2011 Scripting Games’ } | <\/span><\/span><\/span><\/span><\/p>\n

     <\/span><\/span>sort SentOn -Descending | select subject, senton -last 5 <\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>Uses $sentItems variable (previously created) and searches subject field<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>for the string ‘2011 Scripting Games’ it then sorts by the date sent.<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>This sort is descending which puts the oldest messages at bottom of list.<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>The Select-Object cmdlet is then used to choose only the subject and sentOn<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>properties and then only the last five messages are displayed. These last<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>five messages are the five oldest messages that meet the string. <\/span><\/span><\/span><\/span><\/p>\n

   <\/span><\/span>.Notes<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>NAME:  <\/span>Get-OutlookSentItems<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>AUTHOR: ed wilson, msft<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>LASTEDIT: 05\/10\/2011 08:36:42<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>KEYWORDS: Microsoft Outlook, Office<\/span><\/span><\/span><\/span><\/p>\n

    <\/span><\/span>HSG: HSG-05-25-2011<\/span><\/span><\/span><\/span><\/p>\n

   <\/span><\/span>.Link<\/span><\/span><\/span><\/span><\/p>\n

     <\/span><\/span>Http:\/\/www.ScriptingGuys.com\/blog<\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>#Requires -Version 2.0<\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>#><\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null<\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>$olFolders = “Microsoft.Office.Interop.Outlook.olDefaultFolders” -as [type] <\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>$outlook = new-object -comobject outlook.application<\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>$namespace = $outlook.GetNameSpace(“MAPI”)<\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>$folder = $namespace.getDefaultFolder($olFolders::olFolderSentMail)<\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>$folder.items | <\/span><\/span><\/span><\/span><\/p>\n

 <\/span><\/span>Select-Object -Property Subject, SentOn, Importance, To<\/span><\/span><\/span><\/span><\/p>\n

} #end function Get-OutlookSentItems<\/span><\/span><\/span><\/p><\/blockquote>\n

To use the Get-OutlookSentItems<\/b> function, I call the function and save the results. On my computer, the Get-OutlookSentItems<\/b> function takes a pretty long time to complete. Therefore, I run it only once, and I store the resulting objects into a variable. Here is the code that does that:<\/p>\n

\n

$sentItems = Get-OutlookSentItems<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

I might be interested in how many items I have in my Sent Items folder. I can use the Measure-Object<\/b> cmdlet. I am only interested in the count<\/b> property, so I access it directly as shown here: <\/p>\n

\n

($sentItems | Measure-Object).count<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

On the other hand, because $SentItems<\/b> is a collection of objects, I can use the count<\/b> property from it, and avoid using Measure-Object<\/b>, as shown here:<\/p>\n

\n

$sentItems.count<\/span><\/span><\/span><\/p>\n<\/blockquote>\n

I can also use normal Windows PowerShell cmdlets to analyze the information that is contained in the Sent Items folder. The following code uses the $sentItems<\/b> variable (previously created) and searches the subject<\/b> field for the string ‘2011 Scripting Games’. It then sorts by the date sent. This sort is descending, which puts the oldest messages at bottom of list. The Select-Object<\/b> cmdlet is then used to choose only the subject<\/b> and sentOn<\/b> properties, and then only the last five messages are displayed. These last five messages are the five oldest messages that meet the string. This code is shown here: <\/p>\n

\n

$sentItems | where { $_.subject -match ‘2011 Scripting Games’ } | <\/span><\/span><\/span><\/p>\n

     <\/span><\/span>sort SentOn -Descending | select subject, senton -last 5<\/span><\/span><\/span><\/p><\/blockquote>\n

For ease of use, I uploaded the complete Get-OutlookSentItems function<\/a> to the Scripting Guys Script Repository. <\/p>\n

I invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n

Ed Wilson, Microsoft Scripting Guy<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"

Summary: Microsoft Scripting Guy, Ed Wilson, teaches you how to use Windows PowerShell to data mine your Sent Items folder in Microsoft Outlook. Hey, Scripting Guy! I was wondering if I would use Windows PowerShell to process my Sent Items in Outlook. I mean, I think there is some interesting data I could obtain if […]<\/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":[212,49,3,45],"class_list":["post-13831","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-outlook","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"

Summary: Microsoft Scripting Guy, Ed Wilson, teaches you how to use Windows PowerShell to data mine your Sent Items folder in Microsoft Outlook. Hey, Scripting Guy! I was wondering if I would use Windows PowerShell to process my Sent Items in Outlook. I mean, I think there is some interesting data I could obtain if […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/13831","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=13831"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/13831\/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=13831"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=13831"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=13831"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}