cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sherif77
Helper II
Helper II

Parse HTML Email body

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

14 REPLIES 14
Paulie78
Super User
Super User

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?

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.

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

https://ibb.co/bJ2wHR8

ExtractEmailData.png

 

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.

@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

 

sherif77_0-1604277508849.png

 

my actual flow

 

sherif77_1-1604277553874.png

 

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.

@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 

 

Payment.PNG

 

 

[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
Super User
Super User

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

@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
Super User
Super User

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.

 

@Paulie78 

 

Thank you,

This is how the Email was formulated on Pastebin.

 

sherif77_1-1609237710797.png

 

 



تسديد المقابل المالي
السادة / 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
Super User
Super User

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.

@Paulie78

 

Okay here you  there

 https://pastebin.com/y6HYF5Kc 

 

Regards,

 

@Paulie78 

 

Hope all is well,

any feedback on this one ?

 

Regards,

sherif,

Paulie78
Super User
Super User

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?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,060)