Application Support

Follow

Application Support

Follow
PowerShell's Interaction With Outlook(Part 1)

PowerShell's Interaction With Outlook(Part 1)

Automating Microsoft Outlook via PowerShell

Hooman Pegahmehr's photo
Hooman Pegahmehr
·Apr 27, 2023·

8 min read

One of my clients works as a caseworker for a non-profit agency situated in the Tri-State area. Their agency utilizes a shared mailbox to correspond with their clients and cases. Generally, each case is assigned to a caseworker. However, due to the varying shifts, a coworker from another shift may pick up a case and work on it during their respective shift. The agency heavily relies on creating subfolders and categorizing incoming cases under their assigned caseworker.

A shift supervisor is always present to assign cases and ensure that incoming emails are appropriately sorted. Recently, my acquaintance noticed that emails concerning a specific case were being automatically categorized upon their arrival. She checked to see if there was an Outlook rule that executed this action in the background but found nothing. Therefore, she asked the caseworker for whom the emails were auto-categorizing. He humorously replied that it must be the shift supervisor, as he categorizes emails quickly. However, my acquaintance, let's call her Sammy, knew that this was not true since the auto-categorization continued even after James' shift and the shift supervisor's shift had ended.

Sammy's curiosity was further piqued by James' response. Therefore, she reached out to the shift supervisor to inquire about how it was done. Although he was aware that the auto-categorization had been executed by some of his colleagues, he did not know the exact method. After researching the topic on Google, Sammy learned that this could be accomplished using VBA. However, her organization strictly prohibits the use of VBA and macros. Hence, she suspected that some employees might be using VBA clandestinely, and this could be the reason why these coworkers were reluctant to teach others how to execute this trick.

Sammy approached me via Fiverr, a site where freelancers undertake small projects for $5 and more. She shared her story with me and requested that we categorize the incoming emails associated with her cases programmatically without using Outlook rules and VBA. I accepted the job, and the rest of this article chronicles my interactions with Outlook via PowerShell. You might assume that this is an unremarkable project but read on, and I guarantee you that this assignment is replete with PowerShell gotchas.

We'll start by loading the Outlook COM object and retrieving the Inbox folder

Add-Type -assembly "Microsoft.Office.Interop.Outlook"
$Outlook = New-Object -comobject Outlook.Application
$namespace = $Outlook.GetNameSpace("MAPI")
$inbox = $namespace.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)

You can already see the first problem, $namespace.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox) gives us access to Sammy's mailbox as opposed to the shared inbox. To get around this issue, we have to use "GetSharedDefaultMailbox". Here is the updated code:

Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
$outlook = New-Object -ComObject Outlook.Application
$namespace = $outlook.GetNameSpace("MAPI")
$mailbox = $namespace.OpenSharedMailbox("sharedmailbox@example.com")
$inbox = $mailbox.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)

