Sharepoint List email automation based on last modified

Question

Hi,

I didn't quite find what I was looking for.
I have a list which contain updates on project and I would like to send an automated email when the modified date is more than 30 days old.
It's my understanding than a rule cannot be based on the last modified column. I tried using power automate and a flow but I do not find the right trigger for it, for example if I use "when an item is modified" it wont check the whole list and also someone need to change something. I would like to check once a day automatically.
When I tried "scheduled cloud flow" then it is not linked to my SP list...

Can anyone point me out in the right direction?
Thanks

Answer 1

Hi @Grendall ,
Your requirements can be implemented using Power automate.
The list used in the test is as follows:

Create a new flow: choose “Scheduled cloud flow” and set the parameters according to your actual situation.

Fill in the judgment conditions:

formatDateTime(item()['Modified'],'yyyy-MM-dd')
is equal to
addDays(utcNow(),-30,'yyyy-MM-dd')

Then run the flow and you can receive an email as below:

Thanks,
Yanli Jiang

===========================================

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @YanliJiangMSFT-7045

It did work, but when I am trying to add a dynamic contact email it automatically creates another "apply to each"
any idea why?

Answer 3

It only works if I enter a manual email in the TO: field it does not work while I try to use the contact email. The "apply to each -> current item" is added by default as soon as I select contact email. If I remove the apply to each then the email is not sent to the "contact email" and the flow runs for several hours. if i leave the apply to each i get an error FYI same result using outlook 365 send an email V2

here is a sample of my list:

Answer 4

hi

three thoughts from my end

1)
Rather than getting all items and filtering them afterwards I would specify the filter for the modified date directly within the Get items action. You might run in issues long-term with an increasing amount of records in your list.
To process your desired records only, simply add the following expression within the field for the oData Filter Query.
Thus, you can opt-out for the additional condition afterwards (or re-purpose it):

Modified le '@{formatDateTime(addDays(utcNow(),-30,'yyyy-MM-dd'))}'

2)
It might be that your people picker column allows multiple values. In this case the object returned would include an array and therefor you face the additional 'Apply to each' condition.
In case you want to stick with one allowed recipient only, you could simply change the column to NOT allow multiple values and the additional 'loop' will not happen.

If you do not want to touch the column but stick with the first one receiving an e-mail only, you can put the following expression within the TO field
(First entry of current items Contact field, whose property Email)

first(item()['Contact'])?['Email']

in case you like the idea of multiple recipients the following approach could fit your needs

3)
Employees get terminated every now and then and your automation would fail sending the e-mail, as the recipients address is unknown.