Send e-mail to multiple people based on choice column in share point list

[MoriMidori]

Hello,

I'm currently to build the flow to send automatic e-mail to multiple persons based on my share point Choice column.

My situation:

When the new item is create in the Main Share Point list,

1.Power automated can catch the item Plant A (Plant A is Choice Column).
2. Multiple Persons (ex. all persons in Group1 to 3 in A share point list + all persons in Group in B share point list ) can receive the e-mail 

*A share point list and B share point list can refer the attachments.

I refer this link to seek for solution but I have 40 choices to select.

It seems it is not best to do condition for all choices.

https://powerusers.microsoft.com/t5/Building-Flows/Sending-emails-based-on-sharepoint-list-choice/td...

Another question is I have A, B  share point list and one e-mail should be sent to persons on A, B share point list.

Will it have any better solution to resolve it by Power Automated?

Many Thanks
 

[v-yueyun-msft]

Hi , @MoriMidori 

According to your description, do you mean you want to get all the Person's Emails based on your two SharePoint List when an item created in SharePoint List A?  And According to your screenshot , your sharePoint List B Group Column seems a single Person Choice Column.

I test it in  my side , this is my test SharePoint List:

And this is my flow:

(1)The trigger is when an item created in SharePoint List A:

(2)GetItems is get the items in SharePoint List B:

(3)The other actions:

union(triggerOutputs()?['body']?['Group1'],triggerOutputs()?['body']?['Group2'],triggerOutputs()?['body']?['Group3'],body('Select_2'))

item()?['Email']

join(body('Select'),';')

The result is as follows:

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

[MoriMidori]

Hello @v-yueyun-msft ,

Thank for your quick reply.
I found I don't explain the following things.

1.Share Point list  that item is created (Main share point list) is different from share point A and B.  

2.The plant column in Main SP list is as same as A SP list in order to get persons in all groups in A SP list.

3.The person column in B share point list is single person column.

Many thank,

Midori Mori   

[v-yueyun-msft]

Hi , @MoriMidori 

Thanks for your quick response!

The plant column in Main SP list  is a Lookup column or just a Text column in your side ?

Best Regards,

Yueyun Zhang

[v-yueyun-msft]

Hi , @MoriMidori 

【1】If your MainSharePoint list is a Text column like this:

This is my flow:

union(outputs('Get_items_2')?['body/value']?[0]?['Group1'],outputs('Get_items_2')?['body/value']?[0]?['Group2'],outputs('Get_items_2')?['body/value']?[0]?['Group3'],body('Select_2'))

【2】If your MainSharePoint list is a Lookup column like this:

You can just update this action:

triggerOutputs()?['body']?['Plant']?['Value']

Best Regards,

Yueyun Zhang

[eliotcole]

Just quickly ... could you please embed your pictures directly into your initial post there? I can't seem to load them.

---

Moving onward ... I have a suggestion that may help you build, here. It won't necessarily be a solution, @MoriMidori, but it might help simplify your logic.

Make A Helper List

Make a helper list and use that as a Lookup column in your main list (allowing multiple choices if need be), then choose the people column in that helper list as an additional column to include from the chosen list.

Once you have this helper list, then it will present all of the appropriate email addresses to your flow.

Helper List - 'plants'

Make the list and name it "plants" initially, nothing else, no symbols or spaces.

Once you've named it, you can rename the list whatever you like, but this makes the list URL easy.

Configure The plants List

Now in the plants list:

1. Edit the Title column to be:
   • required
   • unique
2. Add a People column named "Group1" (no space!) and ensure:
   • it allows groups
   • multiple selections
3. Add a People column named "Group2" (no space!) and ensure:
   • it allows groups
   • multiple selections
4. Add a People column named "Group3" (no space!) and ensure:
   • it allows groups
   • multiple selections

After you have made the new columns (Group1, etc) you can go back and rename them with spaces and more. Naming them how we have makes them easier to query in Power Automate and elsewhere.

Add Data To The plants List

In the plants list you can now:

1. Add all of choices as that you have in your parent list as Titles
   (in your example they are A, B, C, D, E)
2. In the People columns ensure that every person (and/or group) that you wish to be associated with that choice is in there

If you have external email addresses needed, you may need to add them as 'guests' to your Entra/AAD system for external email addresses.

Main List (your current list)

There are a couple of changes to make to your existing main list that you raised this thread about.

Configure Main List

In configuring your main list you will need to replace your current 'Plant' column:

1. Remove the current 'Plant' Choice column
2. Add a Lookup column called 'Plant'
   1. Select a list as a source - Select the new helper list 'plants'
      (or whatever you renamed it to)
   2. Select a column from the list above - Select 'Title'
   3. More options - Tap 'More options' and and leave all options as default other than the following
      1. Add additional columns from source list - Select:
         • 'Group1'
         • 'Group2'
         • 'Group3'

(you can choose whether or not to allow multiple suggestions here)

Now when people make a choice from the Plant column the groups will be populated with the contacts that you wish to be there.

This means that when your flow is triggered these values should be included and if not will be easily retrieved from the Plants list.

Further Suggestion

I have placed this information in the below spoiler so as not to distract from the above.

Spoiler

Alternatively, as a left-field suggestion, make groups for each set of users / addresses that you wish to send to.

Helper People Column Change

When you make the people column in your helper list you can now create it without the ability to accept multiple entries.

