Auto Respond to Emails using Flow in Office 365

Leaving users, we’re all sad to see them depart, until someone requests that their mailbox remain open to have the content managed or to send an Auto Response advising the sender that they have left. We all have them, those mailboxes which are sat there, gathering dust for one reason or another.

If you are happy to leave the mailbox open and enable its out of office, then of course this can be completed, converting it to shared mailbox so it doesn’t use an Office 365 license and crafting complex rules based on senders and keywords.. But this can become a burden to manage especially as you build a collection of them over time.

Third party SMTP Gateway and Hygiene services, Mimecast, Clearswift, Ironport and ProofPoint, to name but a few, have introduced mechanisms over the years to manipulate message content providing auto response capability within message flow.

As you migrate to Office 365, you may decide to leverage the inclusion of Exchange Online Protection (EOP) as part of your licensed service moving away from the existing solution. However the capability of customising auto responses based on inbound message parameters might not fulfill all requirements. So we turn to Microsoft Flow as an alternative solution.

If you have never looked at Microsoft Flow before in Office 365, it’s a service that helps you create automated workflows between your favourite applications and services to synchronise files, get notifications, collect data, and more. For a more detailed overview I recommend visiting the Microsoft Docs Flow resource.

Capturing the Leavers Messages

Within this scenario, we have successfully moved all the Exchange Mailboxes to Exchange Online and want to remove the legacy solution replacing it with an Office 365 alternative. This needs to be completed without having to leave the mailboxes enabled and provide a centrally managed solution that new leavers can easily be added to. Before we move onto Microsoft Flow, we need to prepare Exchange Online to capture the Leavers Message

Set up a service account

We start by setting up an Office 365 licensed service account, this will be permissioned to the Do Not Reply Shared Mailbox and to setup the Flow. It will need to be licensed for the following at a minimum to support this solution.

  • Flow for Office 365
  • Exchange Online (Plan 2)
  • Office Online

Create two Shared Mailboxes

  • Leavers-Quarantine@hhgttg.com – Captures all the leavers content. (I recommend a 14 day retention policy on this one to minimise build up of content)
  • DoNotReply@hhgttg.com – Used to send the auto response out (Put a delivery restriction on this one)
    • Assign the service account Send On Behalf permissions

Create a Distribution Group

  • Leavers@hhgttg.com – This will contain all the leavers as they depart.

Create a Transport Rule

A transport rule is necessary to capture the messages for the leavers contained in the Distribution group using the following logic.

  • If the Sender is located ‘Outside the organisation‘ and Is sent to a member of group ‘Leavers@hhgttg.com
  • Then redirect the message to ‘Leavers-Quarantine@hhgttg.com

Adding a Mail Flow Rule within Exchange Online

Building the Flow

Now we have a method of capturing the leavers messages it’s time to build the mechanism which will perform the custom auto response.

Login to Office 365 as the new Service Account.

Custom Message Source

Before we build our Flow (we are nearly there…) a source for the custom auto response messages needs to be setup, so create a New Excel Document… Part of this will require the creation of a Table within the document which can only be completed using the full Excel Office application.

  • Create 3 columns, User, Message Body, Subject
  • Populate each of the Columns for a single user
    • Use Alt + Enter to add new lines within the body cell)
    • The User Column MUST be lower case
  • Select the three headers, the data entered and select Format as a Table and select any format you fancy.
  • From the Table Tools, Design menu options on the ribbon, change the Table Name to LeaversMessages
  • Save the Excel File to the OneDrive of the Service Account as LeaversMessages.xls

LeaversFlow002

The Flow

Now we have the component parts of the solution we can finally bring together the inbound emails and the outbound custom messages.

Logged in as the service account access Microsoft Flow from the Office 365 menu or access https://flow.microsoft.com

LeaversFlow003

Click on Create from blank

LeaversFlow004

Click on Search hundreds of connectors and triggers at the bottom of the page

LeaversFlow005

Next you will be presented with the Create Flow Page where Connectors and Triggers are selected.

NOTE: If this is the first time you are setting up connectors, some may prompt you for logins, and permissions as required. Complete these as necessary when prompted to do so.

Search the Connectors for Outlook

Select the connector trigger ‘Office 365 OutlookWhen a new email arrives in a shared mailbox’

LeaversFlow006

