Import Outlook to Excel with Power Automate Tutorial

Рет қаралды 346,637

Күн бұрын

🔥 Learn Excel in just 2 hours: kevinstratvert.thinkific.com
In this step-by-step tutorial, learn how to import email from either Gmail or Outlook into Microsoft Excel using Power Automate. First, we visit Office.com and sign in with a free account. Next, we set up a new workbook in Microsoft Excel and enter the columns that we want to populate with content from an email. We proceed to Power Automate, where we set up a flow to take the contents of an email and insert them into our workbook table. Finally, we test the flow to ensure that the email successfully imports into the Excel worksheet. By the end, you'll be able to take the contents from an email and fill it into your Excel workbook. The resulting flow will work automatically in the cloud and there's no need to launch Outlook, Gmail, or Excel for this to work.
👋 Additional resources
- How to use Microsoft Power Automate: • Power Automate Tutoria...
- Reference guide to using functions in expressions for Power Automate - this will help you understand the expressions below and how they parse text in an email: docs.microsoft.com/en-us/azur...
- Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
- The expression in the "Cookie Type" field:
first(skip(split(first(split(body('Html_to_text'),'Quantity')),': '),1))
- The expression in the "Quantity" field:
first(skip(split(first(split(body('Html_to_text'),'Delivery Method')),'Quantity: '),1))
- The expression in the "Delivery Method" field:
first(skip(split(body('Html_to_text'),'Delivery Method: '),1))
⌚ Timestamps
0:00 Introduction
0:56 Sign into office.com
1:28 Create new Excel workbook
3:07 Navigate to Power Automate
4:02 Build automated cloud flow
5:15 Example email to import into Excel
5:39 Step 1: Check when a new email arrives & filters
6:35 Step 2: Convert HTML into text
7:20 Step 3: Add new row into Excel table with email contents
9:55 Extract content from body of email using expression
11:28 Test flow
13:05 Wrap up
📃 Watch related playlists
- Playlist with all my videos on Power Automate: • Power Automate
- Playlist with all my videos on Outlook: • 📨 How to use Outlook
- Playlist with all my videos on Excel: • ❎ How to use Excel
🚩 Connect with me on social
- LinkedIn: / kevinstratvert
- Twitter: / kevstrat
- Facebook: / kevin-stratvert-101912...
- TikTok: / kevinstratvert
🔔 Subscribe to my KZfaq channel
kzfaq.info?...
🎬 Want to watch again? Navigate back to my KZfaq channel quickly
www.kevinstratvert.com
🛍 Support me with your Amazon purchases: amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#stratvert

Пікірлер: 412

@KevinStratvert 2 жыл бұрын

🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com ⏭ Watch next - playlist with all my free tutorial videos on how to use Excel: kzfaq.info/sun/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY

@SWD2529 Жыл бұрын

Can you create a video tutorial on how to create a button or macro to import an appointment from excel to outlook calendar showing how to select a specific calendar and not just the default?

@jamiewill Жыл бұрын

This is very helpful Kevin. Thanks! Is there a way to also import Senders Name ?

@srikanthkatla308 2 ай бұрын

Amazing video❤. But, can you explain more about expressions you wrote

@loloysan6417 3 жыл бұрын

BENEFICIAL! You are really a life saver Kevin, Power Automate has a lot of connectors or combination can be done but don't have time to explore each of them. Thank you very much for updating us and also sharing different use of Power Automate. Will be waiting for more video like this. Thank you for time and effort, always be safe you and your family

@francismastro 2 жыл бұрын

You have no idea how much time you're saving me! Thanks and keep up the amazing tutorials.

@peterxxl1244 3 жыл бұрын

Wow! How interesting and impressive is this ability to automate Excel functions with "Power Automate"! I had a whole similar problem some time ago, involving scanning emails arriving in a particular mailbox and writing the results to an Excel worksheet. So far, I've been working with a combination of an Outlook macro that responds to the "add item" event (which fires whenever a new email arrives in a mailbox) and an Excel macro that responds by writing "subject", "body" and "to" contents to a spreadsheet. The problem: the whole thing only works when the Excel workbook is open and thus active. I can't wait to experiment with this solution. Thanks a lot for this! :-)

@_alphin_joe 3 жыл бұрын

It’s very helpful for people who needs to import Mails for their work. They need it. Helpful Video 🎬.

@vijayarjunwadkar 3 жыл бұрын

Super helpful tutorial Kevin! This is going to open many possibilities for me and my team! Thanks a lot! 😊👍

@user-js5th1qv5i Жыл бұрын

