Using a flow to extract email body details

[Willo]

Hello, 

Is there a way to leverage a flow to pull out email body and subject details without using a third party service? I'm looking to move details from an email into a sharepoint list.

Thank you

[v-yamao-msft · Accepted solution]

Hi @Willo,

Yes, it could be achieved by using WDL function in flow.

Say your email body would be always the following format:

Then you could create a flow likes below. In this case, I will show how to extract the subject from the email body, please take it for a reference.

The flow is triggered by When a new email arrives, then convert the email body from Html to text.

In action Compose, use the code to get the total length of the body:

length(body('Html_to_text'))

In Compose2, use the following code:

add(indexOf(body('Html_to_text'),'Subject'),9)

In compose3, use the following code:

indexOf(body('Html_to_text'),'Start time')

In Compose4, use the following code:

substring(body('Html_to_text'),outputs('Compose_2'),sub(outputs('Compose_3'),outputs('Compose_2')))

It works fine and the subject can be extracted from the email body successfully.

More details about the functions at here:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference

Best regards,

Mabel Mao

[GabrielStJohn]

Hello, @Willo!

Thank you for posting on the Flow Community Forum! Have you had an opportunity to apply @v-yamao-msft‘s recommendation to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

Thank you for being an active member of the Flow Community!

-Gabriel
Flow Community Manager

[Robert-RWEUK]

Could you explain how you then create the list itemfrom the extracted text items?  This was the OP's original aim, and we have a similar problem.

Regards

[BBAAK]

I was able to do this by making sure I had a connection for SharePoint online in my connectors.

Then I selected after "compose 4", Create Item for SharePoint, then by putting in the address of the site in SharePoint online, then selecting the list,

Then used the dynamic content selector to get the output for Compose 4

[dee2005]

Hi @v-yamao-msft ,

I know this is quite an old post but i hope you will be able to help. Could you explain each of the various compose steps? I'm especially curios why you have a 9 in compose 2?

Seeing this is quite old is there any new and better ways to do this without a third party services of course.

[mmangiante]

Hi @dee2005 

---

@dee2005 wrote:

Hi @v-yamao-msft ,

I know this is quite an old post but i hope you will be able to help. Could you explain each of the various compose steps? I'm especially curios why you have a 9 in compose 2?

Seeing this is quite old is there any new and better ways to do this without a third party services of course.

---

I supposed that @v-yamao-msft ha used 9 because if you see, you have "Subject" plus ":" plus " " (blank space), so the total characters are 9.

--

Regards

Marco

[jainil]

@v-yamao-msft  So the problem I am facing is :  I am getting an email , in email 's body i am getting an email address"  , I need to extract that . Can you suggest something?

[astampf]

and by using the "TRIM" expression, this even works when using a table within the html-email-message!

[astampf]

you would need to identify the start and the end of your email address. maybe seach for the "@" and the nearest "space" before and after this string?

[PaulaDawn]

What if there is a space or a line before the data?  

ItemStock Number Unit PriceDetail TotalStudent ID Card 4182246$0.00$0.00ID
Number: 10000000      Subtotal:$0.00   Shipping:$1.00   Tax:$0.00   Total:$1.00

[AH7]

This worked awesome! Thanks!  
Appreciate the screenshots as well!

[Anonymous]

@v-yamao-msft thanks, this helped me a lot. A couple of questions though
- what is the purpose of the total length of the body in the first Compose action?

- how can I find the index of a string that is between newline characters? There are two instances of the same search string in my email body, I need the second one, which is '\nMySearchString\n' not just 'MySearchString', but with \n it returns the index of the first newline character it finds. I ended up adding 158 characters to the first index rather than searching for the second.

- would there be any advantage in using variables to store the result of the expressions?

[arondiaskevin]

Hello,

 Do you have some exemples, for insert this informations in the excel?

[ninjaryder1st]

@v-yamao-msft ,

Does the "html to text (preview) step need to be added prior to the Compose actions?  It seems the *content is needed for that action, yet nothing was provided in your example flow.    can you clarify if its needed and what goes in the html to text content?   

I don't have a nicely defined "Start Time" like your example.   My repeating email contains a bullet marker an date that changes..  anyway to compose/define this to extract date when followed by bullet in the email body?  

[astampf]

Hey! I learned a lot about this issue, here details that are hopefully useful:

a) YES, you need Html to text whenever your source text is in html (such as an email's body) 

b) instead of IndexOf + Substring .. much better with two nested SPLITs

Details and explanations:

a) example usually: Trigger was "new email received in Mailbox"?

you need a loop [here called "apply to all emails"], but this is built automatically - don't worry.

within the HTML to Text, your source is "items('Apply_to_all_Emails')?['body']"

the "body" might start with something like this:

<p><html><head>\r\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"><meta content=\"IE=edge\"><meta name=\"viewport\" content=\"width=device-width, initial-scale=1\"><style type=\"text/css\">\r\n<!--\r\np\r\n\t{margin:10px 0;\r\n\tpadding:0}\r\ntable\r\n\t{border-collapse:collapse}\r\nh1, h2, h3, h4, h5, h6\r\n\t{display:block;\r\n\tmargin:0;\r\n\tpadding:0}\r\nimg, a img\r\n\t{border:0;\r\n\theight:auto;\r\n\toutline:none;\r\n\ttext-decoration:none}\r\nbody, #bodyTable, ....... ..... <table border=\"0\" cellspacing=\"0\" class=\"mcnTextContentContainer\" width=\"100%\" style=\"min-width:100%!important; background-color:#404040\"><tbody><tr><td valign=\"top\" class=\"mcnTextContent\"  .... <span style=\"font-size:12px\">zum nachfolgenden Vorgang wurden vom zuständigen&nbsp;Underwriter der Versicherer (Demo) die Police eingereicht:</span> ...... </div></body></html></p>"

 

you might think of using "items('Apply_to_all_Emails')?['bodyPreview']"  which is "plain text" only; but at the same time, it's cut off. so not useful.

Using the HTML to Text step, the output (of example above) might look something like this:

[EXTERNAL SENDER]

Frankfurt am Main, den 17.11.22

Guten Tag,

zum nachfolgenden Vorgang wurden vom zuständigen Underwriter der Versicherer (Demo) die Police eingereicht:

b) and second comment - super elegant:

In my example, I want to cut out this information "in the middle:

wurden vom zuständigen Underwriter der Versicherer (Demo) die Police eingereicht

so in Compose (Verfassen), or equally when setting a Variable value, the expression is as follows:

trim(split(split(outputs('text'), 'Underwriter der')[1], 'die Police eingereicht')[0])

- Text before my "desired piece of information": "Underwriter der"

- Text after my "desired piece of information": "die Police eingereicht"

wurden vom zuständigen Underwriter der Versicherer (Demo) die Police eingereicht

-> trim(split(split(outputs('text'), 'Underwriter der')[1], 'die Police eingereicht')[0])

- outputs('text') is simply some plain text

what this function does:

a) trim to get rid of needless spaces

b) split nested; first, split the text into two pieces - at 'Underwriter der'

c) [1] means: take the 2nd half of the text)

d) then put this 2nd half into another split, which is cut off at 'die Police eingereicht',

e) and take the 1st half using [0] .

Well.. good luck, just re-build this one formula, and good luck.

let me know if you have further questions.