store information from outlook's body to sharepoint list

[gennarodp]

I would like to create a flow to save the information contained in the body of the email, coming from a specific address, in a sharepoint list.

I have already created sharepoint list, to save this information

The body of email is in the following format:

To:	CONTOSO SRL JOHN CONTOSO PIAZZA DI SPAGNA ROMA, 80010 Italy		RFQ No: # 123456 Priority: Critical Quote By: Nov-30-20 07:23AM CST(UTC-6) Required Delivery:
From:	NOBODY INC. GIUSEPPE ROSSI CHICAGO Fort Lauderdale, FL 33110 United States Phone: 123-456-789 Fax: 789-456-123 E-mail: giuseppe@nobodyinc.com

Buyer Comments:	PN 123456-7 PUMP
Buyer Attachments:

[No Quote All Items]

Buyer's Request

Item	Part No. Searched	Description	U/M	Qty
01	123456-7	"PUMP"		1

Quote Detail for Requested Part Number: 123456-7 Supplier Part: 123456-7 Description: PUMP	[ ]No Quote This Item
Part No. / Alt Part No. Qty U/M Cond Price Type Price ($) Trace 123456-7 / No Alt. PN [          ] [          ] [SV \/] (X)Outright ( )Exchange [          ] [-Select- \/]

Lead Time:	[          ]
Supplier Comments:
Supplier Attachments:

I would like to save following information in sharepoint list:

 RFQ NO:  "# 123456"

 NAME OF ENTERPRISE OF REQUEST : "NOBODY INC"

 EMAIL OF REQUESTER: "giuseppe@nobodyinc.com"

 SUPPLIER PART:  "123456-7"

 DESCRIPTION OF SUPPLIER PART: "PUMP"

 QTY: "1"

Thanks 

[Anonymous]

I know enough to know that it should be possible. But not enough to know how to do it )).

It's going to be some serious complicated JSON/html/array processing that will go completely wrong if one character is in the wrong place.

I will watch this one. Good luck.

Sam

[gennarodp]

Resolved. 

🙂

[Expiscornovus · Accepted solution]

Hi @gennarodp,

I think this would be possible. If the html tag is using identifiers this would be a bit easier, because you could use split functions based on those id's. If it's just plain html without id's you could convert the html to text.

As an example a create this concept flow to demonstrate how to extract the RFQ No: from a body which is using html which doesn't have identifiers.

1. Add a Html to Text action. Add your email body to that action

2. Add a compose action. Use the split function to search for 'RFQ No:', I used the following expression to get the second part of the result of the split [1]

split(outputs('Html_to_text')?['body'], 'RFQ No:')[1]

3 Add another compose to remove the string characters after the number by searching for 'Priority:'

split(outputs('Compose_-_RFQ_No:'), 'Priority:')[0]

As you can see it's technically possible. But this be can become a pretty complex flow.

So I would also recommend looking at other solutions. Microsoft has several products which should be able to meet this requirement of data extraction. You could for instance use either Azure Form Recognizer or SharePoint Syntex. I don't know if that would be an option for you. 

With both products you can feed the service samples and train it to recognize and extract data from the documents. 

https://azure.microsoft.com/en-gb/services/cognitive-services/form-recognizer/

https://docs.microsoft.com/en-us/microsoft-365/contentunderstanding/create-an-extractor

[gennarodp]

Hello @Expiscornovus thanks for your answer.

your solution is very good, but doesn’t manage multiple data.

in one mail i have many differents data non good structured.

[resing]

Document understanding in SharePoint Syntex can help you with variations on the text in the body of your emails. To use document understanding, you'd need to save it as a file in SharePoint first. You'd build a model that you train on the variations of the email body you expect. Then as new emails come in and are saved as files, you'd classify the files and extract the info from them into columns on the library.

For more on SharePoint Syntex document understanding, take a look at Document understanding overview | Microsoft Docs

To save the file, use a couple pieces of the Outlook connector and one from the SharePoint connector.

The trigger is "When a new email arrives". The actions are "Export email" followed by "Create file". In the example below, I added a condition so it doesn't save every email I receive to SharePoint.