Hi Kevin, lovely video that took me through the process, but I would love for you to clarify and showcase different expressions that can handle different line extractions and just a guide to these in general.

@SavageGothamChess 3 жыл бұрын

This is awesome! You would probably get over a 700,000 cookie orders within the next 24 hours. Another great video as always. Congrats on your hard work, you've reached 750k!

@erib156 2 жыл бұрын

I'm gonna cry, you changed my life! The first video simple and well explained 😍

@gajabathiarjuntg2390 3 жыл бұрын

Great video Kevin. This method is very easy and helpful to import emails to excel. Thanks Kevin for pulling out this video :)

@ulligruber1344 3 жыл бұрын

Delivery method: pigeon 🤣 Love Kevins humor 😁

@nairobi203 3 жыл бұрын

Flying cookies....

@qrsimon 2 жыл бұрын

@@nairobi203 maybe the pigeon ate them

@miransh 3 жыл бұрын

Very detailed and step by step vide Kevin- thanks so much for your hard work- appreciate it ! I went thr half and gave up the idea of proceeding when codes came in - I think this is still too complex for a common person , Microsoft should still simplify this for us...and Stop forcing us to us ONE DRIVE - that a dampener- once they make you use one drive then Microsoft will keep complaining about account size--- I let it go ..thanks....

@travelwell6049 3 жыл бұрын

Video started and I thought; I’m never going to to need that. Saw data going into a spreadsheet and thought; Oh wow! I totally need that! 😂

@RichardWadeUK 3 жыл бұрын

Amazing tutorial Kevin, thanks!

@arunkaggarwal1962 11 ай бұрын

Kevin - All your videos are very insightful and meaningful. Thank you for doing what you do to help the developer community. Would you kindly do a video to explain the formulas you used to extract 3 different values from your email? Depending on the format of the email, the values can be tricky to extract. If you can explain your formulas, then we will be able to use this in our email formats as well. Thanks in advance.🙏🙏🙏

@amrkalammansoori4411 7 ай бұрын

yeah, the expressions are keeping me hanging. I want to understand it in detail and how it is structured.

@KatRG6453 Жыл бұрын

You're videos are amazing and I appreciate them so much!

@HenryGonzalezTv Жыл бұрын

You are by far the best all over the internet. Thank you 🙏

@DannyWiraDharma 3 жыл бұрын

Thank you so much, Kevin! I shared this video to my friends and clients.

@KevinStratvert 3 жыл бұрын

You rock!

@elishatech4600 3 жыл бұрын

since I discovered your channel I have learnt a lot from you. Thanks man keep up

@viveksingh-nt3po 3 жыл бұрын

Very useful! Do make more videos on Windows power automate due to heightened focus on automation these days!! Thanks buddy!

@KevinStratvert 3 жыл бұрын

More to come!

@viveksingh-nt3po 3 жыл бұрын

@@KevinStratvert really appreciate your efforts in sharing the knowledge with us!!

@eleos11 3 жыл бұрын

Hey Kevin, well done! What if customers don't use the same keywords - expressions for their order? Is it possible to solve this kind of issue?

@adriandsilva9250 Жыл бұрын

Hi Kevin, this has been extremely beneficial. Thanks so much! On one of my recent forms, I've run into an issue with having an apostrophe . Any suggestions on how to overcome that when creating my expression? Ie, what if the email had Delivery Method's. Thanks for your time.

@BrunoDallavalle 11 ай бұрын

Thank you Kevin! Took me some time to get how these functions are working, not really straightforward. If you could do some deep dive on them I'm sure it will be greatly appreciated!

@saajalsharma659 2 жыл бұрын

Hey Kevin, this really helped in creating an email tracker for one of my processes, Thanks a lot :)

@bryankendig3989 2 жыл бұрын

Great video. Very helpful. Could you please show expressions if row headings , “cookie “, “quantity “, “delivery method” were switched to column headings??

@lamjs 3 жыл бұрын

Thanks for the amazing tutorial Kevin! Would you show us how to add subtitle (with customized frame/border) in future video? Thanks : )

@brendamartinezgomez2297 11 ай бұрын

All your videos are very insightful and meaningful.

@fallonopperman8293 2 жыл бұрын

Thanks Kevin! I was having issues getting Step 2 until I turned off split in the settings of Step 1.

@JWiley-kd3iw 3 жыл бұрын

I learn so much from you Kevin. Keep it coming ☆☆

@anurangaehelepola1245 8 ай бұрын

A really great explanation! Thanks for this!

@zakeerrg3010 3 жыл бұрын

Kevin. Thanks bro. Love to see more of such useful videos.

