Software Application

Blog Post

Power Automate | How to parse email and save it on a google sheet | In 5 Min

Power Automate
Hello everyone, today we are going to use Microsoft power automate flow to extract specific details from emails.

Why do we need to extract data from emails ?

In day to day life, normally we get tons of emails daily, and in special cases you think you are a HR manager of a company and you get 100s of emails daily from candidates. After the closing date of your interview notice, you have to inform something to all of your candidates. Can you imagine how hard this process is when you get more than 300 emails? How easy if you have an email list of candidates that contains the emails and names.
As another example, think you are a marketing manager or owner of a digital marketing company. After a marketing campaign there are 1000s of customers emailing you to ask something. If you want to make an email list of your customers, you have to go through all the emails and copy them one by one. How difficult is this process and will it take more time? How easy would it be if you had an auto-generated email list?
That’s how the email parser and power automate comes to the stage.

What is Power Automate ?

Power Automate is a service that helps you create automated workflows between your favorite apps and services to synchronize files, get notifications, collect data, and more. There are many other automation tools like Zapier, parabola, but today we are going to use Microsoft power automate.

Let's start Power Automate and Email Parser Tutorial


Step 1

Note : Full video tutorial attached in the bottom of this page.

First of all you need a Microsoft power automate account. You can simply go to this site and simply create an account https://powerautomate.microsoft.com/en-us/ . You can start this free.

Step 2

After creating an account, signup. It will redirect to the dashboard. Now you need to select “Flow” from your left side navigation bar.
Power Sidebar

Step 3

Then you need to create new flow. You can do this by clicking the ‘New flow’ button.
New Flow
Then select ‘Automated Cloud Flow‘ from the popup box.
email parser
Then give a name for your flow and then you have to decide what service needs to use our automation. In my case I use Outlook mailbox.(When a new email arrives (V3) Office 365 Outlook)
email parser
email parser
Then click on ‘Create’ Button.

Step 4

In this step we are going to create a flow. There are few steps we have to design like below.

Step 4.1

At the beginning, you can see a box like below.
email parser
This is our first step in the flow. You have to click ‘show advanced option’ and then have to give a specific subject. In my case I gave ‘New Interview’ as a subject filter.
email parser

Step 4.2

Now we need to add a new step to convert email HTML body to text. First click on ‘New step’ Button and then select ‘Html to text’ option from the list like below.
email parser
Then you will have another box of ‘Html to text’. Now click on the editor and then select ‘Body’ from the list like below.
email parser
After selection the box should be like this.
email parser

Step 4.3

Now in this step we are going to extract our data from the email body and save it into a google sheet.
We need to start a new flow. It should be the ‘Insert Row’. Before that log into your google account using you given to the power automation. And go to the ‘Google Drive’ and create a folder named ‘EmailParser’. You can have a different name. And inside of this folder create a new worksheet and rename it as a ‘Email List’. That name is also be a different if you need.
Then add the first 4 columns as Name, Email, County, Phone like below.
email parser
Select ‘Insert Row‘ option from the power automate new step.
email parser
Then we need to give a file and worksheet to the system like below.
email parser
email parser
email parser
At this point, we need an email template to decide split conditions. In my case I used this type of email.
email parser
Using the above template we can decide our splitting conditions easily.
For Name :-  split(split(outputs('Html_to_text')?['body'],'Name:')[1],'Email:')[0]
For Email:-  split(split(outputs('Html_to_text')?['body'],'Email:')[1],'Country:')[0]
For Country:-  split(split(outputs('Html_to_text')?['body'],'Country:')[1],'Phone:')[0]
For Phone:-  split(outputs('Html_to_text')?['body'],'Phone:')[1]
Then click on the ‘Name’ Box and click on ‘Expressions’ from the popup list.
email parser
Then past the ‘split(split(outputs(‘Html_to_text’)?[‘body’],’Name:’)[1],’Email:’)[0]’ into ‘fx’ and click ‘ok’. Do the same for other fields.
email parser
Now save your flow and test it. To test send an email like this with subject ‘New Interview’.
email parser
Then check your google sheet.
email parser
This is a way to use power automate. Try yourself and let me know if you need any help. Thank you.

Full Video Tutorial