Power Automate - Extract Text Values from an Email Body

[@debcodding8609]

This is excellent. I retire in 10 days so have a dozen people wanting to do something like this before I'm gone. Haven't worked with PowerAutomate at all until this weekend. My end-users will be very pleased!

[@abm4975]

Hi Deb Thanks so much for your support. Best wishes for your retirement. Hope all goes well.

[@akshaykurve4055]

​@@abm4975when I m pressing enter in compose key, it's showing invalid parameter..Any solution

[@sabreaion6074]

Really useful video. I am glad that you didn't put recommended videos at the end. I saw someone else's video and I couldn't see clearly the last steps because of that.

[@abm4975]

Thanks for the support

[@perfectpipesplumbing3992]

This was perfecto! The absolute best explanation on KZbin or anywhere else! I was able to get my flow setup this morning and watched my emails populating my Excel workbook! Now my plumbing business has automated email data input. These MS tools are fantastic!

[@abm4975]

Hi So glad to hear and thanks for your support and comments. Thanks a lot.

[@MrFulcrumSystems]

I have trying to understand how to extract formatted data from an email. You nailed it. Thank you!

[@abm4975]

Glad it helped!

[@leomatute6871]

This is the first suggestion that came up and it looks like you hit the nail on the head buddy. Thanks a lot...great video 🙌

[@abm4975]

Glad you liked it!

[@stobormachinery9487]

How did you get the little blue "fx" (function choice icon) to show in the right hand corner of box and how did you get the list of functions to show "inline". My dynamic choices show in the lower right part of the screen in a pop out box.

[@abm4975]

