Flow: Send an email to multiple addresses stored in a list or library

SO HERE'S A STRANGE THING. I was working on a scheduled Flow that would loop through the items in a list and send out alerts to groups of Owners and Responders when a task was close to its deadline. Sounds straightforward, right? Being fairly new to Flow, I certainly thought so, and even went as far as to trust Microsoft's recommendations as I was building my Flow application. But it turns out that there were "unexpected benefits" along the way. Notably, all the Owners were receiving duplicate notifications from all the tasks - not what I intended and definitely not what the Owners wanted.

Here's a screengrab of the list. You can see that I've added a couple of Owners and a couple of Responders for each task. I want each Responder to receive a notification and all appropriate Owners to be CCed on each notification.

So for this exercise, I created a simplified version of the list I built. You can see each task in the list has a set of Owners (generally managers in charge of the project phases) and Responders (who are generally supposed to carry out the tasks). The list also has a "Completed" tickbox so Responders can set the task to "Complete".

Right, so with my List all ready and populated with Tasks, I set to creating the Flow that would scan the List once a week, then send notifications to all named stakeholders as a task fell due.

I want my Flow to run once a week, and notify all Responders that the deadline for their task is drawing close.

You can set the parameters of your Scheduled Flow at the point of creation or after you've created it ... I generally choose after. In this example, I want the Flow to run once a week on Wednesdays at 1pm.

Let's have the Flow run on Wednesday at 13.00.

The next step is to add the Get Items call. We need the Flow to scan the entire list, looking for deadlines to act upon. So we add a Get Items action, then point it at the SharePoint site and specific list we want it to scan.

Pretty basic stuff, right ... create a Flow, add a Get Items action. Don't get too comfortable. It starts getting more challenging from here on.

What I wanted was to set up a Condition that would only trigger the remainder of the Flow if the deadline was in the range of one week ago to one week from now. This would mean that a deadline falling within the two-week window would result in notifications being triggered. If Responders don't mark the task "Complete", they'll continue to receive alerts in that two-week window. You might prefer a one-month window, but as you'll see, that's no more difficult to achieve.

Add a condition. We'll need more than one condition, but I'm getting to that.

So ... we add a Condition to the Flow. Then, when we select the Deadline in the "Choose a value" window, Microsoft helpfully adds an Apply to each wrapper around the Condition. I didn't ask it to do this, but it makes sense ... because we want the Flow to loop through all the items, looking for Tasks that have a Deadline that falls inside our two week window, right? So, I'm happy with that.

The value we're going to filter on first is the Deadline. As soon as we add that value in the left-hand field, we automatically get an Apply to each wrapper ...

Now we'll need to set two boundaries for our two-week window - one for the start and one for the end. Again, this can be tricky if you're new to Flow, but pretty easy to discover via Google as I did.

We set the first parameter to the following formula, like this:

addDays(utcNow(), -7)

This means take today's date and subtract seven days. We add the formula as an Expression to the condition, saying that if the Task's Deadline is Greater to or Equal to the above value, then act.

Apply the filter value for Deadline as a calculated Expression ...

Next, we have to define the future parameter for the condition. We do that by adding a second condition that defines a Deadline date seven days from now, using this formula:

addDays(utcNow(), 7)

You could also add a "+" in front of the 7, but it's not necessary. Add the parameter, like this:

Having two Deadline filter values is how we bracket a two week window. Adjust the number of days to change the beginning and end of the trigger time period.

Finally, we don't want alerts to trigger on Tasks that have been marked completed, so we add a third parameter to the Condition, saying if the Complete box is not ticked, you can continue to issue an alert on that item.

Finally, we need a filter value that prevents Task already marked as Complete from triggering a Flow response.

The final completed Condition panel should look like this:

And there we are ... all the Condition filters applied.

Right, so that's the easy part done. Things only started to go wrong for me when I tried to set up an Alert Email that would tell the Owners and the Responders that their Task was due for completion.

I did that by adding a Send Email action to the "If yes" branch of the Condition. But the minute I add the Responder data to the "To" field, helpful ol' Microsoft added an enclosing Apply to each function.

I was beginning to see a pattern emerging. Every time I added a value from the list, Flow added an Apply to each wrapper ... which I assumed was necessary.

Again, that's a good thing, right? Because I want the Flow to loop through all the Responders that I added to the Responder field in the list.

And when I tested the Flow at this stage, each of the Responders receives an email so, again, that's good and what I intended. But it was when I added the Owners as a CC to the email that the problems started.

Go to the Send an email action and click the link at the bottom of the panel that says, "Show advanced options", and add a dynamic value for the Owners into the CC field.

