cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mark-carlisle
Level: Powered On

Extract text from Body of an email in Flow

I want to extract information from the body of an email and populate fields in a SharePoint. An extract of the email is follows;

 

Details

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

Account Number 99999999

Contact Name Mr A N Other

Email example@domain.com

Telephone 0123456789

Incident Reference XXXXX-XXXXX


I'm using the expression;

 

substring(body('Html_to_text'),lastIndexOf(body('Html_to_text'),'Account Number '),8)

 

Which is just populating the SharePoint field with 'Account ' whereas I actually want it to populate with '99999999'. I've tried a few different things to try and add on a value to the lastindexof value but I'm running into various errors.

 

Any suggestions would be appreciated.Capture.PNG

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
mark-carlisle
Level: Powered On

Re: Extract text from Body of an email in Flow

Found the solution. Sure I tried this previously but working today.

 

substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Account Number '), 15),8)
21 REPLIES 21
mark-carlisle
Level: Powered On

Re: Extract text from Body of an email in Flow

Found the solution. Sure I tried this previously but working today.

 

substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Account Number '), 15),8)
Sam1
Level: Powered On

Re: Extract text from Body of an email in Flow

I have email content as below

 

Resolution Description : Test test test............................................................... can be 500 words

Department : IT Devlopement Team 

File Path: abcd\dba\ddd\fff\\\gggh\hhhh

 

Each of the content needs to be mapped against lsit column.

 

I tried with below formula but its also getting the department information as well but i just need Resolution Description

substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Resolution Description'), 19),500)

 

mark-carlisle
Level: Powered On

Re: Extract text from Body of an email in Flow

I'm assuming here so forgive me if I'm wrong, your Resolution Description can be a variable length, that is it could be a maximum of 500 words?

 

If thats the case I used the following to account for variable lengths of data;

 

 

substring(triggerBody()?['Body'],add(indexOf(triggerBody()?['Body'],'Account Number'),30),sub(indexOf(triggerBody()?['Body'],'Contact Name'),add(indexOf(triggerBody()?['Body'],'Account Number'),30)))

 So based off the above if you use;

 

substring(triggerBody()?['Body'],add(indexOf(triggerBody()?['Body'],'Resolution Description'),25),sub(indexOf(triggerBody()?['Body'],'Department'),add(indexOf(triggerBody()?['Body'],'Resolution Description'),25)))

 

The output of the above would be 'Test test test............................................................... can be 500 words' based off your example.

 

pete_whitfield
Level: Power Up

Re: Extract text from Body of an email in Flow

Hi,

 

I'm having a similar problem, I am am trying to write an expression that will extract, from the email body, the date given after 'Start Date' (in this example, 28th December) and add that date it to the 'Start Time' Field in 'Create event (V2)', and do the same for End Date. Even if I could do that, would the date format be compatible or would I need to add a further expression to convert into an acceptable date format (and if so, what might that look like?)

 

The following leave request has been Approved by your line manager.

 

Employee: xxxx xxxxxxxx

Leave type: ANNUAL LEAVE

Start date: 28-DEC-17

End date: 28-DEC-17

 

Any suggestions/help would be greatly appreciated.

 

Many thanks

 

Pete

 

mark-carlisle
Level: Powered On

Re: Extract text from Body of an email in Flow

This should work, obviously replace Start with End for the End Date.

 

substring(triggerBody()?['Body'],add(indexOf(triggerBody()?['Body'],'Start Date'),30),9)

In terms of the date format being complatible, not used the Create event (V2) action myself so I'd just give it a go. If it does not work you could try using the formatDateTime fuction to convert it to an acceptable format, you'll find more information on that and the schema in general here.

 

pete_whitfield
Level: Power Up

Re: Extract text from Body of an email in Flow

Hi Mark,

 

Thanks very much for your help - much appreciated!

 

Unfortunately the flow is still failing - the error details are:

 

The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. clientRequestId: 6e0beff4-c7ad-4817-ab13-e833cd0b3324

 

I guess that means I need to add the formatDateTime function, but not quite sure where in the expression I should add it - does it matter?

 

Thanks

 

Pete

mark-carlisle
Level: Powered On

Re: Extract text from Body of an email in Flow

Can you screenshot the failed Flow with what it is outputting so we can see what it is trying you use as the date time?

pete_whitfield
Level: Power Up

Re: Extract text from Body of an email in Flow

Hi Mark,

 

Screen shot below. Does that give you enough info?  Many thanks again for your help.

 

 Failed Flow.png

mark-carlisle
Level: Powered On

Re: Extract text from Body of an email in Flow

Sorry was the inputs above that that I needed. I've done this myself though, although I had to run the Body through a Conversion action first because inputs were not showing a date but some of the html within the Body of the email. Structure of the Flow is below, Start and End Date expressions also follow.

 

substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'Start Date'),12),9)
substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'End Date'),10),9)

flow.PNG

Sam1
Level: Powered On

Re: Extract text from Body of an email in Flow

 
Sam1
Level: Powered On

Re: Extract text from Body of an email in Flow

I just copy pasted the formula but it didnt work for me .I am getting below error.

 

Invalid text value. A text field contains invalid data. Please check the value and try again.  

 

I am keeping entire email content as Html_to_text.

 

I am thinking if any condition can be applied to read the data and exclude as sson as there is new line. appriciate for any solution.

I am completely new to flow.

Sam1
Level: Powered On

Re: Extract text from Body of an email in Flow

Hi ,

 

I further observed below lines of formula is working

 

substring(triggerBody()?['Body'],add(indexOf(triggerBody()?['Body'],'Account Number'),30),sub(indexOf(triggerBody()?['Body'],'Contact Name'),add(indexOf(triggerBody()?['Body'],'Account Number'),30)))

 But how do i get the value from Html_to_text instead of  ['Body'].Please help .I am close to the solution.

mark-carlisle
Level: Powered On

Re: Extract text from Body of an email in Flow

You would need to replace all instances of triggerBody()?['Body'] with body('Html_to_text') assuming your conversion action is titled Html_to_text.

Sam1
Level: Powered On

Re: Extract text from Body of an email in Flow

It worked. Thanks a lot.

 

I need one help. How to check if the output has null value and update space for null value for  below formulas.

 

substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'Alarm Display Id'),20),sub(indexOf(body('Html_to_text'),'Alarm Description'),add(indexOf(body('Html_to_text'),'Alarm Display Id'),20)))

 

substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Severity'), 10),6)

mark-carlisle
Level: Powered On

Re: Extract text from Body of an email in Flow

You could try wrapping the previous expression with this

 

if(empty(replace with expression),value if true, value if false)

Not tested it but should work. More information on the scehma can be found here https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language.

Courtenay
Level: Powered On

Re: Extract text from Body of an email in Flow

Hi there,

 

Just for future reference, Parserr, a conenctor available on Microsoft Flow extracts text from emails, PDF, Excel etc

mark-carlisle
Level: Powered On

Re: Extract text from Body of an email in Flow

Yeah I did look at that, unfortunately due to the volume of email this would require a financial commitment that the company would be unwilling to agree to for what we are using it for. So a zero cost solution was required. Would have made things much easier though!
itkylam
Level: Powered On

Re: Extract text from Body of an email in Flow

I tried to format the date without time in Flow:

EXPRESSION Can format it. However WITHOUT THE OPTION of DATE in the Excel and Flow

I used Dynamic Content, the date from excel can found but NO Format option there.

How can I format it?

venseben
Level: Powered On

Re: Extract text from Body of an email in Flow

This is pure gold!!!!! You just saved my day THX

 

/Frank

>