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‘
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
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
Click on Create from blank
Click on Search hundreds of connectors and triggers at the bottom of the page
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 Outlook – When a new email arrives in a shared mailbox’
Search for the Shared mailbox name which will search for it as you type.
Select Inbox as the folder to monitor.
Click ‘+ New Step, Add an action’ to add the next stage of the process
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)
In the Excel ‘Get a Row’ connector configuration set the Source as ‘OneDrive for Business’
Set the Drive option to OneDrive
Use the file navigator to set the File option to the Leavers.xlsx we created earlier
If everything has worked so far, the Table option should list the LeaversMessages table we created in the Excel file.
Then select the User column header as the Key Column. This will be where the leavers email address will be looked up against.
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.
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
Again search for Outlook in connectors and select ‘Office 365 Outlook – Send an email from a shared mailbox’.
Under Mailbox Address search for the DoNotReply@hhgttg.com mailbox we created earlier.
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.
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.
Once complete, provide a Flow Name such as Leavers Notifications and click Save Flow.
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.
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.
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.
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.
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.