Mail to Excel

[shirls]

So I am brand new to Power Automate and I have managed to get data to run into an excel file in my one drive.

However, it's pulling too much information from the email into each cell, when I need just specific info. 

Here is an example of the email:

Subject: New Lead: Jane Doe 08/05/2023

Body:

Jane Doe is interested in hearing more about: Benefit Review, Dental
Address: 555 Lane Dr, New York, NY 55555
Association: XXX
Association Status: Inactive
Phone: (555) 555-5555
County: Unknown
Campaign: Direct Campaign

Here is a screen shot of what it looks like when the flow runs:

Normally there are spaces between each line in the body of the email, which is where the ">" are coming from... which I don't need. 

The name I am pulling from the subject of the email, but I would prefer not to have "New Lead" and the date come over. 

Here are my expressions:

Address: first(skip(split(first(split(body('Html_to_text'),'Association')),'Address: '),1))

Association: first(skip(split(first(split(body('Html_to_text'),'Association Status')),'Association: '),1))

Assn Stat: first(skip(split(first(split(body('Html_to_text'),'Email')),'Association Status: '),1))

Email: first(skip(split(first(split(body('Html_to_text'),'Phone')),'Email: '),1))

Phone: first(skip(split(first(split(body('Html_to_text'),'County')),'Phone: '),1))

County: first(skip(split(first(split(body('Html_to_text'),'Campaign')),'County: '),1))

Campaign: first(skip(split(first(split(body('Html_to_text'),'Source')),'Campaign: '),1))

Source: first(skip(split(first(split(body('Html_to_text'),'Website')),'Source: '),1))

The information comes from our company, and sometimes there are emails and phone numbers included, sometimes not. 

Thanks in advance for the help!

[trice602]

Hi @shirls ,

It looks like you just need to prefix your expressions with a replace expression.  Here's my test results.  Please mark as a solution if this is helpful!  Always glad to help! Tom

replace(variables('Association'),'>',decodeUriComponent ('%0A'))

replace(first(skip(split(first(split(body('Html_to_text'),'Association Status')),'Association: '),1)),'>',decodeUriComponent ('%0A'))

[shirls]

Hi, @trice602!

Thank you so much for trying to help me with this solution. I am not 100% sure if the steps you created (screenshots) were something I should also be doing OR if you were showing them to prove they worked. Can you break that part down for me? Thank you for your patience!

[trice602]

Hi @shirls ,

Yes I was showing you an example that it worked in my tests and also something you can implement.  Here's the next steps:

1)  Make a copy of your flow (use the copy for testing)

2)  For each of your expressions, wrap it in a replace expression; you want to replace that ">" with the encoded line break.  

3) Retest and see if the results work for you.

2a)  Add the replace expression to one of the expressions and test it (you don't need to add to everyone yet).  If the results work for you, expand it to additional expressions.

Here are my two expressions modifications:

Address: replace(first(skip(split(first(split(body('Html_to_text'),'Association')),'Address: '),1)),'>',decodeUriComponent ('%0A'))

Association: replace(first(skip(split(first(split(body('Html_to_text'),'Association Status')),'Association: '),1)),'>',decodeUriComponent ('%0A'))

Assn Stat: first(skip(split(first(split(body('Html_to_text'),'Email')),'Association Status: '),1))

Email: first(skip(split(first(split(body('Html_to_text'),'Phone')),'Email: '),1))

Phone: first(skip(split(first(split(body('Html_to_text'),'County')),'Phone: '),1))

County: first(skip(split(first(split(body('Html_to_text'),'Campaign')),'County: '),1))

Campaign: first(skip(split(first(split(body('Html_to_text'),'Source')),'Campaign: '),1))

Source: first(skip(split(first(split(body('Html_to_text'),'Website')),'Source: '),1))

If this worked for your, please mark as a solution!  Always glad to help! Tom