Flow to send email from contacts on SharePoint List

[efilippakis]

Hello

• I made an approval flow that sends approvals and emails to managers of 4 different regions. Can I set up one SharePoint list with their emails to have the flow pull from? I want to avoid going into the flow to change the contacts anytime the region managers change? 

[grantjenkins]

You sure can. Hopefully this will get you started.

For this example, I'm using the following SharePoint List. Note that I've renamed the Title field as Region. And the Manager field is of type Person.

See full flow below. I'll go into each of the actions.

I'm not sure what your trigger is, so I've just used a Manual trigger for this example.

Get items retrieves the managers from the Region Managers list.

Select extracts out the Manager Email for each Manager into an array. Note that Map is using Text mode (see screenshot).

Join uses the output from Select (array of emails) and joins them with a semi-colon.

Start and wait for an approval can then use the output from Join for the Assigned to field which will assign the approval to all of our managers.

So, whenever your flow runs, it will dynamically retrieve the Managers from the SharePoint list.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[efilippakis]

Thanks @grantjenkins ! Can I include more than one email in the Manager field. Each region might have 2 or 3 managers. Also, my trigger is a form. The form has multiple choice for the region. I would need that selection to point to the SharePoint list with the emails. The email that needs to be fetched is the, is only from the selected region 

[grantjenkins]

We sure can 🙂

For this example, I'm using the following SharePoint List. Note that I've renamed the Title field as Region. And the Manager field is of type Person that allows multiple selections.

And the following Microsoft Form that just has multiple select choices for the Regions.

See full flow below. I'll go into each of the actions.

When a new response is submitted and Get response details retrieves the data from our Microsoft Form.

Get items retrieves all the items from our Regional Managers List.

Filter array uses the output (value) from Get items and for the condition, it checks if the array of Regions selected in the Microsoft Form contains the Region (Title column) for the current item. This will result in a list of items for only the Region(s) we selected. The expression used in the condition is below. It wraps our Region field from the Microsoft Form into a json expression, so it's converted to a proper array of items.

//This is the Region column from our Microsoft Form

json(outputs('Get_response_details')?['body/r28a2d725d0f745d2b3f7bace0de8548c'])

We now have one or more items from our lIst that contain one or more managers. To extract out all the managers we would normally need to use an Apply to each and iterate through each of the items. However, we can utilize XPath to extract out multiple managers across items very easily.

XML is a Compose action that converts the data from our Filter array to XML so we can use XPath expressions on it. The expression used to convert our data to XML is.

xml(json(concat('{"root": { value:', body('Filter_array'), '}}')))

Managers is a Compose action that uses XPath to extract out the Manager's Emails. The expression used is below. This will give us an array of Emails. However, it will also potentially contain duplicate values. I'm in the Regional Managers List as a Manager for more than one Region.

xpath(outputs('XML'), '//root/value/Manager/Email/text()')

Join will remove any duplicate values from Managers above and join each Manager's Email with a semi-colon ready to add to our Approval action. The expression used is:

union(outputs('Managers'), outputs('Managers'))

IMPORTANT: Before you add the Approval action, you should save and run your flow to ensure it's returning the correct emails.

Lastly, we can use Start and wait for an approval and use the output from our Join for the Assigned to field.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[efilippakis]

I really appreciate all the help, i got the first method to work, but  it requires I create single rows for each manager vs grouping them all into one row. the filter  array keeps failing. I can see it grabs the region I need, not sure what the error means.

[grantjenkins]

Is your Region field in your Microsoft Form a single select choice field, or multiple select choice field?

[efilippakis]

Good morning! It's a single select. 

[grantjenkins]

See updated flow that uses a single select choice field for the Region on the Microsoft Form.

Below is the form where we can choose a single Region.

Below is the full flow. I'll go into each of the actions.

When a new response is submitted and Get response details retrieves the data from our Microsoft Form.

Get items retrieves the item from our Regional Managers List where the Region is equal to the Region we selected on the Microsoft Form. This will retrieve a single row that contains our Managers for that Region.

Select extracts out each of the Manager's Email from the Manager field. Note that even though Get items only returned a single item, it's still returned in an array. To get the single item (without the flow adding an Apply to each) we can use the first expression to retrieve the first item from the array. The expressions used in the Select are:

//From
first(outputs('Get_items')?['body/value'])?['Manager']

//Map
item()?['Email']

Join uses the output from Select (our array of emails) and joins each one with a semi-colon which will allow us to add them to the Approval.

Start and wait for an approval uses the output from Join which will send the approval to each of the managers.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.