Promisingly, yes, but this method is not available in all versions of Outlook for all email account types. So instead we have to use "GetSharedDefaultFolder" method:

Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
$outlook = New-Object -ComObject Outlook.Application
$namespace = $outlook.GetNameSpace("MAPI")
$recipient = "sharedmailbox@example.com"
$recipientAddressEntry = $namespace.CreateRecipient($recipient)
$recipientAddressEntry.Resolve()
$sharedMailbox = $namespace.GetSharedDefaultFolder($recipientAddressEntry, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
$messages = $sharedMailbox.Items

So from here, things aren't that bad, we can define a keyword for our search criteria and loop through the inbox messages to see whether we find a match:

Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
$outlook = New-Object -ComObject Outlook.Application
$namespace = $outlook.GetNameSpace("MAPI")
$recipient = "sharedmailbox@example.com"
$recipientAddressEntry = $namespace.CreateRecipient($recipient)
$recipientAddressEntry.Resolve()
$sharedMailbox = $namespace.GetSharedDefaultFolder($recipientAddressEntry, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
$messages = $sharedMailbox.Items
$keyword = "urgent"
$category = "Important"

foreach ($message in $messages) {
    if ($message.Subject -like "*$keyword*") {
        $message.Categories = $category
        $message.Save()
    }
}

but here is when things get more tricky, one of Sammy's requirements states that we should watch the mailbox for incoming messages. There has to be an eventListener that we can set on outlook to watch for incoming messages, however, I would like to mature a working prototype first:

$timer = New-Object System.Timers.Timer
$timer.Interval = 30000 # 30 seconds
$timer.AutoReset = $true
$timer.Start()

Register-ObjectEvent -InputObject $timer -EventName Elapsed -Action $handler -SourceIdentifier "InboxTimerElapsed"

As a side note, if you ever end up working with Register-ObjectEvent, save it into a variable so you can use receive-job to see if you have any error messages.

$job = Register-ObjectEvent -InputObject $timer -EventName Elapsed -Action $handler -SourceIdentifier "InboxTimerElapsed"
#this would allow you to check on the outcome of your event
Receive-Job $job
#this allows you to easily unregister your event
Get-EventSubscriber | Unregister-Event

The timer approach works, but I am already defeated as the message categorization is delayed by the set intervals, remember Sammy wanted the message categorized the second it lands in the inbox. The second problem is with the scoping of the script block passed into the Action parameter of the Register-ObjectEvent Cmdlet. As the script block loses its scope, it cannot refer to the messages received, I tried the variation in which the function is defined ahead of time and accessed like so:

$function = Get-Item function:\MyFunction
$function.Invoke()

No luck with this, I was thinking of turning this function into a module and importing it into the script block like so:

$scriptblock = {
    Import-Module Categorize-InboxMessages
    Categorize-InboxMessages

}

Somehow I got distracted and didn't try this approach. So I can move my entire code into the scriptblock and let it run there, what's going to stop me?

$handler = {
    Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
    $outlook = New-Object -ComObject Outlook.Application
    $namespace = $outlook.GetNameSpace("MAPI")
    $recipient = "sharedmailbox@example.com"
    $recipientAddressEntry = $namespace.CreateRecipient($recipient)
    $recipientAddressEntry.Resolve()
    $sharedMailbox = $namespace.GetSharedDefaultFolder($recipientAddressEntry, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
    $inboxItems = $sharedMailbox.Items
    $inboxItems | Where-Object { $_.UnRead -eq $true -and $_.Subject -like "*$using:Keyword*" } | ForEach-Object {
        $_.Categories = $category
        $_.Save()
    }
}

This approach works, we can improve it by turning it into an Advanced Function and passing and parameterizing the script block. We'll come to the time issue later:

Function Categorize-InboxMessages {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [string]$Keyword,
        [Parameter(Mandatory = $true)]
        [string]$Category,
        [Parameter(Mandatory = $false)]
        [int]$TimerInterval = 30000
    )

    Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
    $outlook = New-Object -ComObject Outlook.Application
    $namespace = $outlook.GetNameSpace("MAPI")
    $recipient = "sharedmailbox@example.com"
    $recipientAddressEntry = $namespace.CreateRecipient($recipient)
    $recipientAddressEntry.Resolve()
    $sharedMailbox = $namespace.GetSharedDefaultFolder($recipientAddressEntry, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)

    $inboxItems = $sharedMailbox.Items
    $inboxItems | Where-Object { $_.UnRead -eq $true -and $_.Subject -like "*$using:Keyword*" } | ForEach-Object {
        $_.Categories = $category
        $_.Save()
    }

    $handler = {
        param(
            [string]$Keyword,
            [string]$Category
        )

        Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
        $outlook = New-Object -ComObject Outlook.Application
        $namespace = $outlook.GetNameSpace("MAPI")
        $recipient = "sharedmailbox@example.com"
        $recipientAddressEntry = $namespace.CreateRecipient($recipient)
        $recipientAddressEntry.Resolve()
        $sharedMailbox = $namespace.GetSharedDefaultFolder($recipientAddressEntry, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
        $inboxItems = $sharedMailbox.Items
        $inboxItems | Where-Object { $_.UnRead -eq $true -and $_.Subject -like "*$Keyword*" } | ForEach-Object {
            $_.Categories = $Category
            $_.Save()
        }
    }

    $timer = New-Object System.Timers.Timer
    $timer.Interval = $TimerInterval
    $timer.AutoReset = $true
    $timer.Start()

    Register-ObjectEvent -InputObject $timer -EventName Elapsed -Action $handler -SourceIdentifier "InboxTimerElapsed" -ArgumentList $Keyword, $Category
}

# Example usage:
# Categorize-InboxMessages -Keyword "urgent" -Category "Important" -TimerInterval 60000 # Check every 60 seconds

-ArgumentList parameter of Register-ObjectEvent could be leveraged to pass the needed values. Now let's deal with the bigger problem, the timer. I had to do some research to find out that $sharedInbox.items has ItemAdd event listener which could get triggered when a new item is added to the collection(in our example the arrival of a new email). ItemAdd comes with Add() method that can allow us to register an action. But how do we keep the script running? let's see:

Function Categorize-InboxMessages {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [string]$Keyword,
        [Parameter(Mandatory = $true)]
        [string]$Category,
        [Parameter(Mandatory = $false)]
        [int]$TimerInterval = 30000
    )

    Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
    $outlook = New-Object -ComObject Outlook.Application
    $namespace = $outlook.GetNameSpace("MAPI")
    $recipient = "sharedmailbox@example.com"
    $recipientAddressEntry = $namespace.CreateRecipient($recipient)
    $recipientAddressEntry.Resolve()
    $sharedMailbox = $namespace.GetSharedDefaultFolder($recipientAddressEntry, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)

    $inboxItems = $sharedMailbox.Items
    $inboxItems | Where-Object { $_.UnRead -eq $true -and $_.Subject -like "*$using:Keyword*" } | ForEach-Object {
        $_.Categories = $category
        $_.Save()
    }
    Function Categorize-IncomingMessages ($item) {
        if ($item.Subject -like "*$Keyword*" -and $item.UnRead) {
            $item.Categories = $Category
            $_.Save()
        }
    }
    $inbox.Items.ItemAdd.Add({ Categorize-IncomingMessages $_ })

    while ($true) {
        Start-Sleep -Seconds 5
    }

}

# Example usage:
# Categorize-InboxMessages -Keyword "urgent" -Category "Important" -TimerInterval 60000 # Check every 60 seconds

The while loop is going to keep the script running, awaiting the arrival of new messages. Register-ObjectEvent and the handler scriptblock is no longer needed. The code is refactored to take advantage of the event listener that's set when a new item is added to the message's array and it triggers our Categorize-IncomingMessages function by passing the new message to it.

Just like when you think you have figured out everything, you'll find out there's more to it. This approach doesn't work on all versions of PowerShell and Outlook. When Sammy tried it we learned that $inbox.Items do not have a method for ItemAdd. So we have to think of a different approach. We can instead use NewMailEX event of Outlook.Application COM object instance.

Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
$outlook = New-Object -ComObject Outlook.Application
$handler = {
    Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
    $outlook = New-Object -ComObject Outlook.Application
    $namespace = $outlook.GetNameSpace("MAPI")
    $recipient = "sharedmailbox@example.com"
    $recipientAddressEntry = $namespace.CreateRecipient($recipient)
    $recipientAddressEntry.Resolve()
    $sharedMailbox = $namespace.GetSharedDefaultFolder($recipientAddressEntry, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)

    $inboxItems = $sharedMailbox.Items
    $inboxItems | Where-Object { $_.UnRead -eq $true -and $_.Subject -like "*$using:Keyword*" } | ForEach-Object {
        $_.Categories = $category
        $_.Save()
    }
}
$job = Register-ObjectEvent -InputObject $outlook -EventName NewMailEx -Action $handler

This works! the final step is to mature this working prototype into an advanced function.

Function Categorize-InboxMessages {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [string]$Keyword,
        [Parameter(Mandatory = $true)]
        [string]$Category
    )
    Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
    $outlook = New-Object -ComObject Outlook.Application
    $inboxItems = $sharedMailbox.Items
    $inboxItems | Where-Object { $_.Subject -like "*$using:Keyword*" } | ForEach-Object {
        $_.Categories = $category
        $_.Save()
    }

    $handler = {
        param(
            [string]$Keyword,
            [string]$Category
        )

        Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
        $outlook = New-Object -ComObject Outlook.Application
        $namespace = $outlook.GetNameSpace("MAPI")
        $recipient = "sharedmailbox@example.com"
        $recipientAddressEntry = $namespace.CreateRecipient($recipient)
        $recipientAddressEntry.Resolve()
        $sharedMailbox = $namespace.GetSharedDefaultFolder($recipientAddressEntry, [Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
        $inboxItems = $sharedMailbox.Items
        $inboxItems | Where-Object { $_.UnRead -eq $true -and $_.Subject -like "*$Keyword*" } | ForEach-Object {
            $_.Categories = $Category
            $_.Save()
        }
    }

    $job = Register-ObjectEvent -InputObject $outlook -EventName NewMailEx -Action $handler -ArgumentList $Keyword, $Category

}

I hope that you've enjoyed this assignment as much as I did. I return for not charging Sammy, she agreed to let me share her story with you. Although we were able to meet Sammy's expectations, I am thinking of building an API around this script to categorize a bunch of cases to their associated caseworkers for a given shift. Once ready, that is going to make part 2 of this writing. Please feel free to reach out and share your thoughts with me. If you can think of a different approach I would appreciate that you share it with me.

Did you find this article valuable?

Support Application Support by becoming a sponsor. Any amount is appreciated!

See recent sponsors Learn more about Hashnode Sponsors
 
Share this