Parse HTML Email body

[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

[Paulie78]

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?

[sherif77]

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.

[Paulie78]

Check out this flow, which shows you how to do what you want:

https://ibb.co/bJ2wHR8

A bit of explanation:

1. A variable is defined which contains only a line break character.
2. The email contents are split by the new lines into an array, so each line can be accessed separately.
3. A new object is created, which uses expressions to extract the information from each line.
4. 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.

[sherif77]

@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

[Paulie78]

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.

[sherif77]

@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.

[Paulie78]

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

[sherif77]

@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,

[Paulie78]

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.

[sherif77]

@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
جميع الحقوق محفوظة للهيئة العامة للغذاء والدواء . شروط الإستخدام | سياسة الخصوصية

[Paulie78]

I wanted the HTML source code, so a link to the code on Pastebin would have been useful. You posted an image of what it looked like.

[sherif77]

@Paulie78

Okay here you  there

 https://pastebin.com/y6HYF5Kc 

Regards,

[sherif77]

@Paulie78 

Hope all is well,

any feedback on this one ?

Regards,

sherif,

[Paulie78]

Hello, you never pasted the HTML source. You posted some plain text, is that the result you got after Power Automate did a HTML to text operation?