I wanted the add the Owners on the notification as a CC, as they didn't have to do anything, except be aware that the notification had gone to the Responders.

And, predictably, Flow then wraps your Send an email action in another Apply to each, this time for retrieving the Owners values for each email. The thing is, this addition doesn't work like you expect it to.

What happens is that the Flow creates several emails for each task as it loops through the Responders and the Owners ... so for Responders A & B and Owners C & D, you get four emails for the same task, like this:

• Responder A + Owner C
• Responder A + Owner D
• Responder B + Owner C
• Responder B + Owner D

... meaning that The Responder is getting two emails for each task and the Owners are getting two emails for each task. And if you had three Owners and Three Responders, then there would nine emails going out, and so on. We definitely don't what that to be happening. We're going to need a different approach.

I don't mind each Responder getting their own email for a given task, but I don't want that to be multiplied by each Owner also getting an email. So what if we roll up all the Owners for a given task into a single string of email addresses? This is a job for Variables ...

First, we have to declare a Variable for Owner Email Addresses. And that has to be done that outside the Apply to each action, like this:

I added the Initialize variable action after the Get items action, as it has to be listed in the main section of the Flow, outside of any Conditions or Apply to each actions.

Add the action Initialise variable. The Variable Type is String. Leave the Value field blank. Now, I needed to rearrange the way the Apply to each actions were nested, so I unpicked them (by removing any dependent values in the enclosed Actions) and laid them out like this:

To rearrange the Apply to each actions, you first need to remove any dynamic values in the enclosed actions. Then you can drag the Apply to each actions to the correct positions and re-instate any dynamic values in the child actions.

... adding the Append to string variable action that will compile a string of Owners' email addresses for each email as the Flow loops through Apply to each 3.

Really important - do not forget to add the semi-colon after the Owners Email value in the Value field. This is to ensure the string of email addresses are separated correctly.

When we run this Flow, a curious thing happens. All the Owners' email addresses in the group of tasks that qualify under the filter are compiled into an ever-growing string ...

What on earth is going on here? The Flow has compiled all the Owner email addresses from every qualifying task and added them to the CC field of the notification. By the time we get to the fourth notification, every Task Owner is getting the notification, even though they're not an Owner on the fourth task.

... until by the end of the cycle, all Owners are CCed into the last notification.

I tried a few things to fix this - including changing the order of the Apply to each actions - but nothing seemed to work and I wasn't able to find a solution via Google.

The next day, I had a flash of inspiration ... I added another Set variable action before the Apply to each 3 (that appends and holds the Owner emails), and configured it to give the variable a value of "null". This would clear the Owner variable after each notification is sent, preventing a build-up of Owner email addresses in the CC field of the notification.

And that seemed to do the trick. No longer were Owner email addresses from previous notifications being copied into subsequent notifications.

And when I ran a test, there was no more build-up of emails and, as I'd wanted, there was one notification for each Responder with all appropriate Owners for that task CCed on each of the notifications.

There may well be a better way of doing this, but I'm just happy I got a solution that works.

SOME FINAL THOUGHTS

Because Flow can sometimes be a bit difficult to wrangle, here's some extra hints and tips to make this solution better.

Sometimes, when I add an email value to a notification it displays strangely. For example in the below image, you can see that the salutation of the notification displays as "Owners DisplayName" ... but I had actually added the Responders DisplayName, and the notification does show the Responders name on arrival. I don't lose much sleep over this, but I just wondered if anyone else sees this glitch when they use Flow.

Here's what the final Send an email action in Flow should look like. I had put in the Owner and Responder email values for testing purposes, so I could see what Flow was writing into the emails without actually having to send the emails.

Next, if you just put the "Deadline" in the body of the notification, the date displays backwards in the final delivered email, like this: 2021/04/14. To get it to display the right way round and in the right format, add the Deadline as an Expression, like this:

formatDateTime(items('Apply_to_each_2')?['Deadline'],'dd/MM/yyyy')

You can change the "dd/MM/yyyy" bit to a format that suits you, if you prefer.

You can also see, for testing purposes, I had the Responders Email (mislabelled by Flow here as "Owners Email") and the Owners variable in the body of the notification. This saves me peppering my colleagues with emails while I'm testing. However, it's tricky to add the Responders Email value directly into the body of the notification - Flow doesn't offer me that option. So what I did was first add the Responders Email value as an expression, like this:

items('Apply_to_each_2')?['Email']

On saving, Flow converted it to an actual dynamic value (though it still managed to mislabel it as "Owners Email").

I hope this has helped someone ...