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 I could find an easy way to work with the information. Is this something that can be done with Outlook and with Windows PowerShell?

—SH

Hello SH,

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.

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.

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 cmdlet to obtain information about the function. The basic command is shown here:

Get-Help Get-OutlookSentItems

The command and its associated output are shown in the following image.

If I need to see the complete Help information, I use the Full switch with Get-Help. This command is shown here:

Get-Help –full Get-OutlookSentItems

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 switch with the function. This command is shown here:

Get-Help –examples Get-OutlookSentItems

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

Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null

 $olFolders = “Microsoft.Office.Interop.Outlook.olDefaultFolders” -as [type]

 $outlook = new-object -comobject outlook.application

 $namespace = $outlook.GetNameSpace(“MAPI”)

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

$folder = $namespace.getDefaultFolder($olFolders::olFolderSentMail)

Now I call the items method from the folder object, and I choose the four properties with which I want to work. The Select-Object 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:

$folder.items |

 Select-Object -Property Subject, SentOn, Importance, To

The complete Get-OutlookSentItems 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 to the Scripting Guys Script Repository).

Function Get-OutlookSentItems

{

  <#

   .Synopsis

    This function returns sent items from default Outlook profile

   .Description

    This function returns sent items from default Outlook profile. It

    uses the Outlook interop assembly to use the olFolderSentMail enumeration.

    It creates a custom object consisting of Subject, SentOn, Importance, To

    for each sent item.

    *** Important *** depending on the size of your sent items this function

    may take several minutes to gather your sent items. If you anticipate

    doing multiple analysis of the data, you should consider storing the

    results into a variable, and using that.

   .Example

    Get-OutlookSentItems |

    where { $_.SentOn -gt [datetime]”5/5/11″ -AND $_.SentOn -lt `

    [datetime]”5/10/11″ } | sort importance

    Displays Subject, SentOn, Importance, To for all sent items that were sent

    between 5/5/11 and 5/10/11 and sorts by importance of the email.

   .Example

    Get-OutlookSentItems | Group-Object -Property To | sort-Object Count

    Displays Count, To and grouping information for all sent items. The most

    frequently used contacts appear at bottom of list.

   .Example

    $sentItems = Get-OutlookSentItems

    Stores Outlook sent items into the $sentItems variable for further

    “offline” processing.

   .Example

    ($sentItems | Measure-Object).count

    Displays the number of messages in Sent Items

   .Example

    $sentItems | where { $_.subject -match ‘2011 Scripting Games’ } |

     sort SentOn -Descending | select subject, senton -last 5

    Uses $sentItems variable (previously created) and searches subject 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 cmdlet is then used to choose only the subject and sentOn

    properties and then only the last five messages are displayed. These last

    five messages are the five oldest messages that meet the string.

   .Notes

    NAME:  Get-OutlookSentItems

    AUTHOR: ed wilson, msft

    LASTEDIT: 05/10/2011 08:36:42

    KEYWORDS: Microsoft Outlook, Office

    HSG: HSG-05-25-2011

   .Link

     Http://www.ScriptingGuys.com/blog

 #Requires -Version 2.0

 #>

 Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null

 $olFolders = “Microsoft.Office.Interop.Outlook.olDefaultFolders” -as [type]

 $outlook = new-object -comobject outlook.application

 $namespace = $outlook.GetNameSpace(“MAPI”)

 $folder = $namespace.getDefaultFolder($olFolders::olFolderSentMail)

 $folder.items |

 Select-Object -Property Subject, SentOn, Importance, To

} #end function Get-OutlookSentItems

To use the Get-OutlookSentItems function, I call the function and save the results. On my computer, the Get-OutlookSentItems 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:

$sentItems = Get-OutlookSentItems

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

($sentItems | Measure-Object).count

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

$sentItems.count

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 variable (previously created) and searches the subject 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 cmdlet is then used to choose only the subject and sentOn 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:

$sentItems | where { $_.subject -match ‘2011 Scripting Games’ } |

     sort SentOn -Descending | select subject, senton -last 5

For ease of use, I uploaded the complete Get-OutlookSentItems function to the Scripting Guys Script Repository.

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

Ed Wilson, Microsoft Scripting Guy