Flow to automate email to user in a list

[Pb505]

I'm trying to do something which I really feel shouldn't be that difficult.

I have an MS lists page with two columns. One contains users in my organisation (it's not just their name in basic text - it is checked against their active directory record). The other contains a date which they will be on call.

What I want to do is use flows to automate an email to be sent to the user when the date assigned to them is reached. Basically, notify them they are on call.

Any help much appreciated! Thanks

[#2]

1. Setup a flow that runs every 24 hours.
2. In that flow, grab your list using an odata filter for today's date.
3. Loop through the resulting output and send an email to each record.

If you post some more specifics about your list I could get more specific on how to do it.

Are your users in azure ad?

[#3]

@BrendanE1535 

This is how my list looks - I blanked out the names but when you click on them it brings up a box with all their contact information (email, phone, office location, profile pic etc.)

I only need it to send one email to one user at a time. If today's date = the date in the "duty begins" column then it should send an email to the user next to that date.

I'm not certain if users are in Azure AD. If I click a user then I get to see quite a lot of information relating to them like recent files. Is there a quick way to check if they're in Azure AD?

Thanks for your reply.

[#4]

Hi@Pb505 ,

As i understood your column must be Person Or Group type and this field populate from Azure AD well not directly but yes they are sync with Azure AD.

You want to trigger mail only when value matches but as per my knowledge only trigger  happens for certain action like item created or deleted or modified. So if you want to trigger mail you need to use Recurrence(timer) which will execute may be per minute. I hope that helps.

[#5]

Correct, the column type is "Person or Group" and yes, I believe they are synced with Azure AD. I thought it would be possible to have a recurring flow (run once per day) which would look for a date in the list which is equal to today's date. Then it would send an email only to the person in the cell to the left of that date (i.e. the person on call on that date).

I'm guessing I need to use a filter in my list to identify an instance of today's date and then automate the sending of an email to the person next to the date in the list (immediately to the left of it). I'm not so sure on how exactly to use the filters.

[#6]

Hi @Pb505 

Why you want to use filters in list,  for sending automated mail Power Automate will do rest of work by specifying correct condition there, but if you are talking about Editing View of List that can be done using filter option available in Edit View.

[#7 · Accepted solution]

I ended up doing this with a table in Excel instead of using Lists.
My table in excel has two columns. "Name" and "Date on call".
My flow has 3 steps; "recurrence" (once weekly on a Friday), "get a row", and "send an email".
In my "get a row" step, I chose the table and chose "date on call" as the key column. Then for my key value I used the expression "add(44196,dayOfYear(utcNow()))" to create today's date in the format that Excel passes it (I believe this will only work in the year 2021 as 44196 is the number of days between 1st Jan 1900 and the 1st Jan 2021) - I'll need to update it when we hit next year.
Then in send an email I have just been entering fixed email addresses but I will add another column to the table with email addresses for each name and then this will be available as dynamic content in the "to" field.