Hi Go to Powerautomate Settings (righ hand corner left just before the ? (question mark) Click View All Power Automate settings Set Experimental Features > On Thanks

[@stobormachinery9487]

@@abm4975 Thank you. Total beginner trying to learn on the fly. I'm having difficulty with the following: My ultimate goal for this portion is to create an Outlook calendar event from data in an Excel table. I have 2 separate cells, one for date and one for time. I am able to get the date with: 👍formatDateTime(addDays('1899-12-30', int(items('create_a_calendar_event')?['MIGRATION_DATE'])), 'yyyy-MM-dd')👍success. I am unable to get the time cell extraction to work. I'm currently using: 🧠formatDateTime(utcNow(items('create_a_calendar_event')?['MIGRATION_TIME']), 'HH:mm')🧠 fail with error: 👁InvalidTemplate. Unable to process template language expressions in action 'convert_time_to_number' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '0.65625' was not valid. The datetime string must match ISO 8601 format.'.👁 Thank you for your time.

[@abm4975]

Hi Apologies for the late reply. Do you still need help in this? Please let me know.

[@stobormachinery9487]

@@abm4975 Not late at all. Very prompt. I still cannot find a workable solution for this.

[@eddepacheco]

Thank you. Very helpful to trap the booking emails that are not taken in events for more than one person.

[@abm4975]

Glad it was helpful!

[@jocelyng.7142]

This was explained so well. Best video I’ve found on this! Thank you so much!!

[@abm4975]

Thanks for the support Jocelyn

[@mirjamkaal]

Agreed! The best one i could find.

[@1888196721]

Excellent tutorial, exactly what I was trying to do! Thank you.

[@abm4975]

Hi Don Thanks so much for the support.

[@thomasrothe5881]

As a beginner: I extracted a date time out of the e-mail body. How do I set it as a due date in MS To Do?

[@abm4975]

Check the To Do connector and actions step Add a to-do (V3) Thanks

[@Outsider1233]

Hello I have a question what if I want it to separate each text, as I am looking for a particular string of text in an email and it might wrap it with the rest of the body. Like I want each text to be represented line by line?

[@abm4975]

Hi When you use the split() expression it converts into an array. Using the index of the array you can get each lines. If you still need help post a sample text of the email and the key word you are looking for. Also how you want the output to? I will have a look. Thanks

[@Outsider1233]

@@abm4975 for example “please provide this illustration, when these policies are available for premium offset” this is reading as one line and I would like to differentiate each word on different lines so I can recognize certain words that’s start with P for example and ends in N. I have set up a condition at the end of my flow that already does this, but if the words are together on one line, power automate won’t pick it up. I appreciate all your help!

[@abm4975]

Thanks for your quick reply. Try use the below expression. split('please provide this illustration, when these policies are available for premium offset', ' ') This will output as an array. Then add an apply to each loop action step. Map the split() output from the previous actions step. Then inside the loop add a compose action step and add another expression called item(). This will give you each words from that sentence. Thanks

[@MrXeddie95]

Thanks for the video! I just have a small problem still, my formulas look like this currently First(split(outputs(’split_body_text’)[5],’ ’)) And they work great to extract single-row infos like a name/email/phone number. But i have a ”free text” field that has different lenghts for every email and i would like to get all of it no matter the length. It starts at row 13 and can sometimes go down to row 25. Is there a smart formula that can see understand when it ends? Its always the same phrase after this text field so maybe i can add that to a filter? The end phrase is always ”Detta är ett automatiserat” Im very thankful for suggestions!

[@petersassmannshausen3099]

This was exactly what I needed! Thanks heaps! Now to lookup the details in the array dynamically?! 😂

[@abm4975]

You are so welcome!

[@pravallikaa3306]

When I try html to text and give content of body , other things such as from , to etc are included , how to get only the body

[@abm4975]

Hi I need to see your flow. Check my channel about section (see by email contact) and connect me via teams.

[@kartheek1992]

Is it possible to validate email body with other reference email

[@abm4975]

Hi Is that two different emails. Then you need two different flows as trigger referring to those emails. Store the body in OneDrive or SharePoint. Once you received both then you can compare the body. You need three flows to achieve this. Thanks

[@jibbsautomations]

Great tutorial! Can you elaborate on the EnterKey action? What is its purpose?

[@abm4975]

Hi Thanks for your question. The enter key is for identifying new lines from the email message. Thanks

[@jibbsautomations]

@@abm4975 Thank you!

[@abm4975]

You are welcome

[@damienfernando36]

Thank you. Very Useful and nicely explained.

[@abm4975]

Glad it was helpful!

[@thief12208]

This is exactly what I was trying to do! Thank you so much!

[@abm4975]

You're very welcome!

[@nikitaojha1809]

What if I have a whole line as a single array ? Like i have chabged my mail to array but it somehow chabged into 1 ARRAY. Can you please tell me how can I fetch values? Many thanks

[@NEPApool]

So so so great! This was explained wonderfully! Thank you for posting!

[@abm4975]

Glad it was helpful!

[@juajal87]

I cant see the array output from the first split. I shows just one line with all the information. I have compose with the "enterKey" then split with the body html to text followed by the delimiter. Also, I noticed the it is bringing the whole email. It brings the subject and the body of the email.

[@davidmalone3461]

Hi, I can't copy all the items into a scope, only let's me drag one in.

[@abm4975]

Hi David I am not sure. Could you please explain a bit more about this? Thanks

[@Fernanda-uh7iz]

you have saved my life more than once! brilliant!! thank you so much!!

[@abm4975]

Hi Fernanda Thanks for your kind words and support.

[@affy57]

Hello.. Thanks for the Video.. I have a question as well.. I am creating a flow. Where I need to get the details of existing emails. Not When a new email arrives.. Do we have a trigger for that?

[@abm4975]

Hi Trigger is something when an event happens such as create, modify or delete. For getting the email details use the action step called Get Email (v2) if you using the outlook connector. Thanks

[@harshjain1207]

How to find the index number for FYQ? Suppose FYQ position is not always the first, in that case how will you find out the position of FYQ ??

[@andrewalexander9096]

I am having trouble with I believe the delimiter. No matter what I try, the split body text comes out on the same line. For the life of me I can’t figure out why? It’s like it isn’t seeing the enter as a delimiter? It I put other characters in besides enter it delimits using that character. So confused!

[@RakhibMohammad]

With the extracted text from the mail body, how can we create a folder in share point with this extracted text name? It will be helpful if anyone helps me

[@Nathanallenpinard]

Hello, this was very helpful in finding variables with fixed characters that never change, but I'm running into an issue where description/comments cause different numbered indexes depending on how many lines that variable is. So I'm wondering if there is a way you can substring() and instead of taking a certain amount of characters, you instead take the value until the next carriage return or something. Or perhaps there is a convulted way of doing this with multiple expression.

[@abm4975]

Hi Nathan Thanks for the support and question. Declare a compose action and press enter key. Use this compose action output value to substitute the split() expression of your text as separator. This will result in an array of values.

[@gustavoguimaraes5446]

@@abm4975 Hello, congrats for the awesome content. I'm facing the same issue Nathan mentioned. Would you please write the expression? Appreciate.

[@harshadlatey5813]

can we do it for 100 recent mails at a time

[@abm4975]

Hi Have a look on Graph API learn.microsoft.com/en-us/graph/api/resources/mail-api-overview?view=graph-rest-1.0 Thanks

[@edwardsotelo7568]

Can we also extract string from subject as well?

[@abm4975]

Hi Edward Yes you can. Have you got an example so that I could answer it. Thanks

[@edwardsotelo7568]

@@abm4975 let's say there's an email with subject "today is monday email" and i want to get the monday and save it to a variable. The "monday" in the subject changes. It could be Tuesday and etc. How can i get the dynamic text and saved it in a variable? Apologies im new to powerautomate :)

