cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
haniel
Level 8

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 infomration that was important in this list was the client's email address and name.

Account.JPG

 

 

 

 

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.  

EmailTemplate.JPG

 

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

Flow.png

The flow is triggered when an item in the first list is changed.  As the trigger looks for any changes, you may inadvertetly 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.  

emailbody.png

 

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.

 

 

 

 

13 REPLIES 13
CedricBonnin
Level: Powered On

Re: Sending Custom Emails and Mail Merge using Flow

Hi, 

 

would it be possible to use dynamic content in the email body template?

 

I am trying to do that because I wan't that the whole body of my email can be updated by users without editeing the flow itself.

 

 

haniel
Level 8

Re: Sending Custom Emails and Mail Merge using Flow

Making it completely flexible to include any tags would require token (or placeholders) and then determine if these are valid and replace them with the actual variable.

CedricBonnin
Level: Powered On

Re: Sending Custom Emails and Mail Merge using Flow

Yes and how can I do that? 🙂

CedricBonnin
Level: Powered On

Re: Sending Custom Emails and Mail Merge using Flow

I tried with the "replace" function but my parameter is coming from a getitems action and I do not have access to my items field in function only "values"...

Seems quite difficult!

CedricBonnin
Level: Powered On

Re: Sending Custom Emails and Mail Merge using Flow

Well, 

I succeded in getting the field into an Expression but the replace function doesn't work

replace(string(body('Obtenir_les_éléments_2')?['value']?[0]?['valeurHTML']),'{Tel}',triggerBody()?['Tel'])
while replace 'bla bla bla {Tel} ble ble', '{Tel}', triggerBody()?['Tel'])) does.
 
 
Obviously, valeur HTML is containg HTML.
 
Any idea?
 
CedricBonnin
Level: Powered On

Re: Sending Custom Emails and Mail Merge using Flow

Got it  html value does not contain {Tel} but {Tel}...

So next expression is fine

replace(string(body('Obtenir_les_éléments_2')?['value']),'{Tel}',triggerBody()?['Tel'])

haniel
Level 8

Re: Sending Custom Emails and Mail Merge using Flow

Thanks for following-up.  I'm sure some users will find this informaiton userful Smiley Happy

SithaKommi
Level: Powered On

Re: Sending Custom Emails and Mail Merge using Flow

Can someone have step by step guide to create Custom Emails and Mail Merge using Flow. I am new Flow, hence step by step guides would help me. 

haniel
Level 8

Re: Sending Custom Emails and Mail Merge using Flow

Hi @SithaKommi

 

Are you familar with SharePoint?  If so, start by creating the list mentioned at the top.  As for the flow, most of the information appears on the screen shot above.  Let me know if you have any specific questions.

 

Regards,

-Haniel

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 328 members 6,331 guests
Please welcome our newest community members: