Power Automate - Extract Text Values from an Email Body

Рет қаралды 72,689

2 жыл бұрын

This video explains how to extract text values from an email body using Power Automate.
Power Automate Community
powerusers.microsoft.com/t5/M...

Пікірлер: 124

@MrFulcrumSystems 22 күн бұрын

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

@abm4975 6 күн бұрын

Glad it helped!

@perfectpipesplumbing3992 Жыл бұрын

This was perfecto! The absolute best explanation on KZfaq 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.

@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 6 ай бұрын

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

@sabreaion6074 2 жыл бұрын

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 2 жыл бұрын

Thanks for the support

@jocelyng.7142 2 жыл бұрын

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

@abm4975 2 жыл бұрын

Thanks for the support Jocelyn

@mirjamkaal 2 жыл бұрын

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.

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

@Fernanda-uh7iz 2 жыл бұрын

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

@abm4975 2 жыл бұрын

Hi Fernanda Thanks for your kind words and support.

@southernmustang89 Жыл бұрын

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

@abm4975 Жыл бұрын

Glad it was helpful!

@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

@thief12208 Жыл бұрын

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

@abm4975 Жыл бұрын

You're very welcome!

@hoereramschaedel 2 жыл бұрын

this is pure gold!

@abm4975 2 жыл бұрын

Hi Michael Thanks for the support.

@damienfernando36 5 ай бұрын

Thank you. Very Useful and nicely explained.

@abm4975 5 ай бұрын

Glad it was helpful!

@petersassmannshausen3099 8 ай бұрын

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

@abm4975 8 ай бұрын

You are so welcome!

@feliperiveros4396 Жыл бұрын

thanks, very helpful and well explained.

@abm4975 Жыл бұрын

Thanks for your support.

@laszlogyor6628 29 күн бұрын

Very useful video!👍

@abm4975 6 күн бұрын

Glad it was helpful!

@marthasuarez8680 Жыл бұрын

You are awesome! Thank you so much 🤗

@abm4975 Жыл бұрын

Happy to help!

@NandagopalB Жыл бұрын

this is what i need, thanks a lot

@abm4975 Жыл бұрын

Thanks Nandagopal

@georgestascau8933 Жыл бұрын

You saved me, thank you!

@abm4975 Жыл бұрын

Glad it helped!

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

@juajal87 2 ай бұрын

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.

@arnoldderrick4989 Жыл бұрын

Thank you so much!

@abm4975 Жыл бұрын

You're welcome!

@RomaOnYouTube 4 ай бұрын

The best video ever, it helped me a lot!

@abm4975 4 ай бұрын

Glad it helped! Thanks a lot.

@mikebaguncinha4575 Жыл бұрын

You are my hero!!!

@abm4975 Жыл бұрын

Thanks

@andrewalexander9096 6 ай бұрын

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!

@lakshmankumar1483 Жыл бұрын

Good one brother!

@abm4975 Жыл бұрын

Appreciate it!

@ftnetops4264 5 ай бұрын

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

@abm4975 5 ай бұрын

You're very welcome

@nikitaojha1809 6 ай бұрын

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

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

@davidmalone3461 5 ай бұрын

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

@abm4975 5 ай бұрын

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

@lovisapersson5997 5 ай бұрын

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?

@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

@jorgeortega1486 2 жыл бұрын

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 2 жыл бұрын

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.

@saravanavel3048 2 жыл бұрын

Excellent

@abm4975 2 жыл бұрын

Thank you so much 😀

@affy57 2 жыл бұрын

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 2 жыл бұрын

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

@jibbsautomations 10 ай бұрын

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

@abm4975 10 ай бұрын

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

@jibbsautomations 9 ай бұрын

@@abm4975 Thank you!

@abm4975 9 ай бұрын

You are welcome

@danililyayev3215 2 жыл бұрын

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 2 жыл бұрын

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

@Noah_Oye 9 ай бұрын

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 8 ай бұрын

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

@kashifhameed849 2 жыл бұрын

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 2 жыл бұрын

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

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

@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

@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

@beprovidelo 3 ай бұрын

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 3 ай бұрын

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

@harshadlatey5813 10 ай бұрын

can we do it for 100 recent mails at a time

@abm4975 10 ай бұрын

Hi Have a look on Graph API learn.microsoft.com/en-us/graph/api/resources/mail-api-overview?view=graph-rest-1.0 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!

@rorycampion5890 2 жыл бұрын

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 2 жыл бұрын

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

@rorycampion5890 2 жыл бұрын

@@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 2 жыл бұрын

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

@rorycampion5890 2 жыл бұрын

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

@abm4975 2 жыл бұрын

Hi Rory Glad it was useful. Thanks

@weuiwhuih 11 ай бұрын

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

@abm4975 11 ай бұрын

Hi @etheatan1 There is already a few out in community. Here is one by @DamoBird365 kzfaq.info/get/bejne/n5p3e6Z4sqeoaGw.html

@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

@edwardsotelo7568 2 жыл бұрын

Can we also extract string from subject as well?

@abm4975 2 жыл бұрын

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

@edwardsotelo7568 2 жыл бұрын

@@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 2 жыл бұрын

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

@karthikponnam3671 2 жыл бұрын

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 2 жыл бұрын

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

@dannyrene 9 ай бұрын

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

@abm4975 9 ай бұрын

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 9 ай бұрын

did you get my email?@@abm4975

@abm4975 9 ай бұрын

Yes

@youngdataio 2 жыл бұрын

🙌

@abm4975 2 жыл бұрын

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