[@abm4975]

Hi Edward If all the subjects having starting has 'today is' or after 'two words' then its possible. Use the below expression split('today is monday emal', ' ')[2] Above substitute the text with subject text dynamic mapping field. Thanks

[@lovisapersson5997]

Hi, Thank you for a good video! I have a question. how do I do if one of my "inputs" turns into 2 rows? I have a "message" option and I want the whole message into one string of text but it gets separated into multiple rows depending on how long the message is. Do you have any tips for that?

[@RomaOnYouTube]

The best video ever, it helped me a lot!

[@abm4975]

Glad it helped! Thanks a lot.

[@leono5997]

I am new to PA. I always get stuck with the second compose (split). I always get the message the it is incorrect. Anyone able to support?

[@abm4975]

Hi I was away for few weeks. Do you still need help in this please? Thanks

[@beprovidelo]

Hello there thanks a lot! is it possible get the text between words? For example I just want the word DOG to be the output result from de following input 'CuteAnimal[DOG]'.

[@abm4975]

Yes you can. Do the following expression first(split(last(split('CuteAnimal[DOG]','[')),']'))

[@danililyayev3215]

What if we need to store the values into a text file? How would we do that? If we need to contiously store data as emails are being entered.

[@abm4975]

Hi Danil Are you looking to get the email content and store in a local file? Do you want to amend or append the file if you receive more emails? Please explain a bit more about your requirement. Thanks

[@CarneyBrownstreams]

Did I miss a step but then how do you put them in an excel sheet?

[@abm4975]

Hi Video mainly focus on how to extract the values. To add the values in an Excel sheet, Add the action step called Add a row into a table then map the values. Thanks

[@Noah_Oye]

This is really helpful. What if the position of the date is dynamic, how can i still extract the date from the email? Thanks

[@abm4975]

Hi Is the date is placed in a fixed position? Could you give me an example? Thanks

[@MB-im1mr]

Hi, Thanks for the very helpful explanation. How ca nI copy a table from the email body to excel using power automate?

[@abm4975]

Hi Thanks so much for the support. I was away for few weeks. Do you still need help in this please? Thanks

[@MB-im1mr]

@@abm4975 Hi, Yes I still need support how I can copy a table from an email body into excel using power automate. Thanks!

[@dannyrene]

Hey I was wondering if there was some way to contact you for a consultation. Please let me know.

[@abm4975]

Hi @dannyrene Thanks for your query. Please see my channel about section. Scroll down and see business enquiries. Click and find my email address. You can send me an email or connect me via teams. Thanks

[@dannyrene]

did you get my email?@@abm4975

[@abm4975]

Yes

[@rorycampion5890]

Exactly what I need to process some emails, the trouble is our exchange is on prem so the connection to the mailbox doesn't work. Tried using Power Automate Desktop but most of the operations available online aren't in the desktop version

[@abm4975]

Hi Rory Thanks for watching the video. Yes, the onprem exchange is not supported. Thanks

[@rorycampion5890]

@@abm4975 Thanks, managed to get it part working by forwarding to Gmail and doing it from there. Now having issues getting the specific data I need from the output (eg I need the data on the line after "01", sometimes there are a few instances of "01" in the email but the data I want is always on the same line after the compose).

[@rorycampion5890]

Watched a bit more and worked out the line reference 😃 Only thing now is I have all the data in ["Data"] 🤔

[@rorycampion5890]

Ignore, must watch the next 30 seconds of the video 😅

[@abm4975]

Hi Rory Glad it was useful. Thanks

[@alexmeiaboca]

Olá, não sei como expressar isso, mas você simplesmente me salvou. Sua solução simples, rárpida, eficaz. Já estava desistindo, após muitos vídeos assistidos, perguntas em fóruns e grupos. Novamente, muiiiiiiiiiiiiiiiiito obrigado.

[@abm4975]

Thanks

[@weuiwhuih]

hi, could you do a tutorial on how to extract values if the emailbody contains a table?

[@abm4975]

Hi @etheatan1 There is already a few out in community. Here is one by @DamoBird365 kzbin.info/www/bejne/n2WqeXZ9gqZ3bpo

[@laszlogyor6628]

Very useful video!👍

[@abm4975]

Glad it was helpful!

[@mikebaguncinha4575]

You are my hero!!!

[@abm4975]

Thanks

[@hoereramschaedel]

this is pure gold!

[@abm4975]

Hi Michael Thanks for the support.

[@timpullen9752]

Thank you so much for your wonderful video. I receive emails every day with a URL linking to a CSV or EXCEL file. Is there some way to automatically save the CSV or the EXCEL file to Sharepoint please.

[@abm4975]

Hi Unfortunately its not possible. Thanks

[@marthasuarez8680]

You are awesome! Thank you so much 🤗