This change is key as it makes it a single entry rather than an array in flow, ensuring ease of use there.

Make The Groups

Now, create a group for each choice in the helper list, naming them appropriately.

Now you can:

• Add users / guests to the appropriate groups
• Add the group as the single People entry in the helper list column

Groups Advice

Theoretically, it works best to make Microsoft 365 groups for each of these, as they have an email address, and in the future you can use sharing facilities within those groups.

However, you can also create security groups and distribution groups, too. Security groups can be made in the Entra admin portal, and distribution groups may need to be made by an Exchange admin. But you might have the ability to make them.

Potentially Bad Advice

You could swap your choice column for a people column and not use a helper list and just rely on created groups to populate the choices there. However it would get complex as there's no easy way to limit the choices available, and there is a lot more to it. I would advise against this.

[MoriMidori]

@eliotcole 

Thanks for your reply.

I attach the image of my current situation for my question.

When the new item is create in the Main Share Point list,

1.Power automated can catch the item Plant A (Plant A is Choice Column).
2. Multiple Persons (ex. all persons in Group1 to 3 in A share point list + all persons in Group in B share point list ) can receive the e-mail 

For your reference, I don't have authorization for the A SP list and B SP list because it is in the different Share Point Site. I just have  authorization for Main SP list.
Not sure if lack of authorization and different SPS will impact  Lookup column.

BR,
Midori Mori

[MoriMidori]

@v-yueyun-msft 

Thanks for your reply. 
The plant column is Choice column not text column.

It seems that use Lookup or text column will be better. I will try it.

Another will different SPS will impact look up function? because I cannot see A SP list and B SP list in my main list when I use look up function.
Main List (my work SPS)

A SP List (company SPS)

B SP List  (group SPS)
are in the different Share Point Sites...

The following is the picture what I want to build.

Every time when new item is created in Main SP list, Plant column (Choice column) decide who will receive the e-mail.

eg.

When item is created in Main List and Plant column is A,

the plant column A and its all groups in A SP list will receive the e-mail.

the all people in B SP list also will receive the e-mail.

Attach the pictures for your reference.

[v-yueyun-msft]

Hi, @MoriMidori 

Thanks for your response! If your column is a choice column like this:

You can just modify this in the Get Items 2:

triggerOutputs()?['body']?['PlantChoices']?['Value']

You need to use your real column name in your side .

Best Regards,

Yueyun Zhang

[MoriMidori]

Hello @v-yueyun-msft

I tried your method. but there is no "Email" shows.

If I get lost in some steps?
 
BR,

Midori Mori

[v-yueyun-msft]

Hi , @MoriMidori 

Thanks for your quick response! Do not worry about it had no field to choose , you just need to use the Expression here. 
First click here:

And then enter :

item()?['Email']

Best Regards,

Yueyun Zhang

[MoriMidori]

Hello @v-yueyun-msft 

The message shows that I need to enter a valid json.

I'm thinking that if I don't get the accurate e-mails in the Compose step.

Since I don't have authorization of A SP list, I cannot confirm the name of Group1 is really Group1. 

[v-yueyun-msft]

Hi , @MoriMidori 

First , you need to put this code in the "Expression":

Ah...you can see the run history output for the "Get Items 2" to see the ColumnName in the ShrePoint ListA:

Best Regards,

Yueyun Zhang

[MoriMidori]

Hello @v-yueyun-msft 

thanks for your detailed explanation.
I can put the code in Expression and found I can get the e-mail.

However, the error message is displayed when the Compose step.

BR,

Midori, Mori

[v-yueyun-msft]

Hi , @MoriMidori 

The compose code is :

The error code means one of the array here is null. You need to check if one of this return is a null array?

outputs('Get_items_2')?['body/value']?[0]?['Group1']

You can try to add a "Compose" action to check its return in your side .

Best Regards,

Yueyun Zhang

[MoriMidori]

Hello @v-yueyun-msft 

Yes, I found there are null in the Group2. But I cannot add persons in the group.

The list is like as below

BR,

Midori Mori

[v-yueyun-msft · Accepted solution]

Hi , @MoriMidori 

Thanks for your quick response! First you need to check if your column name is right in your code :

Due to if it has no error , it will return []. So in the logic , the flow will not get the error code .

So this may happen when your columnName is not exist in your side .

You can check it in your run history!

Also we can use the code to avoid it , like this.

First create a variable:

Then we can use this code :
union(coalesce(outputs('Get_items_2')?['body/value']?[0]?['Group1'] , variables('Blank'))
,coalesce(outputs('Get_items_2')?['body/value']?[0]?['Group2'] , variables('Blank'))
,coalesce(outputs('Get_items_2')?['body/value']?[0]?['Group3'] , variables('Blank')),body('Select_2'))

Best Regards,

Yueyun Zhang

[eliotcole]

Hi, @MoriMidori ... I think that by putting the complexity in to the flow you are potentially giving yourself more problems to solve rather than less.

That was why I suggested that you simplify your data by making the second helper list:

1. Make a second 'helper' list (maybe call it 'Plants')
2. Delete your 'Plant' choice column
3. Add a 'Plant' lookup column which provides people from the helper list columns

I'll edit my answer above to resemble your setup a little more closely. However I wish you well with @v-yueyun-msft if you get things sorted there. 🙂