Answered

how to extract email body to excel?

(0) Share

Report

Posted on by FLT_Rizki

Hello, I'm a Student who is new to Power Automate and I need some help with a workflow. The workflow will extract information on an email body from a specific email and upload it into an excel file; it will continue to look for the same email and update the excel list with the email body information. kindly advice me me on this process. Thank you.

Categories:

Power Automate Desktop

I have the same question (1)

All responses (8)

Answers (1)

v-yetonggu-msft on at

Like (0)

Report
Hi @FLT_Rizki ,
If it's convenient, could you please provide the specific content of your email and the columns in the excel table that need to be updated?
I did a test for your reference.
In my scenario:
1)If you want to extract body information from a specific email, I suggest you use ‘Subject Filter’ to get emails with the same subject.
2)Use split’ function and specific field index to extract information.
3)decodeUriComponent('%0A') means line break in flow. The meaning of this code is to extract the information after the Application Name: field and before the new line.

split(split(outputs('Html_to_text')?['body'],decodeUriComponent('%0A'))[0],'Application Name:')[1]

My Outlook Inbox:
My Excel Table:
You have to change your formula based on the content of the body of your email to extract the information you need.

Best Regards,
Sunshine Gu

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report

Are you doing this is Power Automate cloud flows or desktop flows? @v-yetonggu-msft has suggested a solution for cases when you'd do it in cloud flows (which makes a lot of sense, given the type of flow you've described). But you posted this in the forums for desktop flows, so I feel like I have to ask.

Was this reply helpful? Yes No
v-yetonggu-msft on at

Like (0)

Report

Hi @Agnius ，
What I am executing is cloud flow. This cloud flow can also be done.

Best Regards,
Sunshine Gu

Was this reply helpful? Yes No
FLT_Rizki 49 on at

Like (0)

Report

Excel format

this are the information i need to extra from the email into the Excel sheet with that order.

my Outlook inbox email i'm extracting from:
subject title:

the information i need to extract

i'm using Power Automate Desktop and i don't see any HTML function.

Was this reply helpful? Yes No
FLT_Rizki 49 on at

Like (0)

Report

i'm actually using the Desktop flow has the cloud flow have some action that is not found in the desktop version.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (1)

Report

If you are using Power Automate Desktop, the suggestion by @v-yetonggu-msft will not work, as that is based on cloud flows.

For desktop flows, what you need to do is retrieve the email first. Depending on what kind of connection you're using, there are several actions to do that. Assuming you're using the Outlook actions, you need to use Launch Outlook first, and then use Retrieve email messages from Outlook. This will result in a list of email messages stored in %RetrievedEmails%.

You should then use a loop, such as For each to process each email message. If you use For each, it will generate a %CurrentItem% for each message in the iterations.

You can then use the properties of the variable to get the email body. A variable of type "Outlook mail message" has .Body and .BodyText properties that can be used to retrieve the body into a variable and then parse it.

Unfortunately, there is no direct way to actually get it as a table, regardless of it being formatted as an HTML table in the email body. Both .Body and .BodyText will return the entire body as a plain text blob. What you need to do is use Parse text with regular expressions enabled to parse %CurrentItem.Body% or %CurrentItem.BodyText% to retrieve your values based on the keywords next to them.

And then you can write that to your Excel file.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
FLT_Rizki 49 on at

Like (0)

Report

Hi Agnius, is this the way the workflow look like?

Was this reply helpful? Yes No
Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report

Yes, except for the Parse text actions. You should use %CurrentItem.Body% as the text to parse. And then write a regular expression pattern as the text to search for.

See this video for reference on regular expressions: https://youtu.be/5cPMEBH5tj8?si=FhtbmbB-xoTA3Dyo

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No