How to extract Body og Email into a existing cell in Excel File?

[Guestuser1]

Power Automate Cloud

[mathiaskjeldsen]

Actions required:

1) use Get Emails action

2) use Get email body action (save it in variable emailBody)

3) use Launch Excel action

4) use Write to Excel worksheet action (here you specify the cell of where you want the content of emailBody)

5) use Close Excel action and configure it to Save/Save as

Let me know if something is unclear. 

--

If this has helped you, please mark my reply as the Solution to your problem.

[BhawnaBansal777]

You can use the following steps to achieve the requirement.

For e.g. My mail is 

and i want to add the row in the excel using a power automate flow.

Use the following steps to create a flow.

On successful run you will find the same content in the excel.

Let me know if you need further help 😊

[Guestuser1]

I see, but I do not want to add a new row as the row is already been created. I just need to update the row, with the body of the email.

[BhawnaBansal777]

You can use update row action too, it is available.

[Guestuser1]

There was a issue and my full problem was not added, sorry. Here is the full problem: I am trying to extract the body of emails I get into a existing cell in Excel File. The body must be added to the correct cell, for example by matching "From name" with the name in the Excel File. Thank you. I am not using Desktop, just could not find the online version.

[Guestuser1]

Oh ok, but please see the full problem below.

[BhawnaBansal777]

Okay - So for example i have this excel sheet with me 2 columns, first is sender which contains the sender email "someone@domain.com" and other is Text " which will get updated with the content of the email".

So, here i want a flow which updates the content in "Text" column in the row when mail is received from bhawna@domai.com.

I have used the below steps.

To give you more clarity for get rows, correctly define the path, file , table, Key Column in which you want to find the Key value that you want to find.

Then parse JSON to use the "ItemInternalId" for Key Value in "Update Row"

Check what value your ItemInternalId is generating and accordingly select the relevant column above it to check the value correctly.

As in my case ItemInternalId is generating the same value as of Sender's email so I used Key Column as "Sender" and Key Value as "ItemInternalId".

After successful run, My output in Excel file.

[Guestuser1]

Can you explain what you wrote in Parse JSON action? I do not know what this is.

[BhawnaBansal777]

I will not be able to tell you exactly was is passed as it completely differs for each and every one as per what is received in previous step "body".

[Guestuser1]

Then what am i Suppose to write? Content: Body? What is Schema?

[BhawnaBansal777 · Accepted solution]

Select the "body" from the previous step and take out the output in the notepad for one of the flow result where your previous action gave you some result.

As for example here: In the previous "Get a row" action i received an Output.

Copy the text which is available to you in the output and click on "Generate from Sample" in the Parse JSON.

and paste the value in it between []

So, it will look like 

and click "Done"

After that your schema will be visible as shown below, and if you see the below in your schema.

 "type": "array",

    "items": {

remove it as shown below.

and also make sure that at the end of Schema, there are required columns, kindly exclude those which could be null. I will say just keep "Sender" as required and remove others.

and it is ready. Save and test it with the last trigger which successfully ran 😊

[Guestuser1]

it worked, thank you so much. but the body has many unnecessary lines, possible to remove them?

[BhawnaBansal777]

Yes, you can use split function from where you want to split the content and use the index of the content which you want to take forward.

Compose a variable and add a expression 

split(outputs('Compose'),'add_here_the_separtor')[0]

here [0] means the first split result before separator that we will get.

Now use this compose variable in the excel sheet body.

[Guestuser1]

ok, but there is another problem. The HTML to Text does not turn the response email to text, but rather the whole discussion. How can I make it so that it only selects the response email.

[BhawnaBansal777]

I have to see it though - but as i can see in the content after every response we have 

"--------------------------------------------------------------------------------"

You can split the whole content with that and use [0] index to get the most recent response.

Try this once.

[Guestuser1]

so when you use a different email, the flow does not add
From: blblabalba
Sent: blablab
To: blbalbal
Subject: blablaba

Then the previous email?

[BhawnaBansal777]

Apologies, but I am not able to understand what you are trying to say.

Could you please be more clear and elaborate it.

[Guestuser1]

this is what i am getting in my excel cell when i use a different email from the one, I copied in to the Parse JSON Schema.

I only want the response in cell.

[BhawnaBansal777]

Have you tried this ?

After every response we have 

"--------------------------------------------------------------------------------"

You can split the whole content with that and use [0] index to get the most recent response.