@chulbulclub283 Жыл бұрын

Hey Kevin, what if my mail body contains Data in tabular form ? what separator can i use ? as in your case :(colon) has used. Thanks

@JustBlankNoInfo 3 жыл бұрын

A very informative and fantastic video! Thank you for the handsome smile in the thumbnail! Really bright

@mukamil33 3 жыл бұрын

Thank you so much Kevin, omg you have no idea how this video helped me,

@fazrinfarook7557 3 жыл бұрын

If Kevin Post a video I will watch it, whether Do I need this tutorial or not => From Sri Lanka

@adepejuadegoke9960 3 жыл бұрын

Great tutorial. Thank you for all you do.

@KevinStratvert 3 жыл бұрын

My pleasure!

@MrDirewolf4377 Жыл бұрын

the hardest part of this tutorial, the expressions, gets completely glossed over. The examples given in the text don't make any sense.

@phillharris3656 3 жыл бұрын

Very well done Kevin. I'm using PowerAutomate more and more and think it's been a real gamechanger for regular users. Do you know if it is possible to copy emails into a SharePoint list or library via PowerAutomate?

@jeremyjpegan 3 жыл бұрын

how are you keeping the format of the body uniform so the expressions run correctly? Is the "order cookies" button on your site a mailto: with a pre formatted email to keep customers sending consistently formatted emails?

@bavandetemadi589 Ай бұрын

Brilliant. So clear, so concise. Nice one.

@Louviewtube 2 жыл бұрын

Really helpful - thank you!

@khaledk9069 2 жыл бұрын

thank you so much for this video, it is really amazing. I have a request, would you please give us more details about formulas on how to extract data from the email body (split, skip...). thanks again

@chokshisuraj Жыл бұрын

Hi Kevin, thanks for this video. One question, can we also use this to create dashboard/report for monthly or weekly sales data?@KevinStratvert

@vidajavan6922 4 ай бұрын

Very helpful. Thank you for this great tip!

@Hoops-Senior 3 жыл бұрын

Hi Kevin, I've been searching but cannot find what I'm looking for and hope you can help. I have a CSV file in my OneDrive and need to be able to import the contents into an Excel worksheet as a table and specify the data type for each column (TEXT, Text, Number, Currency). Any chance of a tutorial for this, as I don't see anyone else doing it, but it seems to be a reasonably common requirement? Thanks.

@VirtualkitchenwithLaura 3 жыл бұрын

I really like this method. Thank you for your knowledge.

@KevinStratvert 3 жыл бұрын

Glad it was helpful!

@Greathall75 2 жыл бұрын

Kevin, suggestion for a future video (if you know a solution)... When copying a table from Excel and pasting into Outlook the resulting table columns are too narrow in Outlook. Any idea why, or how to avoid this issue (without manually resizing them)? I would expect a perfect copy/paste since both programs are Microsoft, but I always end up having to resize the columns.

@alertbox 3 жыл бұрын

Another brilliant video from Kevin

@coscasale4307 3 жыл бұрын

I like the journal function in outlook. Unfortunately they discontinued that function after ver2013... do unhavw any suggestions on an alternative for newer outlooks?

@atiyahhisham4265 2 жыл бұрын

Hi Kevin. Thank you for the step to step. It help a lot! Can you show me how to extract email which contain table that has multiple columns and rows to Online Excel, please. Thank you.

@aditimankar1588 2 жыл бұрын

Hi Kevin, its a nice tutorial but do you have a tutorial to write the expression for the extracting content from Email body as I got stuck at that step. Thanks

@hsobd 2 жыл бұрын

Thanks Kevin, very usefull. I want to filter the extraction from email i.e I want to only add/extract rows having cookie type A, how can we add filter extraction on email text in the power automate. I tried filter expression but I am finding difficulty in making it work.

@AvanishKesharwani 3 жыл бұрын

Very nice video, it is really helpful 👍

@abdulrahmanbaamer8624 3 жыл бұрын

Great & helpful video, can we create similar lists for all email received previously and archive (.pst files) Thanks.

@Samechaitv 3 жыл бұрын

Useful video, you got a new subscriber, keep up the good work, Thanks

@keeplearn5735 3 жыл бұрын

Hi Kevin, do you have video explain about create the expressions? Thank You

@huyfe1347 2 жыл бұрын

Hi, thanks for your video. How do you extract text from long email to Excel? How do you extract information from pdf to excel with PA?

@HennersPD Жыл бұрын

Could you use this for extracting Outlook calendar entries into Excel as well?

@milvialuisadventures7716 2 жыл бұрын

Thank you!! You make it see so sample

@mayankjain9819 2 ай бұрын

Hey @kevinStartvert, Do you have any video on updating the YTD Cookie Sales Excel Report on OneDrive from the daily or weekly reports we get for the cookie sales thru email on Outlook using Power Automate?

@p2kc1 3 жыл бұрын

Hi kevin, thank you for the great explanation Is there any limitations in the free onedrive account?

@RodrigoBedoya 3 жыл бұрын

Thanks for the video. I have been willing to move to Microsoft Outlook 365, but it seems not to work well with my gmail accounts. How can I make it import and sync contacts? thanks.

@mariancoman354 3 жыл бұрын

Super content; keep up the great work! Quick question: is there a way to make Excel deselect the "Wrap text" automatically? I am keeping an email archive, and I always have to Ctrl+A and press twice Wrap text to keep everything ready to read. Thanks a lot!

@neilgoodman2885 Жыл бұрын

I have to work with this, bat it does look promising. Stay In Touch , Respectfully, NHG

@weshumphrey6299 3 жыл бұрын

Wow. Well done.

@prokorshopal4865 3 жыл бұрын

0:01 - Hi Kevin! 👋 13:32 - 560th thumbs up from me! 👍 Cheers!

@netbuddy1089 3 жыл бұрын

Thanka lot, can you do something in Power Automate Desktop. or might have already done this and i might have missed this.

@ritheshr559 3 жыл бұрын

HI Kevin, how do we do this if I receive the details in a form of table in the body of email?

@dmclean3324 3 жыл бұрын

Beats using Power Query for sure. Looking at implementing this and then having that data go into Planner

@Milhouse77BS 3 жыл бұрын

Not sure anything beats Power Query for ease of use

@dmclean3324 3 жыл бұрын

@@Milhouse77BS Normally I would agree but getting emails into Excel using Power Query is tricky (and only supposed to be available if you have an Enterprise license). You also have to set up rules properly so you're not sorting through so much data all the time.

@mattmillward3810 Жыл бұрын

Hi Kevin, great channel and excellent video, I wonder if you can assist - I want to take this automation one step further and add a hyperlink into the table linking to the original email in outlook. Is this possible using the functions?

@wingame52 3 жыл бұрын

have you any video that instructs how to write 'expressions' for excel?

@jennakulaga2067 3 жыл бұрын

Hi Kevin, this awesome! Would you be willing to make a video on how to compose the expressions? Or do you know of a place where I can learn how to write expressions?

@KevinStratvert 3 жыл бұрын

Hey Jenna! Check out the following resource for all the various functions that you can use while writing expressions: docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference Using this, you can reverse engineer the expressions I wrote and that'll help you understand how they work. As a quick tip, start from the inside of the parentheses and work your way out.

@rlerolle 8 ай бұрын

Hi kevin, You re doing an Amazing job !! 🎉❤🙏 !!

@user-dx6rw5fp8g Жыл бұрын

Hi Kevin, Thanks for the tutorial, can you do something like this for MS Teams as well

@krisihari 2 жыл бұрын

Hi Kevin, this was indeed very useful! Thanks a LOT! I am trying to automate a process and wonder if you may have some ideas on this. I have an Excel file with project data piled and compiled from different systems. However not all projects are in the systems. So for some of them I need to collect the missing data. Now I can ask the project managers to go into the file and complete it but it would be much cooler if I can automatically send the missing fields to them via Email or form and ask them to send back the missing data and incorporate it in the sheet . Do you think there may be a way to do this with power automate? Thanks and keep the great work!

@pvrameshbabu 2 жыл бұрын

I think it can be done. If you email id of the project manager stored. Based on the missing fields you trigger email template from excel to outlook.

@zulimagonzalez830 4 күн бұрын

Hi Kevin, what would be the expression if text in the body is displayed as table where first column has the field names and second column vary dependent on the order. It is similar info but there is no " :" but a table. Thanks!

@naturesounds-9876 Жыл бұрын

@Kevin In my version I don't have expressions as on 09:02 , how I can specify my data? Thanks in advance for the valuable information 👍🏻 😍😍

@bellam2413 2 жыл бұрын

Thank you !

@airizzo4283 2 жыл бұрын

Good afternoon, I am wondering if I can possibly figure out what programming language this is called first(skip(split(first(split(body('Html_to_text'),'Delivery Method')),'Quantity: '),1))? I am trying to replicate your work, but on a much bigger scale in terms of items going into my table. It's about 500 items, and I'm trying to figure out how to write that in expression code. But when I try to find an example online to learn more...I can't seem to find it. Only Regex is remotely close. Is their a repository where I can learn more of this?

