Power Automate Parse Email Body

In this Power automate tutorial, we will see how to parse email bodies using Power automate. We will see an example on Power automate parse email body.

And also we will discuss the below points:

  • Microsoft power automate parse email body to SharePoint List
  • Power automate parse email body to excel

Microsoft Power Automate parse email body to SharePoint List

Here we will how to parse the email body and store the content in a SharePoint list.

So, we have a SharePoint list called product review, like below when an email is received in outlook from a customer.

We will check the email body and extract the actual content and store it in the SharePoint list using Microsoft Flow.

 power automate parse email body
power automate parse email body

Step 1: Create a SharePoint List

As I have already created a Product review list in SharePoint Online which had 3 columns:

ColumnColumn Name
TitleSingle Line of text
Customer NamePerson
RatingChoice
Product Review
Microsoft power automate parse email body
Microsoft power automate parse email body

Once your SharePoint List is created, you can create a flow in Power Automate.

Step 2: Create a Flow in Power Automate

Now we will create a flow that will trigger when an email arrives at the outlook inbox.

Log in to the Power Automate, then click on create-> Automated cloud Flow.

Microsoft power automate parse email body
Microsoft power automate parse email body

Now Provide the Flow name, and select ‘When a new email arrives to trigger the flow. Then click on create.

power automate parse email body
power automate parse email body

Step 3: When a new email arrives

In this step, provide the folder of outlook where the new email arrives. Then here we provide the Subject Filter as Product Review, so only those email subjects containing Product review will trigger the flow otherwise flow will not trigger.

power automate parse email body
power automate parse email body

Step 4: Convert The Html table to text

To extract the plain text from the Html content of the arrived email body, for this click on The Next step, then select ‘Html to Text‘ action.

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

power automate parse email body content
power automate parse email body content

Step 5: Split the text to get the actual content

In this step, we will split out the entire body to get the actual content from the email. So for this click on the Next step, then click on the ‘Compose‘ Action.

So here we will split after each pipe ‘|’. In the Input box, write the below expression to split the text.

split(outputs('Html_to_text')?['body'],'|')
power automate parse email body content
power automate parse email body content

Read Power Automate Compose

Step 6: Store the actual content in the SharePoint list

To store the actual content in the SharePoint list, click on the next step then select ‘Create item‘- SharePoint.

Now provide the site address and list name where you want to create an item.

Then in the title field add the below expression, which will extract the value:

outputs('Compose')[1]

Similarly, you can add the expression for Customer name and Rating respectively:

outputs('Compose')[3]
outputs('Compose')[5]
power automate parse email body to sharepoint list
power automate parse email body to sharepoint list

Once your flow is ready, we can run the flow manually.

Step 7: Run the Flow

To run the flow click on the save and run the flow manually. Now send an email to outlook inbox to trigger the flow.

After that, you will get an email notification in your outlook inbox.

power automate parse email body to sharepoint list
power automate parse email body to sharepoint list

Then you can check the flow in power automate ran successfully.

power automate parse email body to sharepoint list
power automate parse email body to sharepoint list

At last, you can check the product review SharePoint list the item gets created.

power automate parse email body to sharepoint list
power automate parse email body to sharepoint list

Read Power Automate send an email with an attachment from SharePoint

Power automate parse email body to excel

Here we will see how to store parse email body content to excel sheet using Microsoft Flow.

So here we will extract the content from the email and store the actual content in the Excel table using Power automate.

Parse email body to excel using Power automate

Step 1: Create an Excel table

I have already created an excel table of Product reviews like below, then I store the excel sheet in the SharePoint document library or you can store it into One drive.

power automate parse email body to excel
power automate parse email body to excel

Step 2: Create a Flow

We will create a flow that will trigger when an email arrives at the outlook inbox. Log in to the Power automate, then click on create and select Automated Cloud Flow.

power automate parse email body to excel
power automate parse email body to excel

Now Provide the Flow name, and select the trigger ‘When a new email arrives (V3)‘. Then click on Create.

Microsoft Flow Parse email and extract information
Microsoft Flow Parse email and extract information

Step 3: When an email arrives

Now in this step provide the Folder, where your email get stored in outlook, and then add the subject query as the name of the subject i.e. Product Review.

power automate split email body
power automate split email body

Step 4: Html table to text

In this step, we will convert the Html table content to text which we extract from the previous step.

Click on the Next step and select ‘Html table to text‘ action, then provide the content as the body – When a new email arrives.

Microsoft power automate parse email body
Microsoft power automate parse email body

Step 5: Add content to the excel table

To add actual content to the excel table which we have extracted in the above step.

For this click on the Next step and then select ‘Add a row into a table’. Next, provide the Location, Document Library, File, and table where you have stored your excel sheet.

In the Fields of excel, we will add the expression to get the data in rows of excel.

For product:

trim(first(split(last(split(body('Html_to_text'),'Product:')),'Customer Name')))

For Customer Name:

trim(first(split(last(split(body('Html_to_text'),'Customer Name:')),'Rating')))

For Rating:

first(skip(split(body('Html_to_text'),'Rating:'),1))
power automate parse email body
power automate parse email body

Once your Flow is ready you can run the flow manually.

Step 6: Run the Flow

To run the flow, click on Save, and then send an email to the outlook to trigger the flow.

Parsing text from email with Microsoft Flow
Parsing text from email with Microsoft Flow

Now you can check your flow ran successfully or not.

microsoft flow extract text from email body
Microsoft flow extract text from email body

Once your Flow ran successfully you can check results in the Excel sheet which you have created.

power automate parse email body to excel
power automate parse email body to excel

You may like the following Power Automate tutorials:

In this Power automate tutorial we learned about Power automate parse email body. And also we discuss the below points:

  • Microsoft power automate parse email body to SharePoint List
  • Power automate parse email body to excel
  • 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

  • >