March 2013

Volume 28 Number 03

PowerShell - Managing an Outlook Mailbox with PowerShell

By Joe Leibowitz | March 2013

Have you ever needed to find an e-mail message from one or two years ago that, if you couldn’t find it, might adversely affect your circumstances? Does your company automatically delete messages from your Inbox or Sent Items folders? Do you get tired of copying e-mails to multiple folders when they touch on multiple topics of interest—a particular project, manager, subject matter, company division or the like?

Say an e-mail from Steve Masters comes in regarding the finances on Project X in Dubai. It would be useful to have an automated way to distribute copies of this message to folders for Project X, Steve Masters and Dubai, and ultimately to yet another folder for safekeeping. And all without you having to manually click around the Microsoft Outlook interface multiple times for each item of mail. (And if you receive a large volume of email, it’s all the more vital to automate some of the management tasks involved.)

The rules facility in Outlook is often useful in addressing these kinds of situations, and code I’ll present in this article shows how to automate the creation of rules. For example, copying messages from Steve Masters to a related folder in Outlook is a relatively straightforward matter. And that goes for other standard rules operations.

What’s more difficult to do, and generally outside the capacity of Outlook rules, is to get a single e-mail into multiple folders based on more amorphous criteria than standard rules contemplate. While Steve Masters's e-mail can easily be copied to a related folder, the other relevant factors in his message—that it concerns finance, Dubai and Project X, among other possible criteria—are much more difficult to translate into actions. The subject line might cover one or two of the criteria and be susceptible to formal manipulation by an Outlook rule, but that often places too much trust in any given sender's subject lines. What we need is a quick, automated method to assign values to e-mails in both your Inbox and Sent Items such that a PowerShell script can be run to allocate them to the correct folders.

Programming Outlook Rules

This section takes a stab at demonstrating programmatic creation of two Outlook rules. One rule reads a message’s subject line for the specific string “Notification” and then copies relevant messages to a specified folder (named Notifications).

