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.
Step 1: Create a SharePoint List
As I have already created a Product review list in SharePoint Online which had 3 columns:
Column | Column Name |
---|---|
Title | Single Line of text |
Customer Name | Person |
Rating | Choice |
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.
Now Provide the Flow name, and select ‘When a new email arrives to trigger the flow. Then click on create.
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.
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.
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'],'|')
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]
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.
Then you can check the flow in power automate ran successfully.
At last, you can check the product review SharePoint list the item gets created.
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.
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.
Now Provide the Flow name, and select the trigger ‘When a new email arrives (V3)‘. Then click on Create.
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.
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.
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))
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.
Now you can check your flow ran successfully or not.
Once your Flow ran successfully you can check results in the Excel sheet which you have created.
You may like the following Power Automate tutorials:
- Power Automate Date Functions
- Power Automate Get Data from Excel on SharePoint
- Power Automate create an HTML table
- Power Automate Forms to Excel
- Power Automate Switch case
- Power Automate String Functions
- Power Automate send email based on form response
- Power Automate Array Variable
- Power Automate list rows present in a table filter query
- Power Automate Create Document Library
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
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com
Thanks! how did you get “Table1” under table field.
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?
any fix for that please?
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.
have you found a solution?
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?
OMG Thank you so much for this ! it worked !!!!
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