October 13, 2024

Power Automate: How to send the Form’s answers by email?

I was contacted recently by Laura with the following question:

I am using Forms to create an exam, and would like the responder’s answers to also be sent to them as an attachment along with my “send an email notification (V3)”. Is that even possible? I saw how to create an attachment and all of that, but when you go to open the attachment in my test run, there is no selected program to view the answers/no way to view them.

Yes, it’s possible so let’s see how to do it.

The form

Let’s start with a simple demo form.

It’s not necessary to us what the questions are since all forms will be different, but how to take the questions and send them.

Now that we have the form, let’s look at Power Automate and see how we can get the information.

The Power Automate

The Power Automate will start as always when we want to get information from a Form. “When a new response is submitted Trigger” followed by “Get Response Details” action. If you’re not aware, the “When a new response is submitted Trigger” will only return the form response identifier, so we need another step to get the details of the form.

Please notice that we need to use the same form information; otherwise, it will fail.

Prepare to send

Now that we have the information, we need to prepare it to send it in an email. We can use the “Create HTML table” action to have a lovely table with questions and answers.

The “From” will contain the “body” of the form since it’s where we’ll fetch the information to build the columns.

But what would happen if we leave it as “Automatic”? The email will look a bit strange because:

  1. We will get all the values in the body – This will be bad since we’re only interested in the answers to the questions, not all the other fields that Power Automate fetches from Forms.
  2. No format in the column names – Microsoft Forms returns the information with the names of the columns as identifiers and not names. So we would get an email with the identifiers and not the correct

Here’s an example of the wrong way to do this:

If we run it, we even have an error:

The ‘from’ property value in the ‘table’ action inputs is of type ‘Object’. The value must be of type ‘Array’.

I did this to show you that the “Create HTML table” action requires an array and not an object returned by the “Get Response Details” action.

We can quickly generate an array using the “createArray function” that transforms the object into an array of only one object.

createArray(outputs('Get_response_details')?['body'])

With this formula, we’re converting the value in the body (the “Object” in the error message) to a valid array that we can iterate. 

At this time you’re asking. Why are we using the “Create HTML table” action in the first place? It’s a good question; the answer is to make our lives easier and make the flow much more readable. We can create the structure in the “Send email notification” action, but then we would generate the HTML ourselves (see the next section if this is what you want). It’s easier and nicer to map the values and use the result in the email.

Now let’s look at how to build the table.

Building the table

This is not what we want, right? We want the questions so let’s build them. First, write the text that you want to show in the email and then pick the value from the list:

Here’s what it looks like.

Repeat the other questions, and it looks something like this:

Here’s the result.

Sending the answers to the responder’s email

Now let’s build the “Send email notification” action with the results and send send the Form’s answers to the responder’s email. According to Laura, we want to send the results to the person who submitted them and have that information in the Form’s results.

Include the results that we built in the previous step like this:

Here’s what we get in the email.

I’m not too fond of the output

If you don’t want it to display it like this then we need to do a bit more work. If we look at the HTML that it returns is as follows:

<table>
    <thead>
        <tr>
            <th>What&#39;s the main purpose of a quiz?</th>
            <th>What&#39;s the result of the following equation</th>
            <th>In the following set of options what is the correct order?</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>Understand if you know about a topic</td>
            <td>x=4</td>
            <td>x + 15 = 6, x + 8 = 12, y – 9 = 25</td>
        </tr>
    </tbody>
</table>

We need to reverse it a bit so that we have two columns. “Question” and “answer” followed by the information. So we need something like this:

<table>
    <thead>
        <tr>
            <th>Question</th>
            <th>Answer</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>What&#39;s the main purpose of a quiz?</td>
            <td>Understand if you know about a topic</td>
        </tr>
    </tbody>
</table>

If you don’t know HTML don’t worry. What you need to do is that the “<tr>” is the “ROW” while the “<td>” is the column, meaning that we need something like this for each answer:

<tr>
	<td>INSERT HERE THE QUESTION/td>
    <td>INSERT HERE THE ANSWER</td>
</tr>

Since we’re doing this manually, we don’t need the “Create HTML table” action so let’s remove it and write everything in the email notification.

Let’s replicate it for all questions, and we’ll get the following:

Here’s the email that we receive.

Before we go, a comment. As you notice, Microsoft customizes the email sent with its logo, etc., so the table we generate is also formatted. We can do more customization, but we would always be “fighting” Microsoft’s stylesheet. In this case, I would always let it go since if Microsoft changes how the emails look, we risk future emails looking bad.

Final thoughts

It may look like a lot of steps, but I wanted to leave as much detail as possible and give you some alternatives in case you didn’t like the way it looked the first time. The point is that it’s possible to achieve a nice-looking email with the answers whiteout having to do it manually. Consider that sending the Form’s answers to the responder’s email can be also done using an attachment but that would imply a lot more work for you since you needed to generate a PDF with the results. I think this solution would generate a good result for you without having to make the Flow too complex. 

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and check out other Power Automate-related articles here or if you want to check other solved problems, click here.

Photo by Daria Nepriakhina 🇺🇦 on Unsplash

 

Manuel Gomes

I have 18 years of experience in automation, project management, and development. In addition to that, I have been writing for this website for over 3 years now, providing readers with valuable insights and information. I hope my expertise allows me to create compelling, informative content that resonates with the audience.

View all posts by Manuel Gomes →

4 thoughts on “Power Automate: How to send the Form’s answers by email?

    1. Hi Joe,
      You can use the following formula:
      createArray(outputs(‘Get_response_details’)?[‘body’])
      I’ve edited the article to add this, so thanks for the question.
      Cheers
      Manuel

  1. Hi Manuel,
    Thank you for the interesting article. I have noticed that the body in the “Create HTML” step doesn’t have headers (it has ids instead of headers). Is it possible to replace ids with headers automatically without manual overwriting?

Leave a Reply

Your email address will not be published. Required fields are marked *

Mastodon