Use PowerShell to Data Mine Your Outlook Inbox

Summary: Microsoft Scripting Guy, Ed Wilson, shows how to use Windows PowerShell to data mine your Microsoft Outlook Inbox.

  Hey, Scripting Guy! I was talking to my boss the other day, and he made a rather interesting observation. He said that if I send four or five emails to a person within a four- or five-minute period, I should probably have picked up the phone and made a telephone call—it would have taken less time, and been more efficient. You know, I believe my boss is completely correct (at least on this one particular point). I was then wondering how much time I spend emailing people who have no impact on my job. I mean, the people on my team with whom I am supposed to collaborate should be my most frequent contacts. Nevertheless, I am beginning to suspect that is not the case. I am afraid that I am wasting too much time corresponding with people who have no impact on my job performance at all. These time wasters are leeching resources from my team at a time when we are already shorthanded. However, I need documentation to take back to my boss for proof. Can I use Windows PowerShell to query my Microsoft Outlook Inbox to retrieve with whom I am in most frequent contact? Information about frequency of contact would be nice to know, but right now I need something actionable to take to the boss.

—JB

Hello JB,

Microsoft Scripting Guy, Ed Wilson, is here. In the week following the North American TechEd 2011 event in Atlanta, Georgia, my routine still eludes me. There is still a ton of email in my Inbox crying for attention, and last week’s meetings are clamoring for my time to reschedule. The nice thing about my Windows 7 phone is that I was able to answer all the really important stuff last week when I was at TechEd. For me, the best thing about TechEd is the chance to meet with people and to talk about Windows PowerShell. It is fantastic to meet people who have been reading the Hey! Scripting Guy Blog for years and to talk to people about their scripting needs.

One of the really cool things that happened is Microsoft Windows PowerShell MVP, Shane Hoey (one of the master minds behind Dr. Scripto TV), sent the Scripting Wife and I a couple bags of Tim Tams via Steve Molkington (aka “The Molk”). Here is a picture of the handover.

And so, I am watching email on my laptop, monitoring Twitter on one monitor, and playing around with the Outlook automation model on another monitor. Meanwhile, I am munching on a fresh Tim Tam and sipping a cup of Earl Grey tea…yes, it looks like I am getting back into a routine after all.

Rather than write a Windows PowerShell script that is limited in the way it exposes data, I decided to write a function that returns a custom object from the Inbox. The easiest way to work with the function is via the Windows PowerShell ISE. I open the file that contains the Get-OutlookInbox function and run it once inside the Windows PowerShell ISE. This places the function onto the function drive and makes it available to me within my Windows PowerShell ISE session.

I then go to the command window, and I type my commands. The first thing I do is call the Get-OutlookInbox function and store the output in a variable called $inbox. This allows me to work with the Inbox content in an easy fashion without needing to query the Inbox over and over again. I can then pipe the $inbox contents to other Windows PowerShell cmdlets to process the results. This technique is shown in the in the following image.

An example of looking in the Inbox for a list of users to whom I correspond is shown here. I first store the email items from my Inbox into a variable, and then I pipe the Inbox information to the Group-Object cmdlet to group the emails together. I then sort them by the frequency of contact.

$inbox = Get-OutlookInBox

$inbox | Group-Object -Property senderName -NoElement | Sort-Object count

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

If you need to see which email in the Inbox was received after a specific date, you can pipe the Inbox email to Where-Object and examine the ReceivedTime property. The cool thing about this code is that Windows PowerShell automatically converts the string “5/23/11” into a DateTime object. Remember that I stored the results of the Get-OutlookInbox function into the $inbox variable.

$inbox | where-object { $_.ReceivedTime -gt "5/23/11" }

If I am curious to see if I have received any email about the 2012 Scripting Games, I can quickly do a search for the number 2012 in the subject. This is shown here.

$inbox | Where-Object { $_.subject -match '2012'}

Remember, that I am working interactively in the command window in my Windows PowerShell ISE. I might not really want to type full command names. Instead, I may prefer to type short commands (aliases). For example, I might want to shorten the command that I used to group and sort the users. This is the long form of the command.

$inbox = Get-OutlookInBox

$inbox | Group-Object -Property senderName -NoElement | Sort-Object count

A shorter form of the command is shown here. You will notice that I do not store the results into a variable. In addition, I use the group alias for Group-Object and a partial parameter name no (for NoElement).

Get-OutlookInBox | group sendername -no | sort count

As you can see in the following image, the command and output work well.

The Get-OutlookInbox function is shown here:

Get-OutlookInBox function

Function Get-OutlookInBox

{

  <#

   .Synopsis

    This function returns InBox items from default Outlook profile

   .Description

    This function returns InBox items from default Outlook profile. It

    uses the Outlook interop assembly to use the olFolderInBox enumeration.

    It creates a custom object consisting of Subject, ReceivedTime, Importance,

    SenderName for each InBox item.

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

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

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

    results into a variable, and using that.

   .Example

    Get-OutlookInbox |

    where { $_.ReceivedTime -gt [datetime]"5/5/11" -AND $_.ReceivedTime -lt `

    [datetime]"5/10/11" } | sort importance

    Displays Subject, ReceivedTime, Importance, SenderName for all InBox items that

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

   .Example

    Get-OutlookInbox | Group-Object -Property SenderName | sort-Object Count

    Displays Count, SenderName and grouping information for all InBox items. The most

    frequently used contacts appear at bottom of list.

   .Example

    $InBox = Get-OutlookInbox

    Stores Outlook InBox items into the $InBox variable for further

    "offline" processing.

   .Example

    ($InBox | Measure-Object).count

    Displays the number of messages in InBox Items

   .Example

    $InBox | where { $_.subject -match '2011 Scripting Games' } |

     sort ReceivedTime -Descending | select subject, ReceivedTime -last 5

    Uses $InBox variable (previously created) and searches subject field

    for the string '2011 Scripting Games' it then sorts by the date InBox.

    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 ReceivedTime

    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-OutlookInbox

    AUTHOR: ed wilson, msft

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

    KEYWORDS: Microsoft Outlook, Office

    HSG: HSG-05-26-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::olFolderInBox)

 $folder.items |

 Select-Object -Property Subject, ReceivedTime, Importance, SenderName

} #end function Get-OutlookInbox

The complete Get-OutlookInbox function is uploaded to the Scripting Guys Script Repository. This makes for an easy download, and it will ensure that you do not pick up any unwanted HTML or other transient stray characters on your clipboard.

JB, that is all there is to using Windows PowerShell to manipulate your Microsoft Outlook Inbox data. This also concludes my discussion about using Windows PowerShell with Microsoft Outlook. Join me tomorrow when I will discuss using the Get-Random cmdlet. It is a cool article, and not just for beginners. Be sure you do not miss the article. I boil down a multiline confusing script into a single line of Windows PowerShell code.

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