Send email based on SharePoint List

[StephenGW]

Hello all,

Looking for some more help building a flow. I have a SharePoint list that contains some dates and some Yes/No data. What I need is when the Yes/No column is True and the date is both 14 and 28 days old I need it to send an email. This is a reminder email basically so as long as the True column is met and the days are either 14 or 28 days old it will send the email.

Thanks

[v-LilyW-msft]

Hi @StephenGW 

Do you want to filter out the item which Yes/No column is equal to true and the date expires 14 days or 28 days and send an email? If any misunderstanding, please kindly let me know.

I set up a simple flow, you take a try with the screenshots below.

This is my test list, after my flow runs, the two marked items will be filtered out and send an email. 

Flow in details:

Expression：

@or(equals(item()?['EndDate'], addDays(utcNow(), -14, 'yyyy-MM-dd')), equals(item()?['EndDate'], addDays(utcNow(), -28, 'yyyy-MM-dd')))

After flow runs, i will receive an email:

Note:The column name used in the formula are all internal names.

How to find column internal name you can refer to this article

https://tomriha.com/what-is-sharepoint-column-internal-name-and-where-to-find-it/ 

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StephenGW]

@v-LilyW-msft 

Sorry for the late reply. It was a long holiday weekend. I should maybe explain this a little better. This will be a reminder email. The Yes/No is if they want a reminder, and it should only happen on 14 and 28 days from the time the data was entered. So if it has been 14 or 28 days since the data was originally entered and they put Yes they want the reminder. Then it will trigger an email for each different instance this is met.

So in this example The Follow Up Audit column is Yes and the days since Audit equals 14 ( I dont want to use this column it is just for example) so it would send an email to the email address in the column to notify them they need to follow up. The initial trigger would be recurring daily.

Does that make sense?

Thanks

[v-LilyW-msft · Accepted solution]

Hi @StephenGW 

According your description, if the user selects Yes when creating an item, a reminder email will be sent on the 14th and 28th day after it is created.

You can refer to the screenshots below.

This is my test list

Flow in details:

Expression1 formatDateTime(items('Apply_to_each')?['Created'],'yyyy-MM-dd')

Expression2: addDays(utcNow(),-28,'yyyy-MM-dd')

Expression3: formatDateTime(items('Apply_to_each')?['Created'],'yyyy-MM-dd')

Expression4: addDays(utcNow(),-14,'yyyy-MM-dd')

After Flow runs,Lily and Test2 will receive reminder email:

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StephenGW]

@v-LilyW-msft 

I think I understand how it works and it looks good to me. I will setup a test and see if it works tomorrow morning. As always great help!

Thanks

[StephenGW]

@v-LilyW-msft 

Kind of related, do you know how I would be able to pull the date difference in this flow to put inside the email body? I thought I had it with something like this but it doesn't seem to work. sub(ticks(utcnow()),ticks(['Created'])).

Thanks,

[v-LilyW-msft]

Hi @StephenGW 

You can use the Expression below to calculate the difference between the two dates.

div(sub(ticks('2019-05-13'),ticks('2019-05-04')),864000000000)

In your flow, you can use the expression:

div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(formatDateTime(items('Apply_to_each')?['Created'],'yyyy-MM-dd'))),864000000000)

Best regards.

[StephenGW]

@v-LilyW-msft 

Ok I see the difference and what I was doing wrong. You have been very helpful as always.

Thanks!