Power Automate Parse Email Body [Excel & SharePoint List]

Recently, I was working on a Power Automate task that required me to extract data from emails and save it in a SharePoint list.

In this article, I will explain all about Power Automate read email body, Power Automate get email body text, and Power Automate parse email body with examples.

Also, we will discuss Power Automate extract data from email body to excel and Power Automate parse email body to SharePoint list.

Power Automate Extract Data from Email Body to Excel

To extract text from the email body and save it to an Excel spreadsheet, follow the below steps.

First, we need to create an Excel spreadsheet with all the details like Task Title, Task Assigned To, and Task Priority. Select the Excel shell and format it as a table, as shown in the image below:

Power Automate read email body

The screenshot below displays the email with all the details that I need to save in the Excel sheet.

Power Automate parse html email body to Excel

The Excel sheet will appear like the below:

Power Automate extract text from email

To work around this, check out the below points:

1. Open the Power Automate Home page -> Click +Create -> Select ‘Automated cloud flow’ -> In the next window, Provide a flow name & select trigger ‘When a new email arrives (v3)’ -> Tap Create button.

Inside the trigger, select the below details:

  • Folder: Select the folder as Inbox.
  • Subject Filter: Provide the subject line to filter the emails received.
Power Automate get email content

2. In the next step, select and add ‘Html table to text‘ action.

  • Content: Select Body under When a new email arrives from dynamic content.
Power Automate extract text from email body

3. Then, select ‘Add a row into a table.’ Configure the required parameters:

  • Location: Select the location where the Excel file is saved.
  • Document Library: Choose the specific documents from drop-down.
  • File: Select the files from the open library icon.
  • Table: Choose the table name.
See also  Power bi gauge chart - How to use with examples

In the fields of excel, I will add the expression to get the data in rows of Excel.

  • Task Title: @{split(split(outputs(‘Html_to_text’)?[‘body’],’:’)[1],’,’)[0]}
  • Task Assigned To: @{split(split(outputs(‘Html_to_text’)?[‘body’],’:’)[2],’,’)[0]}
  • Task Priority: @{split(split(outputs(‘Html_to_text’)?[‘body’],’:’)[3],’,’)[0]}
Power Automate get text from email body

4. Once the flow gets ready, Save the flow. Click on Test-> Select Manually radio button. Click Runflow.

5. When the flow starts running, send an email with the Task Details as shown in the below figure:

Power Automate extract data from email

6. When the flow runs successfully, you can see the task details saved in the Excel spreadsheet.

Refer to the image below:

Power Automate extract text from email

This is how to extract text from email and parse text into the Excel spreadsheet.

Power Automate Parse Email Body to SharePoint List

To get the email text body into the SharePoint list by using Power Automate, check the below-mentioned example.

Here, I have a SharePoint list [Product Details]with a few columns, as shown in the figure below:

Power Automate parse email body to SharePoint list

Example:

When an email arrives with the subject Product Details, I want to extract email content and store it in the SharePoint list.

Go through the steps:

1. Take the When an email arrives[V3] flow action and provide details like Folder and Subject Filter, as shown in the above example.

  • Folder: Select Inbox of Outlook
  • Subject Filter: Take Product Details
Power Automate extract email content to SharePoint list

2. To extract plain text from the HTML content of the arrived email body, click on The Next step and select the ‘HTML to text‘ action.

So, in the content parameter, add the dynamic content, i.e., Body -When a new email arrives.

Power Automate split email body

3. Take Compose flow action to split the information received in the email.

See also  How to Select First Item in a Power Apps Gallery

Inputs: Insert the below expression.

split(outputs('Html_to_text')?['body'],'|')

Here, it will split the email text based on ‘|‘ as a delimiter.

Power Automate extract text from email body to SharePoint list

4. Now, add the Create item flow action from the SharePoint list.

  • Site Address: Select a specific SharePoint site from the drop-down.
  • List Name: Choose a list name under the SharePoint site.
  • Title: outputs(‘Compose’)[1]
  • Product Name: outputs(‘Compose’)[3]
  • Product Type: outputs(‘Compose’)[5]
  • Billing Date: outputs(‘Compose’)[7]
  • Estimated Delivery: outputs(‘Compose’)[9]
Extract html table Text  from email in Power Automate

Once the flow is completed, save and test the flow manually.

5. When an email arrives with the provided subject filter, the flow triggers and runs the flow.

Extract text from email body Power Automate

6. The flow runs successfully and saves the email text in the SharePoint list.

Look at the image below:

Power Automate extract email body text

This way, we can parse text messages from the email body to the SharePoint list using Power Automate.

Also, you may like:

Conclusion

I hope this Power Automate tutorial guide is helpful and understanding. In this article, I have covered how to work with Power Automate extract data from email body to excel and Power Automate parse email body to SharePoint list with examples.

  • Hi this works great, however should there be any extra text in the body of the email after the last data: column for example the ratings which is the last data column, this extra data ends up in the row as well. What expression can you use for it not to include the extra data in the flow run and avoid that extra text showing up in the excel file row column for ratings as an example?

  • One thing I wanted to elaborate on was the extra data that ends up in the ratings column not the row, therefore what can be added to the expression to for ratings the last data column for example that excludes the extra data, for example some email clients include footer data such as where the email is sent from and such.

  • Hi I believe you do that step in the excel when you create the excel file, once you create your desired columns you the click any where on the excel file rows/columns and go to insert, then select table. That converts your file to table. Then when you get to the step to add a role into a table that table one should be an option right after the excel file in the field.

  • im tired of all these tutorials assuming people put bars in their email. That is not practical, nobody does that unless it was a purpose built email for within your organization, you rarely will get an email nicely delimited from an outside source, someone needs to do a tutorial on how to parse without the nicely barred examples.

  • Hey there! i am very bad with coding and starting to learn a lot here. My Question:
    If the email i receive have the following message:
    “A user account has been created or modified.

    Username: [email protected]
    Temporary password: ffffrrrrrrwwww”

    How can i extract only the email?
    trim(first(split(last(split(body(‘Html_to_text’),’Username:’)),’Temporary password’)))

    Would have been my idea?

  • I’m looking for a similar solution as the people mentioned above, emails aren’t structured. and even when they are (which i have) the HTML to Text merges unknown data into various lines. it’d be better to parse the HTML then the Text. the reason i say this is because my emails contain addresses and those all vary in length and thats when lines get thrown off. of course i’m also doing outputs based on lines.

    Any suggestion for how i can go about writting the email directly from HTML to an array by line or table by line. for each line in the email i’d be happy to have it broken into outputs by line.

    Thanks,
    Evan

  • >