Send one email per user based on a date on sharepoint list every Monday morning

[YvonneTian]

Hi Power Automate gurus,

I built a workflow which aimed to send emails to the owners if their requests are overdue. All request details are documented in a SharePoint list. The logic of this workflow works as follows:

When an item in the SharePoint List is created or modified -> Identify the overdue items -> Send email to the owner.

This workflow works okay. Now I have 2 requests to be added to this workflow

#1. The Workflow can only be triggered every Monday 9 AM (Now it sends when there is a change to the list)

#2. The Workflow only sends 1 email per request (Now it sends emails every time there is a change to the list)

Anyone has any ideas how to improve the workflow to get it working for those two points? Thank you so much!!

Cheers,

Yvonne

[trice602 · Accepted solution]

Hi @YvonneTian ,

I was just sharing this flow with someone else and saw your post.  This one is from an Excel table but still valid if you are using a SharePoint List too, just change the Excel part to List.

On #1 for the 9 AM run, change trigger from when an item is changed or modified (if that's what it is) and add a Scheduled flow Monday 9 AM, you can select both options

For the one email option, here's the guts of the flow.  It's a little long but not too bad.  This example has a create csv and create file (to attach) but skip those two steps.

First two variables...

Next you will add all email addresses to a variable and then in the next step, remove the duplicates...

The last Apply to Each is for the single email per address...

When you add filter array for the next step, this will automatically wrap itself in an apply to each.  Here I am selecting my Excel table (your SP list) and I am filtering the Current Email to the Email column in the List.

Next, I create an HTML table from my filter array.  The values are expressions, not dynamic content so you would enter these in, as an example for Description, as item()?['Description'] .  Do this for each of your columns in your html table. 

Next I am putting a border and some cell spacing on the table of the email.  Remember, skip create csv and create file (different example).

replace(body('Create_HTML_table'),'<table>','<table border="1" style="padding:10px">')

Lastly, your Send Email (V2).  Your to: is the Current Item of the Apply to Each and your html table content for the email is the variable formatTable, as shown below.

It took me a couple of tries the first time to get this right but now use this multiple times a day in multiple flow and is very useful.  Ping me if you need help.  If this was helpful, please mark as a solution to help others find it quickly too!

[YvonneTian · Accepted solution]

Thanks Tom!! That is super helpful!!

The trick is to make it a SCHEDULED workflow!!! I spent so long looking for this **bleep** 'recurrence' and here it is!! That lightens my day and makes everything works!!!

Just to post my solution which could be slightly more straight forward than yours 

The Logic basically goes as this

When it is a Monday / Friday

-> Set a variable called 'Today'

-> Get the SharePoint List

-> Check if any item that has 'Rescheduled completion date' earlier than today

    -> The second condition was then to check when there is no reschedule, if the item has 'desired completion date' earlier than today

-> Send email (email address obtained in the list)

I got the luxury to cut down my workflow because all the information such as emails and expected delivery date are pre-loaded. However your solution is absolutely amazing. Thank you so much for putting all these time and effort trying to answer my question!!! Have a lovely weekend 🙂

Cheers,

Yvonne

[trice602]

Nice @YvonneTian  - I'm glad it helped!  I like your flow and will review in detail this weekend too!