In this post, App Dev Manager Edward Fry demonstrates how to use Microsoft Outlook Object Library from PowerShell.

Introduction

Time is a precious commodity. For many professionals, there just aren’t enough hours to accomplish all the tasks in a day. Thankfully, today’s world thrives on automation. Computers can perform painstaking, mundane tasks in a matter of seconds, leaving you the extra time to work on a multitude of more mentally intensive jobs. Whether you have an advanced technical degree or just use the computer for day-to-day tasks, it isn’t too hard to make the computer your personal assistant.

For this article, let’s focus on a specific type of task that is common for many professionals: sending out reports. While the specifics will vary, this scenario typically involves generating reports on some area of business interest and then sending those reports to recipients of interest. Of course, one way to approach this task would be to have each interested party look at their own reports or dashboards.

However, this isn’t always feasible. For instance, your users may be executives who don’t have the time for managing their own reporting. Or you might be in sales or services and need to share reports with your customers. Sharing that information is just part of the service that you provide, so it wouldn’t be appropriate to offload such a task to your customers. These are only two possible scenarios where you need to push reports to others rather than (or in addition to) having them pull their own.

An additional constraint, though, is that your report recipients must all be separated from each other. One executive might not need to know or care about another’s area of focus. One customer should not see or even be aware of other customers’ reports for privacy reasons. This constraint adds some complexity and work to the process. Wouldn’t it be nice to have the computer be your personal assistant and take care of all that for you?

Our goal in this article is to automate the download of periodic reports, associated processing, and dissemination to recipients via discrete outgoing messages with relevant attachments. For this article, I am assuming that the reports come from a reporting tool that allows email subscriptions, like SQL Server Reporting Services (SSRS), that Outlook is the email client, and some basic PowerShell knowledge.

By the way, you don’t have to be a seasoned developer in order to pull this off; anyone with a little PowerShell experience can benefit. Every information professional will benefit from some basic scripting ability in a language like PowerShell, and I highly recommend setting aside some time to learn this useful practical skill. If you need the basics, check out some of the free resources that are available.

Setup

Before we get to the scripting, we need to set some things up. This will make the script easier to code, and it will enable easier management as things change over time.

Report Delivery

A key component of this solution is that the reports you need to send out are delivered to your inbox. Of course, it’s possible to run them manually and save them off, and there’s a solution for automating those, too (though that is outside the scope of this article), but it is much easier to have them come to you.

Oftentimes, people are not aware that you can automate report delivery in SSRS. It is quite simple and involves just pre-selecting the filters of interest and a delivery schedule – for instance, once a month or once a week, etc.

When you set up your subscription, be sure to select the type of report that you wish to distribute. Since PDF is the most common file type for this sort of scenario, we will assume the reports are delivered in PDF format for the rest of this article.

Recipients

Typing in a list of recipients each time you need to send out reports is tedious and error-prone. Instead of doing that, let’s add a layer of abstraction to the process. In our case, we will use Outlook groups to refer to recipients as a group rather than by name. As you can see in the image below, you create a group for each type of recipient for each destination. For instance, our example here assumes customer recipients. You would make an Outlook group for primary recipients, one for CC, etc. Check out how to make groups in Outlook HERE.

For each group, then, you simply add the actual people who will need to receive the reports. That could be one person or a lot of people. As contacts change, all you need to do is keep these groups up to date, and the automation script will implicitly pick those changes up without any need to modify the code.

Template Emails

We also need a template email. In the image above, you can see that our template includes the message that we want to send to our recipients along with some tags that will be replaced in our script. To create an email template, go HERE.

File System

In this optional but recommended step, organize your folder structure so that you can save a copy of each report automatically for your records. Setting up an organized filing system is key to keeping the files organized and easy to locate in the future.

Automation Process

Once the reports of interest show up in your inbox at the designated time from SSRS, our automation will follow these steps:

1. Connect to Outlook
2. Open the emails and save the PDF to the proper folder in your filing system for backup purposes. I like to rename each PDF with something meaningful to the recipient and report. For example: (CompanyName)(ReportTitle)(Date).pdf
3. Process Reports
   1. Attach the PDF into the analogous email template.
   2. Expand the To and CC recipient groups.
   3. Customize the message as needed.
4. Send the emails.
5. Disconnect from Outlook

Connect to Outlook

The real magic of this solution is that we can control Outlook remotely through PowerShell. We are taking advantage of the fact that PowerShell makes the entire .NET framework available to us. That means that any functionality exposed by the framework can be accessed by PowerShell. This also means that you could modify this code to control other Office products like Word or Excel. Here is the code, which you can just copy and paste:

Add-Type -assembly "Microsoft.Office.Interop.Outlook"
add-type -assembly "System.Runtime.Interopservices"
try
{
$outlook = [Runtime.Interopservices.Marshal]::GetActiveObject('Outlook.Application')
    $outlookWasAlreadyRunning = $true
}
catch
{
    try
    {
        $Outlook = New-Object -comobject Outlook.Application
        $outlookWasAlreadyRunning = $false
    }
    catch
    {
        write-host "You must exit Outlook first."
        exit
    }
}
$namespace = $Outlook.GetNameSpace("MAPI")

Process Reports

Now we can finally process those emails that were sent by SSRS automatically with the report PDFs.

