Scripting Office with PowerShell: Creating Outlook Rules

Scripting Office with PowerShell: Creating Outlook Rules

One of the really cool things about Microsoft Office is how extensible it is. Microsoft has included scriptable interfaces for all of the Office applications in the form of Component Object Model Objects, or as you may have heard it called: COM objects. The Office COM objects make it possible to control and automate tasks in probably the most commonly used applications in any job in the world (second maybe to web browsers).

The problem though, is that the COM objects for Microsoft Office as DLLs using .NET classes, so it can be difficult very unfamiliar for PowerShell users who don't have much background in programming. In this and future posts on Scripting Office with PowerShell, I'll try to provide examples and explanations to make the Office COM objects more accessible to IT professionals.


In my role at Cage Data I work directly as a sort of project manager/account rep for many of our customers and keeping track of their emails along with emails from vendors and an awful amount of spam can be really tough. I've long had a rule setup that categorizes emails from customers and vendors, but that means I have to keep track and add any new customers to my rule. What if I could automate this somehow? We track client domains in Atera, so what if I could just have the rule pull from there? Enter the Outlook Object Model:

# If the `New-Object` command errors, uncomment the `Add-Type` command
# Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
$Outlook = New-Object -ComObject "Outlook.Application"

# Get the Outlook namespace
$Namespace = $Outlook.GetNamespace("MAPI")

To start working with Outlook, you'll need to load in the Outlook COM object. All Office Applications use this concept of Namespaces to separate parts of the application, but Outlook only has one: MAPI. We'll need a reference to the MAPI namespace for all of our rule setting.

Now that we have Outlook and the Namespace, let's start creating a new rule:

$Rule = $Rules.create(
    "Client emails"
    [Microsoft.Office.Interop.Outlook.OlRuleType]::olRuleReceive
)

It looks weird, but this code is just creating a new rule named "Client emails" (line 2) that will act on received emails (line 3). This is one of those cases where the object model relies on using .NET types. It can be a little confusing, but you can see on Microsoft's documentation, all you have to do is change olRuleReceived to olRuleSend and now the rule acts on sent messages.

Okay, so we have our rule started, but it doesn't have any conditions assigned. Since we are looking for messages, sent from client domains, we're going to create a Sender Address rule condition:

$Condition = $Rule.Conditions.SenderAddress
$Condition.Enabled = $true

You can see all of the available Rule Conditions in the Microsoft docs.

Using PSAtera, we can write some simple logic to create a list of all client domains:

$Customers = Get-AteraCustomers
$Condition.Address = $Customers | `
    Where-Object Domain -ne $nil | ` # Remove empty domains
    ForEach-Object { $_.Domain.Split(";") } # Split the semi-colon delimited list for customers with multiple domains

Pretty simple so far. We just grabbed all of our customers from Atera and aggregated all of our customer domains into a single list. Since Atera stores multiple domains for a customer as a semi-colon delimited list we split that up into individual items in our final list.

Now that we have the condition added to our rule, what are we going to do with items matching the condition? There's a lot of actions that you can assign to your rule, but for my inbox, I just mark all of the emails with a category named "Client".

$Action = $Rule.Actions.AssignToCategory
$Action.Enabled = $true
$Action.Categories = @("Clients")

For a complete list of actions you can assign, take a look at the RuleAction object.

If the category doesn't exist, the rule will create it for you automatically, so you don't have to worry about checking for the existence of the category before creating the rule.

Finally, now that everything is setup for our rule, just one more line of code:

$Rules.Save()

This last line, as it says, saves all of the changes we've made to the Outlook rules. Note that the method is called on the $Rules object, not the $Rule object, so you can create a number of rules, or create some and make changes to other rules and then save them all at once.

Now put it all together:

# Load the Outlook COM Object
# If the `New-Object` command errors, uncomment the `Add-Type` command
# Add-Type -Assembly "Microsoft.Office.Interop.Outlook"
$Outlook = New-Object -ComObject "Outlook.Application"

# Get the Outlook namespace
$Namespace = $Outlook.GetNamespace("MAPI")

# Get a list of all of the Outlook rules
$Rules = $Namespace.DefaultStore.GetRules()

# Now the fun part. Let's start creating a rule
$Rule = $Rules.create(
    "Client emails", # The name of the rule
    [Microsoft.Office.Interop.Outlook.OlRuleType]::olRuleReceive # Weird looking, but just means the rule will target received emails
)

# Start creating the 
$Condition = $Rule.Conditions.SenderAddress
$Condition.Enabled = $true

# Let's get all client domains from Atera
$Customers = Get-AteraCustomers

# Now collect all of the domains
$Condition.Address = $Customers | `
    Where-Object Domain -ne $nil | ` # Remove empty domains
    ForEach-Object { $_.Domain.Split(";") } # Split the semi-colon delimited list for customers with multiple domains

# The action of the rule will assign the Clients category
# If the category doesn't exist, the rull will create it for us.
$Action = $Rule.Actions.AssignToCategory
$Action.Enabled = $true
$Action.Categories = @("Clients")

# Now save everything
$Rules.Save()

As I said at the beginning, Microsoft has COM objects that you can use for scripting any Office application. Read up about the various Object Models on the Microsoft docs.


Liked that post? Help me keep the blog running

Buy Me A Coffee