Unanswered

Parse HTML Email body

(0) Share

Report

Posted on by sherif77

Hello,

I have an HTML email that i received in specific created folder, I would need to parse the text within the email to SharePoint list where i have already set my headers to correspond the value in each email.

Request Number , Invoice Number , Invoice Date, Fees, Saddad Number, Biller Number

The typical Paragraph in the email is as follows :

Fees Payment

Dear \ XXXXXXXXXXXXXXXXXXX
An invoice has been issued for the request number 2020-105913 for service New Marketing Permission
Invoice Number : XXXXXXXXXX (Numbers)
Invoice Date : 31/10/2020 (Dates)
Fees: 15000.0 (Numbers)
Sadad Number : XXXXXXXXXX (Numbers)
Biller number : XXX (numbers)

Connectors i tried using When an email arrives, HTML to email, Compose ( then could not pass the compose ones)

Compose

length(body('Html_to_text'))= return 1730 character in the initial email received

Compose (Request number )

add(indexOf(body('Html_to_text'),'Request Number'),15) = output 675

Compose ( Invoice Number)

Int(indexOf(body('Html_to_text'),'Invoice Number')) = output 725

Compose (substract request Number from Invoice Number)

Sub(outputs('Request_Number'),outputs('Invoice_Number'))= output -50

I hope you could assist in this one urgently,

Regards,

Sherif

Categories:

Building flows

I have the same question (0)

All responses (14)

Answers (0)

Paulie78 8,422 Moderator on at

Like (0)

Report

Does the real content have:
Invoice Number : XXXXXXXXXX (Numbers)
The word numbers at the end, or did you put that there to be helpful? Could you post an example of the exact sample output in the code block?

Was this reply helpful? Yes No
sherif77 on at

Like (0)

Report

Thank you, @Paulie78

the word numbers doesn't exist,

i just marked the information in case it will require specific expression. Invoice Number Value consist of 10 Digits.

I hope that helps.

Regards,
Sherif.

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
Check out this flow, which shows you how to do what you want:
https://ibb.co/bJ2wHR8

A bit of explanation:

A variable is defined which contains only a line break character.
The email contents are split by the new lines into an array, so each line can be accessed separately.
A new object is created, which uses expressions to extract the information from each line.
A parse JSON step is added which parses the newly created object so that the information from it is easier to use in subsequent steps.
The main section of code is within the object creation and the code looks like this:

{ "requestNumber": "@{substring(substring(variables('emailLineData')[3], 50), 0, indexof(substring(variables('emailLineData')[3], 50), ' '))}", "invoiceNumber": "@{trim(substring(variables('emailLineData')[4], add(indexof(variables('emailLineData')[4], ':'),1)))}", "invoiceDate": "@{trim(substring(variables('emailLineData')[5], add(indexof(variables('emailLineData')[5], ':'),1)))}", "fees": "@{trim(substring(variables('emailLineData')[6], add(indexof(variables('emailLineData')[6], ':'),1)))}", "saddadNumber": "@{trim(substring(variables('emailLineData')[7], add(indexof(variables('emailLineData')[7], ':'),1)))}", "billerNumber": "@{trim(substring(variables('emailLineData')[8], add(indexof(variables('emailLineData')[8], ':'),1)))}" }
The code for requestNumber is slightly more complicated than the others, but still easy enough to comprehend. Once you run it you end up with an object that looks like this:
{ "requestNumber": "2020-105913", "invoiceNumber": "XXXXXXXXXX", "invoiceDate": "31/10/2020", "fees": "15000.0", "saddadNumber": "XXXXXXXXXX", "billerNumber": "XXX" }
Which once passed though the parse JSON step, makes everything really easy to access.

Was this reply helpful? Yes No
sherif77 on at

Like (0)

Report

@Paulie78

I'm receiving this error at compiled Email data

Unable to process template language expressions in action 'Compiled_Email_Data' inputs at line '1' and column '19999': 'The template language expression 'substring(substring(variables('emailLineData')[3], 50), 0, indexof(substring(variables('emailLineData')[3], 50), ' '))' cannot be evaluated because array index '3' is outside bounds (0, 0) of array. Please see https://aka.ms/logicexpressions for usage details.'.

