Sending Custom Emails and Mail Merge using Flow

Many of us have used mail merge at some point in our careers.  I was recently asked to help create a SharePoint-based mail merge, where a unique emails gets sent to clients when the status of the client changed in SharePoint.  Depending on a status value, a different email message with personalized greeting would bet sent.  Here's how I leveraged Flow to get he job done.

Two SharePoint Lists

First, I needed to have two lists in SharePoint.  The first list contained the client information, including the status.  This list was used to trigger the flow.  Other information that was important in this list was the client's email address and name.

The second list was the email templates.  For this scenario, the templates contained two fields - the status that would be matched to the client's status and the body of the email, captured as HTML.

Building the Flow

The flow is broken down into three steps

1. Trigger for starting the flow
2. Getting the email templates
3. Sending the correct email to the client

The flow is triggered when an item in the first list is changed.  As the trigger looks for any changes, you may inadvertently invoke the flow on any change.  To overcome this, you may want to consider watching for changes on the status field.  For example, create another field called Last Status and assign it the last status every time you save.  Then, all you need to do is see if the status field has changed.

In the second step, the Get Items action is used to get the email templates.  This control is not fully functional at this time, as the Filter Query is not working.  So instead of getting one specific email template based on the status, I had to get all of them.  What is important is to set the Maximum Get Count parameter to be larger than than number of email templates, or better yet, not set it at all.

This brings me to the third step in the flow.  Instead of simply sending the email template, I had to loop through all the templates until I found the template whose status matched that of my client.  When a match was found, the Send an email action was used to create and send the email.  It is not currently possible within a flow to replace parts of a string.  So, I had to leave the greeting of the email outside of the Email Body itself.

This process can also be used to create mail merges by reversing the process.  Instead of having a single user and multiple email templates, you could have multiple email recipients with a single email message.  You can even leverage this process for sending different messages to recipients as long as there is a way to identify who should receive what message.

I hope you find this useful.