Use PowerShell to Data Mine Your Outlook Inbox

Use PowerShell to Data Mine Your Outlook Inbox

  • Comments 20
  • Likes

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

Hey, Scripting Guy! Question  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

Hey, Scripting Guy! Answer 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.

Photo with Tim Tams

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.

Image of command output

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.

Image of command output

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.

Image of command output

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Much cleaner than mine: http://poshcode.org/2695.

  • Any reason why would I be receiving this message when trying to run Add-type -assembly "Microsoft.Office.Interop.Outlook"

    Add-Type : Length cannot be less than zero.

    Parameter name: length

    At line:1 char:9

    + Add-type <<<<  -assembly "Microsoft.Office.Interop.Outlook"

       + CategoryInfo          : NotSpecified: (:) [Add-Type], ArgumentOutOfRangeException

       + FullyQualifiedErrorId : System.ArgumentOutOfRangeException,Microsoft.PowerShell.Commands.AddTypeCommand

    I have Outlook 2010.

  • Are you sure this works with Outlook 2010?  $InBox = Get-OutlookInBox gives an error.  $InBox = .\Get-OutlookInBox gives no error but returns nothing in $InBox.  Please advise.

  • @JD very strange ... not sure why you would get that error. Did you download from the Script Repository, you may have gotten come "crud" when copying from web page.

    @ILGeorge Yes, it works with Outlook 2010 ... I just used it again on my laptop, and I have OL 2010 installed.

  • @JD did you figure this out? I have the exact same error message but the code is

    Add-Type -AssemblyName System.Printing

  • Look in the GAC for the assembly.  It is not installed with office by default in earlier versions.

    You also have to resolve 32/64 bit product and location.

    dir C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Outlook\

    It should look something like this:

    17:34 PS>dir C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Outlook\

       Directory: C:\WINDOWS\assembly\GAC\Microsoft.Office.Interop.Outlook

    Mode                LastWriteTime     Length Name

    ----                -------------     ------ ----

    d----          3/7/2008   5:45 PM            11.0.0.0__71e9bce111e9429c

    d----          6/6/2009   3:06 PM            12.0.0.0__71e9bce111e9429c

    You can see that on this machine I have had both oFfice 11 and Office 12.

    Some of teh limited products will not have the Interop.  Only full products contain it.  Check the installer to see if it is optional.

  • I have 24 Exchange Mail databases. I check external mail flow by sending test email from one external address to a test mailbox on each of these databases. Each of these then sends to another external address using rules in Outlook. What I would like to know is how you can do the above but choosing a profile other than the default Outlook one. I am after automating this by setting up the end external address under Outlook but do not want to have Outlook permenantely using this address as the machine is used for other puposes. Thanks.  

  • Use NameSpace.Logon to select a profile.

    msdn.microsoft.com/.../bb219914(office.12).aspx

  • Thanks a lot for the great info. Will this script work on both 32 and 64 bit operating system?

    I want an accurate code which works on outlook 2010 as well. please send me at smooth.munna@gmail.com

    regards,

    Bhalotia

  • $outlook = new-object -comobject outlook.application

    not working in win7 OL2010

    New-Object : Cannot load COM type outlook.application.

    At line:1 char:22

    + $outlook = new-object <<<<  -comobject outlook.application

       + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException

       + FullyQualifiedErrorId : CannotLoadComObjectType,Microsoft.PowerShell.Commands.NewObjectCommand

    Please let me know if I need to add any other assembly to make this work. Thanks.

  • Hello Sir,

    Is it possible to achieve this functionality using a batch file with command line commands?

    My requirement is to check a folder(not inbox) in outlook for any new mail(i.e,unread), if any then start a program.

    Can this be achieved using command line?

    Please let me know.

    Thanks,

    Manikanta

  • What's about  Outlook when used with multiple Smtp and Exchange accounts ?

    richardspowershellblog.wordpress.com/.../outlook-viewing-mail-items

  • I realize this is an old post but I've been trying to connect to another mailbox (I have 2 mailboxes, and am interested in the non-default one). Does anyone know how to accomplish this? Thanks,

  • i am trying to list all the email subject from sent items and for a particular date range. But it is not printing the recent email subject it only printing from 3/16/2014 (which in outlook is 2 weeks old) Can you please help ? i need to list down the recent email list


    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 | where { $_.SentOn -gt [datetime]"3/1/2014" -AND $_.On -lt [datetime]"3/25/2014" } | Select-Object -Property Subject, SentOn, Importance, SenderName


    output -

    Subject SentOn Importance SenderName
    ------- ------ ---------- ----------
    RE: Missed conve... 3/16/2014 8:04:4... 1 Saxena, Divyansh...
    Happy Holi 3/15/2014 7:34:0... 1 Saxena, Divyansh...
    Shift Handover_1... 3/15/2014 7:27:1... 1 Saxena, Divyansh...
    FW: Mindjet migr... 3/15/2014 7:03:3... 1 Saxena, Divyansh...
    George - Act 3/15/2014 7:00:5... 1 Saxena, Divyansh...

  • Is it possible to search the archive folder inbox. If a match is found how to move it to a separate folder.