Search for the Shared mailbox name which will search for it as you type.

Select Inbox as the folder to monitor.

LeaversFlow007

Click ‘+ New Step, Add an action’ to add the next stage of the process

LeaversFlow024

The next stage is to add a lookup to the Excel document stored in the Service Accounts OneDrive.

Search the Connectors for Excel

Select ‘Excel Online (Business)Get a row’ (Ensure that you select the correct connector)

LeaversFlow008

In the Excel ‘Get a Row’ connector configuration set the Source as ‘OneDrive for Business’

LeaversFlow009

Set the Drive option to OneDrive

LeaversFlow010

Use the file navigator to set the File option to the Leavers.xlsx we created earlier

LeaversFlow011

If everything has worked so far, the Table option should list the LeaversMessages table we created in the Excel file.

LeaversFlow012

Then select the User column header as the Key Column. This will be where the leavers email address will be looked up against.

LeaversFlow013

Lastly, select the Key Value using the Add Dynamic Content options select Expression, click Show More under the string functions and click toLower(). Click back on Dynamic content and click on To from the Outlook parameters.

NOTE: The string comparison of the lookup for Excel is case sensitive. This requires that the user column in the Excel document MUST also be in lower case.

LeaversFlow026

Now we have the Inbound email and the Excel Connector configured we need to add the send email connector to perform the Auto Response.

Click ‘+ New Step, Add an action’ to add the next stage of the process

LeaversFlow015

Again search for Outlook in connectors and select ‘Office 365 Outlook – Send an email from a shared mailbox’.

LeaversFlow016

Under Mailbox Address search for the DoNotReply@hhgttg.com mailbox we created earlier.

LeaversFlow017

In the email To parameter select the From attribute from the first inbound message connector. This will send the Auto Response email back to the original sender.

LeaversFlow018

For the Subject and Body parameters select the Subject and Message Body attributes loaded from the Excel Connector. You can add additional text around the outbound message body at this point also.

LeaversFlow019

Once complete, provide a Flow Name such as Leavers Notifications and click Save Flow.

LeaversFlow021

The Flow will now appear under My Flows for the Service Account. From here it can be shared will others, so that it can be monitored without having to log on as the Service Account under which it will run.

LeaversFlow022

To test the Flow add a user to the Leavers@hhgttg.com and add a line for them to the Leavers.xlsx Excel File with a corresponding test response message.

Other Points to Note

There are some other points to note about running Flow and Exchange Online Mailbox administration…

Delegating Process support

To provide others access to administer this solution, the Distribution Group can be administered using standard Exchange Roles. The Excel document can be shared through OneDrive for leavers to be added to the table as required and the Flow can also be shared with others to allow it to be monitored or edited.

Deleted Mailboxes to Contacts

Note that when mailboxes are removed from Exchange Online, it is necessary to Disable not Delete the mailbox then re-enable the Object as a Mail Contact to allow it to be added to a Distribution Group. This contact object can then be hidden from the GAL.

Flow Licensing

As with other Office 365 services, licensing for Flow is bundled with other products and the standard Office 365 License provides 2000 Flow runs per month for each with a 5 minute maximum flow frequency.

However, the total amount of allowed Flow activations is across all licensed users, so if you have 10 Office 365 licenses you get 20,000 runs per month and 100 licenses 200,000 runs per month..

This should more than cover you for the runs you require, if not you can increase the Flow Plan you have or stump up £25.00 GBP ($40.00 USD) for 50,000 runs bundle. See the Flow Plans licensing page for more information.

LeaversFlow023

It should be also worth noting that some of the Connectors in use in this example are still in preview and as such their configuration may change as they are released. Caution should be taken if implementing as a live solution.

Next Steps

This solution provides a simple 3 stage Flow process.. It can be enhanced to include Flow conditions such as an separate flow action if the leaver is NOT found in the Excel file resulting in a default Auto Response being sent instead of a custom one as an example.

I hope this is a good demonstration of what is possible using Flow with Exchange Online.


Update: A few hours after posting this, it was amended to reflect the requirement for the email address to be in lowercase. If you tried the flow and it fails to compare the email address, do check out the updates above in relation to case sensitive comparison on the Excel connector. 

Advertisements

One thought on “Auto Respond to Emails using Flow in Office 365

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.