Flow to send email reminders when calendar date approaches

[B-ran]

New to the forum and flow so please forgive my limited knowledge.

What I am trying to do is set up a flow that will send an email along with reminders at certain intervals when an item is created in a SPO calendar up to the date of the item along with reminders when it becomes past due. The email will need multiple recipients which will vary depending on the timing of the reminders and will vary depending on the item created.

[v-yamao-msft]

Hi B-ran,

Please try to use formatDateTime function and adddays function in your flow.

You could add a Condition in your flow, if the start date of the event is equal to today’s date plus one day, then send user a reminder email or other notifications.

The Condition could be configured as:

@equals(formatDateTime(triggerBody()?['EventDate'],'yyyy-MM-dd'), formatDateTime(adddays(utcnow(),1),'yyyy-MM-dd'))

The flow configuration for your reference:

Please take it for a reference. If you need more help, please feel free reply.

More details about the function, please check the doc at here:
https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language

Best regards,
Mabel Mao

[B-ran]

Hello Mabel,

Thank you for the response, much appreciated!

In the instance you suggested I will need a condition for every reminder correct? For example I will need a new condition for 30 days, 14 days, 7 days etc? Also, would the flow run everyday to check the current date or would I have to manually run the flow so that it would send the reminders at the different intervals?

The other question is how do I make this work after the due date has passed to continue sending reminders?

[efialttes]

@B-ran

One option is to have a Flow executed everyday, getting all the items from your list and iterate over them with an Apply to Each. Taking as starting point the item's creation date, then add 30 days, 14 days, etc. and compare with today. One condition per reminder.

You can also have an addition condition to evaluate if Due Date has passed, if so send a reminder (assuming you want to send a daily reminder if Due Date is passed).

Take into account a Flow can run for 30 days, then it is cancelled by the system, since this is the current limit. So if you have a Flow executed once everyday verifying all items in the list, you will not be affected by this limit.

You can find an excellent example of the recurrence trigger in this post:

https://flow.microsoft.com/es-es/blog/working-with-dates-and-times/

Hope this helps

[B-ran]

@efialttes

Thank you!

Can you please provide some screen shot examples of how this would work?

Also, would you please eloborate more on the 30 day limit for flow runs?

[efialttes]

Hi again

30 days is the current limit for a single flow run. After 30 days the flow pending steps will time out. More info about limits here:

https://docs.microsoft.com/en-us/flow/limits-and-config

You will also find a simpler description of the scenario I suggested in the following link:

https://medium.com/@thomasgranheim/send-reminder-emails-using-microsoft-flow-and-office-365-606e0ea1...

Happy Flowing!

[B-ran]

@efialttes

@v-yamao-msft

Ok, I have a flow working for the creation and edit of an item. It will send out emails based on the dates just as I need it to. Thank you both for your help thus far!

I am however still having issues with the reminders that I need sent out based on the days before the calendar item. Below is what I have setup that is not working. Any idea what I am missing?

[efialttes]

Hi again

I would move the condition inside the Apply to Each. Sorry I cannot see the expression inside, if I zoom the image the content get blurred

Hope this helps

[B-ran]

I moved the the conditions inside the Apply to Each and am still receiving the error:

InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '1' and column '2125': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'.

The formula I am using for the condition is:

@equals(formatDateTime(triggerBody()?['EventDate'], 'yyyy-MM-dd'), formatDateTime(adddays(utcnow(), 1), 'yyyy-MM-dd'))

Any suggestions?

[efialttes]

Hi!

Assuming you are using now the Recurrence trigger, you shall replace the dynamic content trigger body()?['EventDate'] in the evaluation expression.

Is your column named EventDate? If so, try with either item()?['EventDate'] or items('Apply_to_each')?['EventDate'] instead

This way you refer to the current iteration of the loop

There is a very similar flow described in this thread

https://powerusers.microsoft.com/t5/Building-Flows/Flow-Expressions-How-can-I-determine-if-a-SharePo...

Hope this helps

[v-yamao-msft]

Hi @B-ran,

Please make sure the column name you are using in the code is correct.

The error message says that function 'formatDateTime' expects its first parameter to be of type string, please make sure the column you are using in the code is of type string.

Best regards,
Mabel Mao

[B-ran]

@v-yamao-msft

@efialttes

I updated my confition formula and am getting a different error now see below:

@equals(formatDateTime(triggeritem()?['EventDate'], 'yyyy-MM-dd'), formatDateTime(adddays(utcnow(), 1), 'yyyy-MM-dd'))

InvalidTemplate. Unable to process template language expressions for action 'Condition' at line '1' and column '2124': 'The template function 'triggeritem' is not defined or not valid.'.

[efialttes]

Hi again

Instead of

triggeritem()?['EventDate']

try with

item()?['EventDate']

Hope this helps

[B-ran]

@efialttesThat worked!!! Thank you!!!

The next step I hope to accomplish is to make the reminders recognize one of the required options in the creation of the item.

There are 2 options 1.In progress 2.Completed

Is there a way I can make the reminders send when the status is In progress and stop once the item has been changed to Completed? Would this be captured in the get items to only get the ones that are marked In progress?

[efialttes]

Hi again

I would suggest to add a new condition inside the "Apply to each", assuming the status is stored in the SP list. So in this condition you compare the current item status to Completed, if true do nothing (meaning the Apply to Each will evaluate the next item), if false send the notification email

Hope this helps

[B-ran]

@efialttes That is working now also! Thank you for all your help!

Last thing I need is to send the reminders for past due date.

Can you explain where the condition would need to be and what the condition formula might look like?