@HowToCommutePH Жыл бұрын

Hi. Will the data that was automatically read and put in excel table be deleted on the excel once you moved/dragged the email to a different folder / sub folder?

@perfectpipesplumbing3992 Жыл бұрын

Everything was great, right up to the section on Expressions. It's quite a bit more complicated when you need to extract from a much larger body of text.

@huntermccall6002 Жыл бұрын

Maybe make multiple similar automations? I myself have people essentially order multiple things in one email ("x lbs of y cookie type" and curious how to reverse the order. Idk how to get something like this to figure it out so I'm pondering trying out emailparser. Have you had any other success?

@perfectpipesplumbing3992 Жыл бұрын

@@huntermccall6002 It takes a little trial and error, i.e. doing some test runs to dial it in. But between this tut and the other, I got it working. The other tut shows how to extract the data, and this tut teaches how to update the Excel sheet via updating a table.

@kristis61 3 жыл бұрын

finally the video i waited for! but i would like to automate the fulfilled column! is there an automation where when the email is dragged from inbox to another folder named «done” to record the day i dragged the email to that folder? is this possible Kevin?

@freda_gmail5446 3 жыл бұрын

Kevin how does one stop versioning (turn off versioning old versions of same files) in one drive business in MS 365? Versioning is enabled by default and i cant seem to turn it off

@sc100200090 3 жыл бұрын

Matchless Content Buddy!

@user-zb9tf6qg5z 2 жыл бұрын

Hi, your video is very usefull for me.can i ask one thing? When i try to make the flow from forms to excel, Table is not automatically linked to the flow. Do you know what is the reason?

@olgaclaramunt1834 2 жыл бұрын

Hi Kevin, in my case I'd need to extract data from an attachment which is received in a zip folder. Too complicated?

@ninobucao5982 2 жыл бұрын

Very good demo !!!!! 👌👌

@jessicalee2668 2 жыл бұрын

Hi Kevin, Just check if the option for Excel Online (OneDrive) still available or not. In October, I still could choose but now I can only choose Excel Online (Business). That means I cannot use anymore as I don't have Business account !

@leighski 3 жыл бұрын

What if we want to extract existing emails from an Inbox or subfolder? I'd like to capture data from existing emails as well as new emails that I place in an Inbox subfolder

@JavedAnsari-vba 2 ай бұрын

Hi Kevin, is it possible to get the attachment files ( excel, word or pdf) into this master sheet where all emails are updating automatically. Thanks for very informative video

@AnshulMarele-gbaa Жыл бұрын

Hi Kevin, how can we connect email which is synced in our outlook app with power automate for creating any flow?

@akbarmahfuzalam 2 жыл бұрын

Hey Kevin, how about the table in body of the email only to be extract into onedrive excel.

@ללמד_טבעי Жыл бұрын

Do the codes you wrote in the description of the clip also work in emails from Gmail? Or does Gmail need other codes? Thanks : )

@gumikebbap 2 жыл бұрын

sup Kevin great content! I think at 10:24 the expression should say 'Cookie Type' instead of 'Quantity', when looking for the Cookie Type.

@bernipremium9193 2 жыл бұрын

I thought the same!

@anjalipatel9879 3 жыл бұрын

Very helpful tutorial thanks Kevin.Can you please make a video on Sharepoint web pages formatting add-ons to create a knowledge base ? Thanks 😊

@KevinStratvert 3 жыл бұрын

Thanks for the suggestion! I've added this to my list. Stay tuned. Cheers!

@punyapeaswar4644 Жыл бұрын

Well explained👍 Was looking for this video.. Found it very helpful 😊 Thank you 🙏 And yes Delivery method - Pigeon😂 👌

@karinthiry1753 6 ай бұрын

Hi Kevin, thank you very much for this video. Can you tell me what trigger you use if you want to import Outlook email to Excel which are already in your inbox. I suppose not "when a new mail arrives" because they are already in your inbox. And do we have to use an instant flow instead ? Karin Thiry

@PapaKayOh Жыл бұрын

Nice work. Can you do a tutorial on building a support ticketing and tracking system using MS access along with power automate?

@smithseminars2358 3 жыл бұрын

This is a great video. Everything is showing up in Excel. But when an email comes into excel, the first row is the first 2 cells filled with everything from the email body. The rest of the cells are empty. Then a second row comes into excel with the correct information and everything perfect. When I click on each of the 2 cells, they identify as only the correct parts of the body just like in the second row. Any help would be great.