Recurring flow to send email to one user per week

[juls]

Hello,

I am trying to build a flow that will send the same email twice a week (Monday and Friday), but the receiver will be a different person every week. These receivers are on a list, and I would like it to go in the order the list is on. When the list is finished, it should restart from the beginning.

I started building it but got stuck on the restart logic at the end, so I saved it before researching how to continue. When I saved it, it sent that email to every user on my list, so I guess I'm on the wrong track. 

This is what I have so far:

The next step is where I got stuck, I was trying to make it go back to the beginning of the flow if it got to the end of the list:

But wasn't sure how to continue from there and I am clearly doing something wrong as everyone got the email at the same time.

Any help how to do this is appreciated, thank you!! 

Ps. I've created a new list so I can test just sending emails to my addresses, so I don't get shamed in the office again lol

[rzaneti]

Hi @juls ,

Do you have a kind of "Last email sent" column in your list? If you don't have and it is possible to add, I would recommend you to do so, as it will be very helpful to this flow.

By using a column like that, you can either (1) track the user that received the email most recently and select the next in line or (2) just filter the List for the oldest received date.

Let me know if you have a column like that or if it is possible for you to create it (make sure to create it as a "date" data type), and then we can move forward to implement the logic 🙂

[juls]

Hi @rzaneti, thank you for your reply!

I am creating this column right now 😃

[juls]

Heya @rzaneti , where should I go from there? =]

[rzaneti · Accepted solution]

Hi @juls ,

Sorry for the delayed response!

I've prepared the following step-by-step, but some particularities from your flow may require some small changes. If you get stuck in any step or need any additional actions in your process, let me know and I will be happy in help 🙂

Example overview

For this solution, I will work with the following example, and try to extract the email from the user with the oldest "LastSent" date, which in this case is "Frank S":

Your flow must be very straight forward: you just need a "Get items" action (you can use the one that is already inserted in your flow), and then you can use the "Send email" action. Finally, we update the List record to ensure that the "LastSent" date is equal to today. From my end, instead of a "Send email", I'm using a "Compose" action instead, just to display the output of the expression used to extract Frank's email:

Further reading

Before we start the step-by-step, I will share some additional content related to the topics discussed in this answer, as it can be helpful for you or for future readers that face the same issues. 

• Get Items action overview (official docs): https://learn.microsoft.com/en-us/sharepoint/dev/business-apps/power-automate/guidance/working-with-... 
• Data types in Power Automate: http://digitalmill.net/2023/08/03/data-types-in-power-automate/ 
• Working with dynamic contents in Power Automate (in this post, there is a specific section for expressions): http://digitalmill.net/2023/07/31/using-dynamic-content-in-power-automate/ 
• Accessing array elements in Power Automate: http://digitalmill.net/2023/07/12/using-loops-and-accessing-array-elements-in-power-automate/ 

Step 1 - Getting List records sorted by date

Let's start!. In Power Automate, modify your "Get items" action to include a Order By query that will sort all of the records, in ascending order, by your date column. To achieve it, the query must be [YourColumnName] asc, just like I did below (highlighted in yellow):

Note: in this query, you don't need to add quotes to the column name. 

As mentioned before, this small change will return all list records sorted in ascending order by date, so we need to capture only the email address from the first record, as it has the oldest date. 

Step 2 - Get email from oldest record

To get the email of the first record returned by SharePoint, you will need to use the following expression: 

outputs('Get_items')?['body/value'][0]['Email']. This is the meaning of each part of the expression:

• outputs('Get_items')?['body/value']: Accesses the property "value" inside the "body" of the "Get items" action. In practice, the "value" property stores a list of the records returned by SharePoint. As this is a list of records, its data type is an array.
• [0]: This is a index reference for array elements. As mentioned above, the "value" property is an array, and we can access each of these individual records by passing its index between square brackets. The index counting starts at zero, so when we use "[0]" in a expression, we are accessing the first item of an array (when we use "[1]" we access the 2nd, and so on).
• ['Email']: After the index reference, we can now access any property from the individual list record. In our case, we need to access the email property, so we are passing its name between square brackets and single quotes. From your end, make sure to replace 'Email' for the logical name of your List's email column. Sometimes these columns names are tricky, so let me know if you find any trouble in this step. 

I'm adding this expression to a "Compose" action, but you can do it directly into the "To" input from the "Send email" action:

Step 3 - Update records LastSent date

After sending your email, you need to update the list again to ensure that your record won't be the oldest one anymore. You can do it by adding a "Update item" action, and passing the following expression into "id" input:

outputs('Get_items')?['body/value'][0]['ID']. This expression follows the same logic of the Email one, but at this time we are accessing the "id" of the record. 

For the other action inputs, you just need to populate your date input (in my case, called "LastSent"), and you will use a utcNow() expression for that:

The utcNow() expression gets the current date/time, so you can ensure that the record will be updated with the exact date of the flow run. 

Testing the flow

After running the flow, as expected, our expression returns Frank's email, as he is the employee with the oldest date in "LastSent" column:

And our "Update item" action modifies the SharePoint List, ensuring that Frank's "LastSent" date is set as today: 

When I run the flow again, the expression will return Lisa's email, as she now has the oldest "LastSent" date:

And, as expected, her "LastSent" will be updated for today:

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/ 
https://www.linkedin.com/in/raphael-haus-zaneti/ 

[juls]

Thank you very much for the detailed information and guide, I really appreciate it!

It worked well, yay 😃