Forum Discussion
How to send an email to a person using another spreadsheet to look up emails?
Hello,
I would like to simplify my workload. I receive payments from parents for our aftercare program. I made a form to help me with the data entry. How can I send an email to a parent based on matching the child's name? In other words, I have a separate spreadsheet already with the child's names in one column and the parent's email in another column. Is there a way to have a form when submitted match the child's name to the spreadsheet and then send it to the appropriate email address?
Hi Maria mariapgo , yes this can be done but not just using Forms, you need to build a simple flow in Power Automate as shown below:
1) first of all we have the spreadsheet with the names of the students and the email address of the parent. Select the rows & columns and from the Home menu select Format as Table. Any similarity between the names in my spreadsheet and people presently living or nearly dead is purely coincidental.
2) Next we have the form:
3) In Power Automate create an instant cloud flow. The trigger is the Forms "when a new response is submitted" where you select your form.
The first action after this trigger is always the Forms "get response details". Again you select the form and then when you click in the Response Id field you select Response Id from the dynamic content box that appears on the right.
3) Add an Excel for Business action "list rows present in a table" and select the location, library and your file and table as shown below. We only want to bring back one email address so add a filter query which in my example is Student eq '{Name of child}' where Student is the column in your spreadsheet with the name of the student, eq is equals, then inside single quotes select the question in your form with the name of the child.
4) Add a send an email (v2) action and it will wrap itself in an apply to each even though we are only bringing back 1 email address. I(n the To field select parent's email address from the dynamic content, then add the relevant questions into the email.
5) This is the example we have used:
6) And this is the result:
Rob
Los Gallardos
Microsoft Power Automate Community Super User
