Send reminder email to users that have not responded to on MS Form

[jap11]

Hi all!

I'm currently trying to build a fully automated process that once a month sends a list of people a link to a form to complete, then if those users have not completed the form within 7 days, it sends those people a reminder.

The automated monthly email is already setup and works fine but I'm struggling to find a way to automate checking the people that have not submitted the form and then sending those people a reminder.

I'm also conscious that because this a monthly recurring task, there may need to be something additional that archives all responses, so that when the flow recurrence begins again at the start of the month, it does not check responses from the previous month.

1. Recurrence email is sent out asking people to respond on form

2. After 7 days, the flow checks which people have not responded on the form this month

3. Sends those people a reminder to submit the form this month

4. All monthly responses are then archived into a separate share point list, including any people that have not responded and their responses can appear blank - that would then flag in the list

Note, it is not actual essential that the reminder email gets the person to submit the form, it is more around we can keep an audit of the requests and the reminder request.

Any help would be greatly appreciated! 😄

[jap11]

I've mad some progress on this and got it almost working however the apply to each seems to be causing the flow to send an email based on each item on the SharePoint list. What I'm asking it to do is check the SharePoint list for the name of the submitter in, then also check to see if there is an entry for this Month. 

Does submitter email = email address

AND

Is month = December-2022

If this returns as false, then this means the user has not submitted the form this month as there is no data in SharePoint list for this month. However, the flow is then sending the same email three times over and I can't work out why. There is 3 items in the SharePoint list so i'm assuming that is related???

The flow does not fail, it just isn't doing what I hoped. There is only one item on my list that the above applies to, therefore only one email should be sent, however it is sent multiple times.

If anyone understands where I'm going wrong or how I could resolve this that would be great! 🙂 

[RPLANTEFEVE]

Hi Josh,

I know that my answer is very late, but in case it can still help you or other persons with the same issue, I decided to post anyway.

I wanted to do something similar, that is how I ended up here. It took me a while to understand how power automate works (It was my first power automate flow) but I found a (decent) solution.

First, you need two lists. One with the email adresses of people the form has been send to, and one which contains the form responses.

Below you have an overview of the flow (the images overlap).

First you have a recurrent trigger, then you load the first list (with all the emails) and the second list (with the responses, where one column contain the email of the respondent). Be careful here to have the same case on both list for emails.

Then, you will have to create at least one variable, a boolean which I called has_answered.

The second variable is actually a workaround an unwanted behavior of power automate so maybe someone will have a better solution here. But it will contain the email address of the person you want to send the reminder to. I called it email_to_remind and set the type to string.

Now come the nested loops on all the emails of the first list, and all the emails of the second list. The tricky part is that you do not need to create the loop explicitly. Power automate will make them for you when you will set the parameters of the condition. So create a condition for equality of the two email adresses (one from each list). The loops will magically appear (but that's what bother me for the very last part of the flow). 

Now that you have the loops, you will need to set the boolean has_answered to false and email_to_remind to the value of the first list email at the beginning of the first loop. Then, inside the true section of the condition, set has_answered to true.

Ok now when the flow will loop through, if it finds in the second list the current email of the first list has_answered will be true at the end of the second loop.

Optionally, send an email to yourself containing the variables (has_answered, email_to_remind, the email of the first and second list) to help you debug if a problem occurs.

Finally, create a condition on the value of has_answered. If true, do nothing. If false, send an email to email_to_remind. 

If you try to set it to the email of the first list, power automate will create a for loop (because your list contain more than one mail). I did not find a way to tell power automate that I want to use the item of the first list selected by my first loop... Therefore the workaround with email_to_remind. If you let power automate create the unnecessary for loop, it will actually send a email to everyone (even the people which have answered) as many times as there is people who have not answered. So with 100 people in the list and 50 that have not answered it means 5000 emails sent (and each person will have 50 (or 49) spam emails).

[stir98]

Can you expand your flow so I can see the details?

[cryssya]

Hi @jap11

is it possible to help with information on how you created the first part of the flow? To send the initial e-mail with the link to the form? 

thank you.

[jap11]

Hi @cryssya - which part? the one shared in my screenshot or the one that I mentioned I had already got working in a separate flow?

You can setup a scheduled flow, say for example once a week, then send email.

Apologies if i have misunderstood your question.

[dfrugoli]

Would you be willing to export your flow and post it here? Your flow sounds like exactly what I am trying to accomplish. Thanks in advance!