Also i had to change the first compose for the output of HTML to text as it was giving me an error still

my actual flow

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

Go into the run history and see what your email lines array contains (post it and change the data if you need to). It seems like the line split hasn’t worked for you.

Was this reply helpful? Yes No
sherif77 on at

Like (0)

Report

@Paulie78

I tried it didn't work for Me i don't understand why, but i think its for the following reason

The Email include Arabic words / letters

[http://dev-internet.XXX.XXX.XX/assets/email/logo.png] [http://dev-internet.XXXX.XXX.XX/assets/email/EmailLogo.png]
تسديد المقابل المالي السادة / شركه XXXX XXXXXX XXXXXX XXXXXXX
تم اصدار الفاتورة الخاصة بالطلب رقم 2020-107047 مقابل خدمة اذن تسويق جديد
رقم الفاتورة: XXXXXXXXXX
تاريخها: 04/11/2020
بمبلغ 19000.0 ر.س , و رقم سداد XXXXXXXXXXX
رقم المفوتر : XXX
نود التنوية بأنه في حال عدم السداد خلال (30) يوم من إصدار الفاتورة سيتم إلغاء
وشطب الطلب
وعليه يتطلب تقديم طلب جديد
للمزيد الرجاء الضغط على الرابط التالي
[https://XXXX.XXXX.XXX.sa/dashboard/email-notification/127937]
Fees Payment Dear \ XXXX XXXXXXX CO LTD
An invoice has been issued for the request number 2020-107047 for service New
Marketing Permission
Invoice Number : XXXXXXXXXX
Invoice Date : 04/11/2020
Fees: 19000.0
Sadad Number : XXXXXXXXXX
Biller number : XXX
please notice that if non-payment within (30) calendar days from issuing the
invoice, the request will be canceled and closed and you are required to submit
a new application.

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

Well, its a significantly different email format to the one you originally posted, so that would certainly explain it not working 😐

Was this reply helpful? Yes No
sherif77 on at

Like (0)

Report

@Paulie78

I'm actually still learning a lot of information sounds gibberish to me but I'm trying. so, i apologize for any inconvenient information shared from my side.

Is it possible by any chance to get this done by powerAutomate or it will be hard.🤔

Thank you in advance.
Sherif,

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

It's totally possible, if you are new to Power Automate then I wouldn't class it as easy. The flow I gave you would have worked if your email actually matched what you originally posted 😁.

The best thing for you to do (because I can now see that it is a HTML email with a great deal of other content). Would be to get the HTML source and put it on pastebin.com - so I can at least help you with the same source content.

Was this reply helpful? Yes No
sherif77 on at

Like (0)

Report

@Paulie78

Thank you,
This is how the Email was formulated on Pastebin.

تسديد المقابل المالي
السادة / XXXX XXXXXX XXXXXX XXXXXXXX
تم اصدار الفاتورة الخاصة بالطلب رقم 2020-119206 مقابل خدمة تعديل اذن تسويق
رقم الفاتورة: 2270947260
تاريخها: 27/12/2020
بمبلغ 5000.0 ر.س , و رقم سداد 2270947260
رقم المفوتر : 109
نود التنوية بأنه في حال عدم السداد خلال (30) يوم من إصدار الفاتورة سيتم إلغاء وشطب الطلب
وعليه يتطلب تقديم طلب جديد
للمزيد الرجاء الضغط على الرابط التالي
Fees Payment
Dear \ XXXX XXXXXXX XX XXX
An invoice has been issued for the request number 2020-119206 for service Update Marketing Permission
Invoice Number : 2270947260
Invoice Date : 27/12/2020
Fees: 5000.0
Sadad Number : 2270947260
Biller number : 109
please notice that if non-payment within (30) calendar days from issuing the invoice, the request will be canceled and closed and you are required to submit a new application
For more detail please click this link

All rights reserved to the XXXXX XXXX and XXXX XXXXXXXXX. Terms of Use | Privacy policy
جميع الحقوق محفوظة للهيئة العامة للغذاء والدواء . شروط الإستخدام | سياسة الخصوصية

Was this reply helpful? Yes No