[@abm4975]

Happy to help!

[@feliperiveros4396]

thanks, very helpful and well explained.

[@abm4975]

Thanks for your support.

[@georgestascau8933]

You saved me, thank you!

[@abm4975]

Glad it helped!

[@NandagopalB]

this is what i need, thanks a lot

[@abm4975]

Thanks Nandagopal

[@tagueocallaghan6891]

GREAT VIDEO. I am wanting to extract the last word of a line within the text. currently I am using last(split(outputs('Converted_Data')[6],': ')) but this displays the entire line of text when i only want the last word in that line

[@abm4975]

Thanks for the support. last(split(outputs('Converted_Data')[6],': ')) Expression looks ok to me. I need to see your data? Thanks

[@tagueocallaghan6891]

@@abm4975 the data is: " * Location: THURS Morning - Prestart 6:30AM - At: - Sandercock Park - Gold", " Coast Hwy at Paradise Ave, Mermaid Beach (320) (3D Job) (F/Move 10AM) (", " Ammar), QLD", I want to extract the last piece of text QLD, but this value can change.

[@abm4975]

Try this and let me know. last(split(last(split(outputs('Converted_Data')[6],': ')),','))

[@ftnetops4264]

little hard to follow from time to time, as it appears things have already changed, but I'm getting through it. Thanks !

[@abm4975]

You're very welcome

[@lakshmankumar1483]

Good one brother!

[@abm4975]

Appreciate it!

[@jorgeortega1486]

Great explanation and it works with those fixed incoming emails. What about if the emails are not fixed but have different formats, however works like Short Description: Description: Sender: and Callback Number: are in different lines. What expression can you use to extract this data without knowing in what line they will be?

[@sabreaion6074]

There is an online form that is sending me emails like that, most of the time the value that I need is not in the same row, this value always is 8 characters long and has a "reference # " next to it. What I did is after the "html to text" I put a "find text position" where it will search for "reference # ", then a new step "substring" where the starting position is the output from the previous step and the length is 8 characters. After that you can search for a video explaining how to move that data to an excel, pdf, etc. Maybe @abm can make a video with this explanation.

[@abm4975]

Hi Apologies for the late reply. Do you still need help in this? Thanks

[@sabreaion6074]

@@abm4975 what I am still trying to do is to save this email to one drive, just to let you know, I don't have any of the premium tools. Maybe this is not important if you come up with a solution/video, I exactly don't remember, but the error is something about what I am trying to save/export is larger than the length of the original text.

[@arnoldderrick4989]

Thank you so much!

[@abm4975]

You're welcome!

[@kashifhameed849]

Hi dear, thank you for sharing such a useful videos. I am looking for power automate flow in which I want to filter customer email and send the email with multiple attachments dynamically!!

[@abm4975]

Hi Kashif What's the data source? SharePoint or Dataverse? Thanks

[@karthikponnam3671]

Hi sir, you explained very well. I understand how to pull the text from email boday to power automate But i have one question.? Can you please let me know how can we write the outputs from power automate to excel..?

[@karthikponnam3671]

Sir i usually get the reports to my email on daily basis. At the month end I will be copying the data manually from email to excel and consolidating and doing the required calculations. Can we do the above requirement using power automate..? Your suggestions will be help us to improve our knowledge on Microsoft tools and save lot of time for me

[@abm4975]

Hi Karthik Could you please post your issue in Power Automate community and tag me as @abm. I will have a look. powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity

[@MurtuzaKantawala]

Hi Sir this Murtuza from India I watched this video and it was really knowledgeable video But as you extracted name from email body My task is extracted 1. State 2. City 3. Channel PartnerID 4. Customer ID in loop Process -> if email arrives then check with state first if folder exist in Sharepoint Library then skip to City Extract City if city folder not exist then create new folder under that state folder then skip to channel partner id check for folder if exist then skip to customer id and if folder not exist then create customer folder under that Channel Partner folder how to achieve this sir please help me out with this

[@abm4975]

Hi Did you manage to extract the data from Email? Do you need help with SharePoint for searching folder exists or email extracting the data? Thanks

[@randomterran3260]

Thank you!

[@saravanavel3048]

Excellent

[@abm4975]

Thank you so much 😀

[@youngdataio]

🙌

[@abm4975]

Thanks for the support Elian.

[@maheshkpidaparti6741]

Thanks for the explanation and really informative. I have a quick question, What If I want to opt-out entire row in email body. Example in this case would be, EE Count: 420 and Opp #: 486912

[@abm4975]

Hi Mahesh Can't you use the carriage return as separator for the split() expression. That will help you to get the entire row. Thanks

[@MABhats_Handle]

#Abm Abm ,I need your support how could i contact you.

[@abm4975]

Check my Channel about and connect me via email. Thanks