The first piece of business is to invoke the Outlook API using code such as the following. This code gives us access to the messaging namespace of the Outlook API, in which typical objects are e-mail messages, Outlook rules and mail folders, among other objects (for more information, see http://msdn.microsoft.com/en-us/library/ff866465.aspx).

Add-Type -assembly "Microsoft.Office.Interop.Outlook"
$Outlook = New-Object -comobject Outlook.Application
$namespace = $Outlook.GetNameSpace("MAPI")

With the namespace in hand, we specify the originating folder (the Inbox), create a new rule and specify the folder to copy to (Notifications).

$inbox =
  $namespace.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
$MyFolder1 =
  $namespace.Folders.Item('joe.leibowitz@companyname.com').Folders.Item('NOTIFICATIONS')
$rules = $namespace.DefaultStore.GetRules()
$rule = $rules.create("My rule1: Receiving Notification",
  [Microsoft.Office.Interop.Outlook.OlRuleType]::olRuleReceive)

It’s an interesting exercise to run some lines of code to see how folders are enumerated in the MAPI namespace. The following lines illustrate how to navigate to various levels of depth in the folder tree:

$namespace.Folders
#in my Outlook, gives 'joe.leibowitz@companyname.com'
#and 'Public Folders - joe.leibowitz@companyname.com'

$namespace.Folders.Item(1)
#gives 'joe.leibowitz@companyname.com'

$namespace.Folders.Item(2).Folders
#gives 'Public Folders - Favorites' and 'Public Folders - All'

$namespace.Folders.Item(1).Folders
#gives all subfolders under 'joe.leibowitz@companyname.com' main Folder

$namespace.Folders.Item(1).Folders.Item(1)
#gives one subfolder, 'Deleted Items'

$namespace.Folders.Item(1).Folders.Item('My Projects').Folders
#gives all the subfolders in 'My Projects'

$namespace.Folders.Item(1).Folders.Item('My Projects')
.Folders.Item('Outlook scripts').Items 
#gives all emails under the 'Outlook scripts' subfolder
#of 'My Projects' folder

Having created a new rule instance, we can define its parameters and properties like this:

$rule_body = $rule.Conditions.Subject
$rule_body.Enabled = $true
$rule_body.Text = @('Completed Notification')
$action = $rule.Actions.CopyToFolder
$action.enabled = $true
  [Microsoft.Office.Interop.Outlook._MoveOrCopyRuleAction].InvokeMember(
    "Folder",
    [System.Reflection.BindingFlags]::SetProperty,
    $null,
    $action,
    $MyFolder1)
$rules.Save()

The first line relates the rule to the subject line of an incoming e-mail. The string to look for (“Completed Notification”) is specified, and the action to take is CopyToFolder (as opposed, for example, MoveToFolder). The invocation recites the action and the destination folder.

Figure 1 shows the second example of programmatically creating a rule, where we look at recipients rather than the subject line.

Figure 1. Creating a Rule for Recipients

$MyFolder2 = $namespace.Folders.Item('joe.leibowitz@companyname.com').Folders.Item('FRANCISCO')
$rule = $rules.create("My rule2: Received from Gonzalez", [Microsoft.Office.Interop.Outlook.OlRuleType]::olRuleReceive)
$body = $rule.Conditions.From.Recipients.Add('maria.gonzalez@companyname.com')
$rule.Conditions.From.Recipients.ResolveAll()
$rule.Conditions.From.Enabled = $true
$action = $rule.Actions.CopyToFolder
$action.enabled = $true
  [Microsoft.Office.Interop.Outlook._MoveOrCopyRuleAction].InvokeMember(
    "Folder",
    [System.Reflection.BindingFlags]::SetProperty,
    $null,
    $action,
    $MyFolder2)
$rules.Save()

All e-mail messages from Maria Gonzalez at the companyname.com domain will be copied to the Francisco subfolder.

For a good general primer on programming Outlook rules, see http://tinyurl.com/ayyack5.

Managing Messages with a Script

Clearly, one can do a lot in Outlook with the built-in rule facility, but what if you want to divide messages into categories like Project, Finance, Human Resources, Recipient Name, Sending Division, Month of Receipt, City, State, Country or any other of a virtually limitless number of categories, where any given e-mail could apply to multiple categories that are not identifiable from the subject or even the body of the message? In cases like these, a custom script can function like a special rule.

In the following example, we manage the Sent Items folder by manually marking the subject line of each message with acronyms ("\\\Admin,FOR", for example), where each acronym relates the message to a target folder (Administrative,Foreign in the example) to which the e-mail should be copied. The process calls for manual action only to mark the subject lines of sent items with your designated acronyms. 

When the script runs, it finds the acronyms and copies the e-mail to as many folders as the acronyms listed in the subject line refer to. In addition, sent items are “aged” out of those folders by code that tests the respective received and sent dates for a given period and moves relevant messages to their respective resting places once the age criteria is met.

The function I wrote is called CopyMove-Email. It has three parameters: a list of acronyms or symbols, the corresponding list of folders and an integer representing the maximum number of days to retain a message under Sent Items before moving it to safekeeping. (For use with Inbox items—the code is not shown—a fourth parameter should be added to indicate the number of days to maintain an Inbox item.)

FUNCTION CopyMove-Email
{
  param
  (
    [string[]]$acronyms,
    [string[]]$folders,
    [int]$sent_max_keep
  )

Then, as with the case of creating a rule, the messaging API is invoked:

Add-type -assembly "Microsoft.Office.Interop.Outlook"
$Outlook = New-Object -comobject Outlook.Application
$namespace = $Outlook.GetNameSpace("MAPI")

If you look in Task Manager, you’ll see two instances of Outlook. One is the original, displayed version that you are working in, and the other is a background Outlook process being used by this script.

Next, we construct a timespan to mark the absolute limit for keeping sent items in that folder, whether the item is marked for copying or not.

$timespanSentMax = New-Object TimeSpan($sent_max_keep,0,0,0,0)

Using the timespan, we create the dates against which to test each e-mail item in the Sent Items folder.

$Date = New-Object DateTime
$SentMaxDate = New-Object DateTime
$Date = Get-Date
$SentMaxDate = $Date.Subtract($timespanSentMax)

We also need a string-formatted date to use in naming text files that will store a specific day's run of this application. The text files will store, respectively, mail that was copied and mail that was moved.

$dt = $Date.ToString().Replace('/','')
$dt = $dt.Replace(' ','')
$dt = $dt.Replace(':','')

Then we need to accumulate data about moves and copies in temporary holding arrays:

$array_of_move_results = @()
$array_of_copy_results = @()

Experience shows that merely looping through all the messages in the Sent Items folder once isn’t enough; on a first loop, some items are handled but others aren’t touched. It may take three or four such loops to handle all the items that need handling. The number of loops probably depends on how many mail items are ready for copy-move operations and other factors in how Outlook interoperates with your computer. In any event, the solution is to use a Do-While structure to keep running the loop until all marked items have been properly managed.

do
{
  $hit = 0

The application is now ready to begin parsing through Sent Items:

foreach($mail in $namespace.Folders.Item('joe.leibowitz@companyname.com').Folders.Item('Sent Items').Items)
{

Note that joe.leibowitz@companyname.com is the author's Outlook name for the group of folders that includes Sent Items. You should, of course, use the name that shows up in your own Outlook client.

The first If test is against the established maximum-days window and for the existence of either the \\\ tag or the /// tag:

if(
    $mail.SentOn -ge $SentMaxDate -and
    (
    ($mail.Subject.IndexOf('///') -gt - 1) -or
    ($mail.Subject.IndexOf('\\\') -gt - 1)
    )
  )
{
$acronym_ctr = - 1

With a matching e-mail in hand, test it for each acronym in the $acronyms parameter:

#test the mail for all acronyms
foreach($acronym in $acronyms)
{
$acronym_ctr += 1
#get the matching Folder for this acronym
$fldr = $folders[$acronym_ctr]
  if($mail.Subject.ToUpper().IndexOf($acronym.ToUpper())  -gt - 1)
  {

For each matching acronym, the script makes a copy of the subject e-mail and moves the copy to the folder that matches the acronym. This part of the code is shown in Figure 2. Essentially, this copies the message to the new folder while also leaving it in the original Sent Items folder.

Figure 2. Copying Code Based on Acronyms

$MailCopy = $Outlook.CreateItem(0)
  $MailCopy = $mail.Copy() 
  $results =
    $MailCopy.Move($namespace.Folders.Item('joe.leibowitz@abbott.com').Folders.Item($fldr))
  write-host $results
  $str = $mail.SenderName + ' ' +
    $mail.SentOn + ' '  +
    $mail.Subject
    $array_of_copy_results += @($str,' to: ',$fldr,' ','from: Sent Items')
  }
}

The next action for these kinds of mail items (shown in Figure 3) is to move them to Sent Items OLD for more permanent storage and also for later transfer to a text file documenting the move-copy actions.

Figure 3. Moving Items to Sent Items OLD

$target_folder2 = 'C:\SentMailMoved_' + $dt + '_.txt'
  #having done the copies,move it to 'OLD' folder for storage
  $results2 =
    $mail.Move($namespace.Folders.Item('joe.leibowitz@abbott.com').Folders.Item('Sent
    Items OLD'))
  write-host $results2
  $str = $mail.SenderName + ' ' +
    $mail.SentOn + ' '  +
    $mail.Subject
  $array_of_move_results += @($str,' to: Sent Items OLD'' ','from: Sent Items')
  $item_handled = $true
  $hit = 1
}

Then messages in Sent Items that are past the maximum retention period should be moved to Sent Items OLD. Their data is also stored for moving into the related text file. (See Figure 4.)

Figure 4. Managing Message Retention Limits

elseif($mail.SentOn -lt $SentMaxDate)
  {
    $target_folder3 = 'C:\SentMailMoved_' + $dt + '_.txt'   #.txt'
    $results =
      $mail.Move($namespace.Folders.Item('joe.leibowitz@abbott.com').Folders.Item
      ('Sent Items OLD'))
    write-host $results
    $str = $mail.SenderName + ' ' +
    $mail.SentOn + ' '  +
    $mail.Subject
    $array_of_move_results += @($str,' to: Sent Items OLD',' ','from: Sent Items')
    $hit = 1
  }
  }
}
while($hit -ne 0)
<#

The last action to take is moving data about moved and copied items into the text files set up to hold that data:

$target_folder3 = 'C:\SentMailMoved_' + $dt + '_.txt'
'Moved from Sent Items to Sent Items OLD: ',$array_of_move_results >> $target_folder3
$target_folder = 'C:\SentMailCopied_' + $dt + '_.txt'
'Copied items: ',$array_of_copy_results >> $target_folder
}

You can call the CopyMove function with parameters, including the list of acronyms and the list of related folder names, plus the maximum number of days to keep sent items in that folder before saving them to Sent Items OLD:

CopyMove-Email ('Admin','PRJ','FOR','DOM')('Administrative','Projects','Foreign','Domestic') 15

Wrapping Up

All the power and flexibility of Office applications is exposed in their APIs and open to being adapted
to endless scenarios and needs by using PowerShell—or C# and other languages.  I use PowerShell because I find that it’s more fun than the others!

As an afterword, I’d note that scheduled tasks can be configured to include the code I’ve shown, but a certain number of gotchas will manifest themselves in the process, not least of which is that the task will not run if the machine is not connected to the network. So, remote users relying on a VPN, for example, might be out of luck if, as is usually the case, the connection automatically shuts down after a certain period of nonuse (like when you go to bed at night). Thus, the app can be scheduled to run only during times when you know the network connection is active.


Joe Leibowitz is an infrastructure consultant who specializes in Microsoft’s identity management products.