Send email on a date entered in MS Form

[MarkH1972]

Hi. I have an MS form and a simple PA workflow in place which notifies different people that a new starter will be joining the company. As soon as the form is submitted, each person is emailed a list of tasks to complete prior to the new person arrival. The emails can be sent weeks in advance and i would like to send a reminder 1 week prior to their arrival. 

The persons start date is include in the MS form. 

I have added a "1 week prior to arrival" date on the MS form and played around with the "Delay Until! feature in power automate but i cannot get an email to send on a specific date. Any help appreciated. Thanks 

[RobotRising]

Hi Mark, 

Instead of trying to delay the Power automate flow run I would suggest creating a flow that captures the Microsoft Form submissions into a SharePoint list. You can then have another flow that runs on a scheduled (once or twice a day) that checks the list for any items that the column "new person arrival" is 1 week away from and then sends the email. 

Record form responses in SharePoint | Power Automate

Not quite the right template as this is for checked out items but will give you an idea for the reminder flow: 

Remind editors about their checked out SharePoint pages | Power Automate

--------------------------------------------------------------------
🌟 If I have helped give me a thumbs up! 👍
If I have answered your question, please mark it as solved ✅

[wskinnermctc]

@MarkH1972 the advice given by @RobotRising is probably the best. You don't want flows sitting in delay for an extended period of time. They would basically be running the entire time. It would also timeout after 30 or 60 days I forget which.

Furthermore, the SharePoint list could be used as a record of tasks completed if you wanted to build out a more comprehensive onboarding tracker.

[MarkH1972]

Thanks both. I will give that a try

[MarkH1972]

So I set up the share point list to capture the MS form submission. That is working

Im now trying to set up a separate flow to run daily by using the recurrence as the first step, with Get Items next. 

the Get Items step has no dynamic content though. 

I was going to try and trigger an email from a date column in the share point list 

[wskinnermctc]

@MarkH1972 you will have to add a screenshot of your flow. It is difficult to tell what is the issue from your description.

I think I know what the problem is, but I want to confirm before making anything confusing.

[RobotRising]

You will want to use a query filter in the get items action that filters based on that date , that way you are only getting items that are within that date range. Then send an email from that get items output. 

I am mobile right now so don't have any screenshots or examples but will hop back on here tomorrow 

[MarkH1972]

Thanks everyone and apologies if i am not explaining this too well. We have a simple MS form which captures some basic info about a new starter. When the form is submitted, a workflow runs to notify the onboarding team and the data is put into a sharepoint list. (see screenshot)

What i would like to do, is have a seperate workflow which runs daily and checks the "1 week before start date" column. When that date is reached, i'd like to fire off a reminder email to the onboarding team

[wskinnermctc · Accepted solution]

@MarkH1972 first thing I need you to do is delete your SharePoint List column that you named "1 week before start date".

Primarily you need to delete it due to the name you used. If you look into a post I made about SharePoint Column EntityPropertyName you will see that the name "1 week before start date" will be problematic in Power Automate because it starts with a number. The name you will have to use in power automate is probably something like odata__x0031_weekbeforestartdate and that will be annoying to reference.

Make sure you create the new column with no spaces in the name to prevent encoding. You can rename it afterwards and the internal name will stay the same.

I'm going to create an example using a column named "ProvisioningStartDate" which will be the week/date that the provisioning for a new employee should begin.

(You could just use a "StartDate" column and subtract 7 days; however, to respect your current process, I'm going to use the 1 week before start date example.)

1. Trigger - Schedule Recurrence
   • You set this for the days you want the flow to run. It will need to run on any day that could be a ProvisioningStartDate. If a ProvisioningStartDate is on a Wednesday and the flow only ran once a week on Monday, that item would be skipped. If all your ProvisioningStartDate will be a Monday then you can run the flow once a week on Mondays.
2. Get Items - SharePoint
   • This uses the Filter Query in the Advanced Parameters. You need to put the column name equals the current date. See the reference Get Items Example for more info.
3. Apply to Each - For Each
   • Will perform actions on each item returned by Get Items. If you have 12 employees with the same ProvisioningStartDate then the Apply to Each will go 12 times and send 12 emails.
4. Compose
   • This holds the StartDate dynamic content. This is only needed because the email body has difficulty adding dynamic content. So putting the date in this Compose, and then using the outputs of this in the email body is easier.
5. Send an Email (V2)
   • This will be the email sent to whomever you have the address.

Filter Query expression:

ProvisioningStartDate eq 'utcNow('yyyy-MM-dd')'

Here are the screenshots below:

List View of Items with employee start datesFlow Trigger is a Recurrence ScheduleGet Items uses a Filter Query on the column ProvisioningStartDate eq 'utcNow('yyyy-MM-dd')'This compose is used to hold the startdate to put into the email body. This is due to difficulty getting the dynamic content into the email body.

Send an Email uses the outputs of the Compose in the body for the StartDateEmail Output Example

I hope this example gets you in the right direction.

If you wanted to use the StartDate column instead of the ProvisioningStartDate, you just need to adjust the Get Items advanced parameter Filter Query. It will use the StartDate column and then add 7 days to the current date.

So basically, "If today's date added 7 days is equal to the StartDate then get the item."

StartDate eq 'addDays(utcNow(),int(7),'yyyy-MM-dd')

 Here is a screenshot example:

Filter Query using the StartDate eq 'addDays(utcNow(),int(7),'yyyy-MM-dd')

[MarkH1972]

Thanks so much. I set up a test flow and it seems to be working just great. I will edit my "live" flow and start to use this from now on

[MarkH1972]

Thanks for all the help. I am slowly learning 🙂

I have submitted a related question here if anyone would like to help further

https://powerusers.microsoft.com/t5/Building-Flows/Send-email-to-specific-user-when-checkbox-is-tick...