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
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
thanks
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:WINDOWSassemblyGACMicrosoft.Office.Interop.Outlook
It should look something like this:
17:34 PS>dir C:WINDOWSassemblyGACMicrosoft.Office.Interop.Outlook
Directory: C:WINDOWSassemblyGACMicrosoft.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.
@JD did you figure this out? I have the exact same error message but the code is
Add-Type -AssemblyName System.Printing
Use NameSpace.Logon to select a profile.
msdn.microsoft.com/…/bb219914(office.12).aspx
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.
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…
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.
@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.
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.
$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,
Is it possible to search the archive folder inbox. If a match is found how to move it to a separate folder.
Make sure you run Outlook and PowerShell at the same level of elevation, otherwise you get this error:
New-Object : Retrieving the COM class factory for component with CLSID {0006F03A-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)).
@Bob H
You can specifiy within outlook to allow the scripts to access your mailbox. I believe this will prevent the error message you’ve mentioned.
On a side note, I’ve created a script that is somehow similar to this topic – it is moving email items to specified folders, PST files and so on. You can easily modify this script to datamine the inbox content. Here it is: github.com/mnmnc/p0r
Hey Scripting Guy! Love this snippet of code. I was wondering if you have any tips on how to take this a bit further. I’m interested in mining my email to determine the frequency of repeated terms and/or phrases with the goal of using this data to better
manage my inbox and gain insight into what are the common topics I’m discussing with project team members. Any ideas?
If you have a really large inbox (mine is nearly 2GB), it can take a looooooong time to load it into memory. If you are only mining emails from a particular sender (or something else you can sort by), I’d recommend copying relevant emails to a subfolder
and using the ‘PickFolder()’I method. That will bring up a small dialog box letting you choose which folder you want to seach. In my case, I just wanted the Body of all the emails from a certain sender. I just copied them all to a temp email folder and seached
that. Took the process for 20 minutes+ to 45 seconds 🙂
Is there a way to do this for a gmail account?
Excellent! Once I figured out how to store the variable I had no issues.
Thanks,
Justin
Can this be used for all the users in a RDB
Hey Scripting Guy !
Good Day,
I have a modified version of the script that is posted to data mine outlook inbox. Please check below code:
Write-Host "Creating Get-OutlookInBox Function …" -ForegroundColor Magenta
Function Get-OutlookInBox
{
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, ReceiverName,SenderName, Body,CC
}
Write-Host "Function Created Successfully…" -ForegroundColor DarkGreen
Write-Host "Runing search Query and exporting results…" -ForegroundColor Magenta
Get-OutlookInBox | where {$_.Subject -match ‘NetApp Product Registration’}`
| select Subject, ReceivedTime, SenderName, CC
Another update I have made:
Write-Host "Creating Get-OutlookInBox Function …" -ForegroundColor DarkYellow
Function Get-OutlookInBox
{
Add-type -assembly "Microsoft.Office.Interop.Outlook" | out-null
$olFolders = "Microsoft.Office.Interop.Outlook.olDefaultFolders" -as [type]
$outlook = new-object -comobject outlook.application
$folders = $outlook.session.Folders.item(1).folders
foreach($folder in $folders) {
$Name = $folder.Name
Write-Host "Fetching Data from $Name"
$folder.items | Select-Object -Property Subject, ReceivedTime, Importance, ReceiverName,SenderName, Body,CC,To,@{n=’Folder’;e={$Name}}
}
}
Write-Host "Function Created Successfully…" -ForegroundColor DarkGreen
Write-Host "Runing search Query and exporting results…" -ForegroundColor DarkYellow
Get-OutlookInBox | where {$_.Subject -match ‘KSA’-or $_.Subject -match ‘Najran’ -or $_.Subject -match ‘Jazan’ -or $_.Subject -match ‘Jizan’ -or $_.Subject -match ‘Gizan’ -or $_.Subject -match ‘Gazan’ -or $_.Subject -match ‘Jezan’-or $_.Subject -match ‘Saudi’-or
$_.Subject -match ‘14038’ -or $_.Subject -match ‘14048’}`
| select Subject, ReceivedTime, SenderName, CC, To, Folder | Export-Csv -Path C:TempKSA_Emails_report.csv -Force
Both the scripts are not generating full data. Only the items that are residing in the OST file are being downloaded while old items are not being fetched.
Please support.
Thank You
Hello,
Can this script be modified to use a searchfoler instead of the inbox folder?
IF so can someone explain me how do do this please?