Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

Unable to extract data from email body use expression

(0) ShareShare
ReportReport
Posted on by 8

Can anyone help me to write an expression to extract data from email body to excel?

Below is what is looks like in the email body, is not an attachment.

h415044918_0-1677194065582.png

 

If anyone can help me will greatly appreciate.

 

Best,

  • h415044918 Profile Picture
    h415044918 8 on at
    Re: Unable to extract data from email body use expression

    Thank you for your reply, but you can see from the subject. I did stuck in the expression. 

    On this step

    h415044918_0-1677281552164.png

     

     

     

  • jiwhite Profile Picture
    jiwhite 498 on at
    Re: Unable to extract data from email body use expression

    It's the same process for extracting the text and sending it to Excel. I have provided the connector for reading text or HTML from an email body above. If you want more help, you'll need to provide more information on what you're specifically trying to do. You're not likely to find that people will write the code for you, but if you post what you've done so far with identifying information removed, you'll get suggestions on how to improve your code.

  • h415044918 Profile Picture
    h415044918 8 on at
    Re: Unable to extract data from email body use expression

    Mine is not attachment, just in email body and look like excel table.  

  • jiwhite Profile Picture
    jiwhite 498 on at
    Re: Unable to extract data from email body use expression

    It works for me. I have a system to extract text from attachments and send them to Excel on OneDrive. Post images of your anonymized Power Automate flow and Office Script if you want some help troubleshooting.

  • h415044918 Profile Picture
    h415044918 8 on at
    Re: Unable to extract data from email body use expression

    It does not work, I think it should be add expression in order to extract the data out.

     

    h415044918_0-1677257931627.png

     

  • jiwhite Profile Picture
    jiwhite 498 on at
    Re: Unable to extract data from email body use expression

    The best way to get the data and send it to Excel is most likely to be by using the HTML to text connector.

     

    For example, an email with this HTML table in the body:

    jiwhite_0-1677257143080.png

    Would be converted by

    jiwhite_1-1677257225245.png

    to:

    Col1

    Col2

    Col3

    1

    2

    3

    4

    5

    6

    That could be sent to an Office Script using Run Script to write the data to a spreadsheet.

  • jiwhite Profile Picture
    jiwhite 498 on at
    Re: Unable to extract data from email body use expression

    You can put the body of the email directly into a string, then parse the HTML, or you can use the HTML to text connector to extract the text from the body.

     

    For example, I created an email with this HTML table in the body.

    jiwhite_0-1677198883343.png

    If I put the body of the email directly into a string variable, it looks like this:

    jiwhite_2-1677199147129.png

     

     

     

    <html><head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><style type="text/css" style="display:none">
    <!--
    p
    	{margin-top:0;
    	margin-bottom:0}
    -->
    </style></head><body dir="ltr"><div class="elementToProof" style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)"><br></div><div lang="EN-US" style="word-wrap:break-word"><div class="x_WordSection1"><table class="x_MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse:collapse; border:none"><tbody><tr><td width="208" valign="top" style="width:155.8pt; border:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">Col1</p></td><td width="208" valign="top" style="width:155.85pt; border:solid windowtext 1.0pt; border-left:none; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">Col2</p></td><td width="208" valign="top" style="width:155.85pt; border:solid windowtext 1.0pt; border-left:none; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">Col3</p></td></tr><tr><td width="208" valign="top" style="width:155.8pt; border:solid windowtext 1.0pt; border-top:none; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">1</p></td><td width="208" valign="top" style="width:155.85pt; border-top:none; border-left:none; border-bottom:solid windowtext 1.0pt; border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">2</p></td><td width="208" valign="top" style="width:155.85pt; border-top:none; border-left:none; border-bottom:solid windowtext 1.0pt; border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">3</p></td></tr><tr><td width="208" valign="top" style="width:155.8pt; border:solid windowtext 1.0pt; border-top:none; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">4</p></td><td width="208" valign="top" style="width:155.85pt; border-top:none; border-left:none; border-bottom:solid windowtext 1.0pt; border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">5</p></td><td width="208" valign="top" style="width:155.85pt; border-top:none; border-left:none; border-bottom:solid windowtext 1.0pt; border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">6</p></td></tr></tbody></table><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">&nbsp;</p><p class="x_MsoNormal elementToProof" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif"><br></p></div></div></body></html>

     

     

    If I use the use the HTML to text connector and put that into a string variable, it looks like this:

    jiwhite_1-1677199126523.png

     

     

     

    Col1
    
    Col2
    
    Col3
    
    1
    
    2
    
    3
    
    4
    
    5
    
    6

     

     

    If you have a known table structure, the second one is probably better.

    You can then feed the text to Excel using Run Script and have an Office Script that creates a table out of it.

  • h415044918 Profile Picture
    h415044918 8 on at
    Re: Unable to extract data from email body use expression

    Is HTML 

  • jiwhite Profile Picture
    jiwhite 498 on at
    Re: Unable to extract data from email body use expression

    What is the table?  Is it text, HTML, an image, ...?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,343

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,703

Leaderboard

Featured topics