With our handy Outlook namespace object in hand, we can now get references to other objects and commands within Outlook. First, we’ll get a reference to the inbox and the emails that match our criteria within the inbox.

$inbox = $namespace.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
$emails = $inbox.Items | Where-Object {$_.Subject -like $subjectComparisonExpression}
The $emails variable will contain a list of just those emails that were sent from SSRS.  Now, for each of those, we can apply some processing.  For instance:
# Process the reports by recipient
ForEach ($email in $emails)
{
    # Look at all the attachments in a single email
    ForEach ($attach in $email.Attachments)
    {
        # Not the most robust way to do it, but extract only the first pdf.. there shouldn't be more than one anyway
        if ($attach.filename.contains("pdf"))
        {

             # Set the customer filename and paths and save off their report

             # Save the main file

          # Prepare outgoing emails (see following article section)
      } } }

Prepare Outgoing Emails

Within the looping structure above (in place of the “Prepare outgoing emails” comment above), we can now get our emails ready. This will utilize the email template and groups that we prepared earlier. Here is some sample code to get you started:

             # Now that we've saved the attachments, send notices out to the recipients
            $template = get-childitem $emailTemplatePath -Filter "$emailTemplatePattern$customer.oft"
            if ((Test-Path $template.FullName) -eq $true) # Make sure there's a template to use
            {
                # Create the email from a template
                $emailToCustomer = $outlook.CreateItemFromTemplate($template.FullName.ToString())

                # Expand the contact groups
                #  Start with the To line
                $toListOriginal = $emailToCustomer.To
                $toListNew = $null
                $contacts = $namespace.GetDefaultFolder($olFolderContacts).Items  # All the contacts in outlook

                foreach ($to in $toListOriginal) # Enumerate the list of To's
                {
                    $toContacts = $contacts.Item($to)
                    for ($i = 1; $i -le $toContacts.MemberCount; $i++) 
                    {  
                        $toContact = $toContacts.GetMember($i)
                        [array]$toListNew = $toListNew + $toContact
                    } 
                }

                # Expand the CC line
                $ccListOriginal = $emailToCustomer.CC
                $ccListNew = $null
                $contacts = $namespace.GetDefaultFolder($olFolderContacts).Items  # All the contacts in outlook

                foreach ($cc in $ccListOriginal) # Enumerate the list of CC's
                {
                    $ccContacts = $contacts.Item($cc)
                    for ($i = 1; $i -le $ccContacts.MemberCount; $i++) 
                    {  
                        $ccContact = $ccContacts.GetMember($i)
                        [array]$ccListNew = $ccListNew + $ccContact
                    } 
                }

                # Convert to string
                if ([string]::IsNullOrEmpty($toListNew) -eq $false)
                {
                    $toListFinal = $null
                    foreach ($to in $toListNew) { $toListFinal = $toListFinal + $to.Address + ";" }
                    $toListFinal = $toListFinal.Substring(0, $toListFinal.Length - 1)  # Trim final ;
                    $emailToCustomer.To = $toListFinal
                }

                if ([string]::IsNullOrEmpty($ccListNew) -eq $false)
                {
                    $ccListFinal = $null
                    foreach ($cc in $ccListNew) { $ccListFinal = $ccListFinal + $cc.Address + ";" }
                    $ccListFinal = $ccListFinal.Substring(0, $ccListFinal.Length - 1)  # Trim final ;
                    $emailToCustomer.CC = $ccListFinal
                }
                
                # Customize the rest of the message as needed
                $emailToCustomer.Attachments.Add($fileFullPath) | Out-Null
                $emailToCustomer.HTMLBody = $emailToCustomer.HTMLBody.Replace("[Month]", $priorMonth)
                $emailToCustomer.HTMLBody = $emailToCustomer.HTMLBody.Replace("[Year]", $priorYear)
                $emailToCustomer.Save()
            }

As you can see, we first use a template denoted by the constant $emailTemplatePattern for each recipient or customer to create a new email. Next, we expand the contact groups specified in the To and CC lines of the new email. Finally, we replace the tags that were denoted by “[tag]” in the template with appropriate data points meaningful to each recipient. This data is usually pulled directly off of the title or file name of the reports sent by SSRS. You could also parse information out of the attachments themselves, though such an activity is beyond the scope of this article.

Note: You can also have the script automatically send out your emails. I prefer to double check before they go out to make sure that everything looks ok.

Wrapping Up

Once everything is ready, you can either review and manually send out the resulting emails, or you can just sit back since PowerShell did it for you, depending on your choice above to Save or Send.

It is important to shut down any Outlook processes that were spawned during the script so as to maintain idempotency. Here is some code to do that:

# Close outlook if it wasn't opened before running this script
if ($outlookWasAlreadyRunning -eq $false)
{
    Get-Process "*outlook*" | Stop-Process –force
}

Conclusion

We examined how to configure and connect to Outlook through PowerShell in order to process periodic reports. Those reports are delivered automatically by SSRS as email attachments. PowerShell uses .NET to control Outlook programmatically, file away the reports, and create customized emails for each recipient. This technique could easily be modified to perform other types of email processing or to automate other Office products. We hope that this saves you some time, which is, after all, our most limited commodity of all.