Send email on every Friday of week using power automate in round robin

[BhavaniShanker]

Hi,

I have created the flow to send email invite on every friday in our team one by one every week. We are of 5 members in a  team, I have assigned each week 1 person. 5th member falls under next month first week and the 2nd person starts from 2nd week of month. I need help how to handle the situation using round robin method. Below is the share point list created for the team members.

ID	Name	Email	Status	Week Num
1	XX1	XX1@hotmail.com	Yes	1
2	XX2	XX2@hotmail.com	No	2
3	XX3	XX3@hotmail.com	No	3
4	XX4	XX4@hotmail.com	No	4
5	XX5	XX5@hotmail.com	No	1

Appreciate your help.

Regards,

Bhavani

[BhavaniShanker]

@ScottShearer , @trice602 , @LinnZawWin  -could you please advise on above query.

[David_MA]

The following expression can get you close to what you want, but the 5th member will not always fall on the first week of the following month:

add(mod(div(dayOfYear(utcNow()), 7), 5),1)

This expression calculates the sequential number based on the current week number.

[LinnZawWin]

@BhavaniShanker 

Will you be able to create a new "Email On" column in your SharePoint list?

Then, Scheduled Flow would run daily and send an email to the record which has the same "Email On" as today's date.

Then, the flow searches for the row with the earliest "Email On" and then, update the value as one week later.

E.g. The email invite is sent out every Friday of the week.

Here's the initial setup. Set Email On for the first member as the next Friday and set the Email On for the other members with the different dates from the past in the order that you would like to send.

Name	Email On
XX1	2023-07-21
XX2	2023-07-02
XX3	2023-07-03
XX4	2023-07-04
XX5	2023-07-05

After sending it out the first time on Friday to XX1, the flow would look for the record with the earliest "Email On" (which is XX2) and update it with the next Friday date. The value look something like this

Name	Email On
XX1	2023-07-21
XX2	2023-07-28
XX3	2023-07-03
XX4	2023-07-04
XX5	2023-07-05

And the process repeats itself weekly and it would work well if you remove a member from the list. If you want to add a new member to the list, you just need to set the "Email On" value with one of the dates from the past.

E.g. if you want XX6 to be emailed between XX1 and XX2, set the "Email On" value as "2023-07-27".

[BhavaniShanker]

Hi @LinnZawWin ,

Thanks for the explanation, I have tried the flow to build update date for next person on email column EmailOn. But, it is updating the current column. Tried giving the initialize variable and set to 0, then tried to assign the ID+1. it is throwing

the error saying below error in screenshot. If I can assign the ID+1 to variable, it will be one step away to reach the goal. Could you please suggest how to achieve the scenario to update the next row date and also end of the row, it should go back to 1st row again. 

We have also tried , add(variables('Title'), 1) but this expression throwed error.

Have exported the flow and attached below.

For refernce, below are the columns i'm using  

Title	Name	EmailOn
1	XX1	27/7/2023
2	XX2	4/8/2023
3	XX3	1/7/2023
4	XX4	2/8/2023
5	XX5	3/8/2023

Appreciate you're quick help!

[LinnZawWin]

@BhavaniShanker 

Instead of increment, you should use "Set variable" action with add() function to add 1 to the the ID number by using int() function to convert the string to integer.

However, "updating date for next person on email column EmailOn" does not require incrementing the ID of the record. If you do that, you end up updating the non-existent member when the number becomes 6.

The flow should simply retrieve the record with the earliest "EmailOn", send an email, and update the "EmailOn" value of that record with the current date. That's all.

Can you please re-read my suggested solution above and let me know if there is any part which is unclear?

[BhavaniShanker]

Hi @LinnZawWin 

Thanks for the above suggestion.

I have re read the solution given by you, it is best approach which satisfies our requirement.

But stuck at point where I could not find "flow searches for the row with the earliest "Email On" and then, update the value as one week later(I got formula to update).

addDays(utcNow (),add (sub (5,dayOfWeek (utcNow ())),7)) to get the next friday

Could you please help how to achieve below.

"flow searches for the row with the earliest "Email On". 

Appreciate the Help!!

[VictorIvanidze]

See https://www.ivasoft.com/roundrobinflow.shtml

[LinnZawWin · Accepted solution]

@BhavaniShanker 

The screenshot in my previous response is exactly how to find "flow searches for the row with the earliest "Email On" 

You can use "Get Items" action to get the items in the SharePoint list but order by the "Email On" column with Top Count = 1. Then, it will only return the row with the earliest "Email On"

[BhavaniShanker]

Hi @LinnZawWin , Thanks for the solution, It worked now