Email Excel Reports with Power Automate & Office Scrips - One Click Automation 🤖

[@rakesh4642]

This was splendid...Bro pls do more videos on Power Automate

[@eddiepaterson1583]

Love your Video examples. We have a CLUB attendance sheet which is used dynamically when the members arrive on the day. When completed I have a 'Menu' sheet which has an ''email to" button. My system uses VBA. This button initially generates a copy of the attendance sheet, makes a PDF, saves that to a 'historic information' folder, then attaches to a 'New email, window. There we add the recipient/s and send. We could automate the recipients and send auto as well but they sometimes change. Don't want to send a dynamic sheet, just a static sheet that can be discarded. Do this also with the Club Membership, Committee and historical attendance reconciliation. Love automation and really appreciate the tips I get from yourself and some other presenters.

[@DAXifiedSatish]

I faced similar problem. I need to send aroud 20 reports every week. Mail should have some visuals and an excel file as attachment to email. The problematic part was, in mail body I don't want to send visuals as image. I want to send it as html so that recipients can copy data if required. My solution is using Power Automate and paginated reports from power BI. It is working as expected but there is also a problem that u can only trigger 1 report at a time and have to wait for 5 minutes to trigger another report. But my mail require 2 reports one on mail body another as attachment I solved it using 2 connection 1 that usage to create attachment and 2nd for body of mail report Power Automate is changing the way of reporting. It is a wonderful tool

[@shanemammen3624]

Hi Chandoo, thanks for the video - how would you do this for a pivot table that needs to filter based on todays date to output daily data?

[@Jobin.Thomas]

Much awaited video..waiting for a series. Thanks Chandoo!

[@awesh1986]

Didn't understand much but its awesome

[@have3086]

Thank you for sharing your knowledge.

[@NeLzKieTV]

Thank you so much for your tutorials Chandoo!👌

[@vasanthsathiyanarayanan8026]

Mind blowing man..

[@CarlyFTD]

Nice T-shirt ! 👍 and thanks for the useful video as always 😊

[@chandoo_]

My pleasure!

[@MamawaniAdam_9664]

Hi Chandoo, thanks for the tutorial. May u suggest how to refresh power query using power automate?

[@thicketroad]

Definitely agree that the steps to doing this is not at all intuitive compared to VBA and the scripting language again is difficult to read for a Joe Average coder like me. That said our organisation is blocking macros soon for good reason as a policy in 365 so it will be the only solution that wider groups can adopt. Microsoft could do so much better with the scripting language though if they want it to be as ubiquitous as VBA.

[@rtshort]

I've used VBA to send an email and copy parts of the cells, charts and etc. as an image in the body. I use a range of cells for the "mailto:" and a cell for the subject and body text before inserting the image. I think I prefer that way over what you just went through. I'm not sure that I'd like to use a "link" created to do the job, along with all of the coding which as you said is quite intense. I wouldn't know if the link would be good forever or not, though I'm sure it would. I think I'd prefer everything to run from my PC. Very interesting though. Thanks.

[@VBASqlAccessPowerApps]

you can do either

[@e_senior]

Nice step by step explanation. Looking for a work around to the VBA send email since it fails on my office 64 bit install. This seems like a possible solution. Thanks.

[@chrism9037]

Very cool stuff, thanks Chandoo!

[@TamilSelvan-lf3fy]

Sir Maths concept for data analyst ? Then power BI or tableau ?

[@pgkannan]

Happy Ugadi Anna

[@alexandertipan172]

It's an excellent video with a clear explanation!!!! I have a question, Is it possible to execute all scripts with the same button? Thanks for your answer.

[@chandoo_]

Thank you. Even though we have two scripts, only one button is needed. It is just that the logic flow goes from button - > Power Automate Flow link - > Creating the email content script - > Power Automate to send the email

[@sakthii2403]

Hi sir, do's can we automate Data cleansing process in Power Automate?

[@ManojKumar-zn2gf]

Hi, Is it possible to get a used range (basically count) of any particular column from a sheet in the Excel workbook using office scripts? Though we have a getusedrange method it always gives us a used range of the entire sheet, but I need it only for a particular column.

[@DeeAdamsDillingham]

This was awesome but where do we find the code you mentioned in the end of the video?

[@nikhilkukde6704]

Thanks and great work

[@phlaviofranca]

Hello, hello @Chandoo! Hope you're doing well! In the step where we create the send button, does it no longer work? I have the error: { "error": { "code": "DirectApiAuthorizationRequired", "message": "The OAuth authorization scheme is required. Please add authentication scheme and try again." } }

[@ICTNuggets]

Thanks a lot Chandoo. This is awsome

[@TroublesomeTalha]

I am a having a problem where the image I get in the email does not display any data even though I am getting the right image when I manually convert the base 64 string.

[@gajjashivaleela1325]

Please please please create more videos on Power automate 🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏

[@arnab246]

Thanks ... Can please add same with vba

[@shubhamnimgade7055]

How significant is Power Automate for web scraping?? Is it a substitute for programming languages like python to scrape data??

[@lykahayen]

How about copy the table and send to email?

[@ysantosh]

I haven't use this anna.. somewhat confusing for me as I am new to Office Script and Power Automate.. Nice informative vid though... Please do much more vids on PA so that we all can learn from new things as you always do..

[@theravikk1755]

Make a video on Microsoft copilot. It will replace data analysts?

[@PinkeshArtCraft]

What if button I don't need, it should go directly on timely manner automatically

[@prashanthbs3068]

Sir Can we Add CC in mail and how to code to add CC and BCC in mail.

[@b_rizzle4808]

hi Chandoo, i have excel version 2302, build 16130 and i have automate tab and scripts, but i don't have the record actions button. which version are you using? im on desktop excel

[@karthikpottumuthu]

Does the office script kill VBA coding....?

[@vvkarticle]

can we make like these automate (Just by clicking we are getting desired numbers) which will happen in Power BI how can we do in Excel please make a video, If there is any type of that video already made please give the link

[@grgshany9250]

Iam a beginner excel user and i see your videos is very good and i want to learn from you to be advanced user but you have too many videos so i dont know from where i have to start to build my excel knowlage to be efficient as much as i can If you read my commemt pls help me and guide me for the best option, and thank you

[@nwel20]

Hi Chandoo - to run the flow for other users, do you have to share the power automate flow also? And will it send the outlook email using the author's account or the other user's outlook?

[@chandoo_]

Yes, you need to share the flow with others (by adding them as co-owners). Then it will run and use the "user's" email.

[@ManojKumar-zn2gf]

Great vedio, I have the files in sharepoint server instead of onedrive. Is it possible to run office scripts for files which is present in sharepoint server using power automate flow? When I tried for the files present in onedrive it worked but not for files present in sharepoint. It would be better if I got calrity on this. Thanks in advance!

[@chandoo_]

I think it is possible. But you need to see if the sharepoint connectors are enabled. I haven't tried it.

[@canirmalchoudhary8173]

This is amazing but I need to get Record action button and after that I would have to use that only.

[@semeretsegaye3568]

Hi Sir How can I Find data For data Analysis with task todo For practices pls

[@ahlanbutuhasan8735]

userful and helpful video thank you @Chandoo

[@sandeepc6976]

Hi chandoo, this is sandeep here, I have a task to automate reports in power bi through azure devops, how can I automate this so that charts gets automatically updated every month. I have the data in excel csv file which contains different types of invoices, can you please respond to my comment.

[@DAXifiedSatish]

I tried to use office scripts but it is a bit complicated. I want other user to run flow which is using my script but it fails. I shared, saved on sp folder still some issue. Hope will be familiar with it soon

[@amermx]

available for office 365?

[@SGH-rt6ci]

Hi, I have a serious issue that I cannot overcome in VBA. Everyday I copy rate for natural gas from commodity exchange and paste it in excel sheet one under another to keep track of the prices. Unfortunately vba automation seems no to work. When I start my computer every morning I want the macro to refresh the connection and then copy new rate and paste it in the first empty cell under the rate from the previous day. RefreshAll in Private sub does not work. Postponing for 10-15 seconds before next macro is run does not help either. Any suggestions ?

[@chandoo_]

I suggest posting this question along with your code in a forum or Q&A website for help. With the details you've provided, I suspect, something has gone wrong with the connection or source system. You can try stepping thru the code to see the problem but if it is an asynchronous connection, you may not see the issues rightaway.

[@vishnuvardhan2828]

If i want to sent a group of employees then how ?

[@palakvarshney6800]

Hey Chandoo nice video but it would be better if you have used power bi in place of excel.

[@Morale777]

thank you for the great video, please make the same video but now with pivot table and pivot chat. Please I beg

[@neelmanibhardwaj8872]

Please tell me how to merge two cells in the same row in power query

[@chandoo_]

I think you mean two columns. Select both columns (hold shift) and then from transform ribbon use the merge columns option.

[@neelmanibhardwaj8872]

@@chandoo_ Thanks for answering. But I do not want to merge the entire columns, Suppose I just want to merge row 1 of two different consecutive columns, Just like how we simple merge and centre in excel , how to do that?

[@chandoo_]

Power Query is not Excel. You can't create merges like that.

[@neelmanibhardwaj8872]

@@chandoo_ Thank you for helping

[@waqasmunawar3373]

Thanks a lot Boss

[@Metalhorse_]

Agreed, VBA works much better, but this ones interesting too. What if I want to simply copy the cell range instead of making it to an image does it make the process less complex?

[@chandoo_]

Unfortunately, the object model of Office Scripts is not capturing the copied range as image (or values) correctly. I tried this initially but gave up as the image was blank.

[@jiabinchen1934]

What if I want to loop a list of email recipient and send a multiple emails?

[@chandoo_]

You can use loops in office scripts and Power Automate. Maintain email IDs list in the Excel and load them to PA. Then loop thru the items and activate the email script for each of them.

[@jiabinchen1934]

@@chandoo_ Thank you!

[@shalomfxacademy6779]

awesome boss

[@maven558]

Hello Good morning sir.

[@Anonymous-zn9qo]

Hi chandoo will this going to work on excel 2016

[@chandoo_]

No. Office Scripts is not available in older versions.

[@vasumedesetti9755]

Your Videos are really awesome and very helpful Sir..can you please also do videos on how to create automated reports in SharePoint from Scratch

[@rp-mb8jb]

Is power automate a free tool? What is the difference between power automate and power automate destop? Kindly answer

[@chandoo_]

Power Automate Desktop is a free tool that you can download and use to automate tasks on your computer. Power Automate Web is a subscription service that can automate tasks on the cloud.

[@rp-mb8jb]

@@chandoo_ Thanks a lot. Request you to do some power automate tutorial

[@mukeshvarma8662]

Hi chandu, I need your Help. Iam doing a manual task over 6 months. I need to make it Automate. I have an excel files which has Queries connection to different Excel files. Every day I need to open the excel files and click on refresh all and Refresh the power Bi data set manually. Please tell a way to RefreahAll the queries in Excel File without Opening the File. The File is Located in SharePoint folder. Please Help me with this Buddy. Thank You in Advance.

[@ArhamAlig-jp9hn]

Use power query editor

[@shanmugapriyayu2141]

Hi Chandoo! Is it possible for you to do some videos on using zoho sheets?

[@AlesKus]

Hi Chandoo, i cannot find demo file... Possible to get link?

[@chandoo_]

See here for all the scripts - chandoo.org/wp/send-email-from-excel-with-office-scripts-power-automate/ There is no file as it wont work on your computer anyway.

[@kevingodsave8893]

Interesting video, Chandoo! I'm curious if the solution could be created in Power Automate only. Maybe a consult / collab with Anders Jensen?

[@chandoo_]

Power Automate can only do some basic tasks on the spreadsheet. That is why we are using OS + PA option.

[@pratiksalvi5514]

Hey chandoo sir could you please help me with my career I am a BCom graduate and I want to learn data analytics but the problem is that with my skill I don't know how to use Excel I want to start using Excel from basic could you please suggest me anything

[@toptechkit]

Probably the Power Automate the Desktop version may be easy

[@watchnow4211]

Nice

[@academysolution8074]

How to automate this Is it possible ?? created a mail merge connected to an excel sheet to print labels. However, a single label is reproduced for each record. I would like each label to be repeated X number of times indicated in column G of the excel file so to explain myself better, in the word file I sent you, 15 labels of the first type will appear, then 10 of the second, 0 of the third, etc.. in the word file I sent you a label is shown for each line of the excel sheet, one for the code A30452LRD-16, one for A30452LRD-28 etc.., instead I would like 15 labels to be shown for the code A30452LRD-16, 10 for code A30452LRD-28, the number must vary based on what I reported in column G. If I change the numbers in the column, the number of labels also change.

[@pravinjadhav7844]

I learn VBA from online but not in use. Also, watch this video but it seems too complicated.

[@deegadams]

Whew over my head

[@technicalknowledge9128]

Skill

[@IamYouareMee]

👍

[@dhaval1489]

Hope dubbing in the moive is better, using this cheap vesrion of hindi cheapens the experience

[@chandoo_]

🤷‍♂️ What are you talking about?

[@martinargimon730]

why are these sales pitch so long videos. !!! can u not convince investor with a short and concise video. is boring me that is why i sotp long vieos like this.. u shoudlhave learn good marketimg techniques

[@chandoo_]

hmm.. what are you talking about? Did you even watch the video or just being an npc?

[@martinargimon730]

@@chandoo_ sorry chandoo. Is not your video at all ! Sorry. Is actually the ‘KZbin’ commercial add that is added at the beginning of your video. Not sure if u noticed. It was about Elon Musk and his new A.I . Venture. The video ( speaker) goes on and on trying to convince you to invest in that venture. Sorry for the misunderstanding. I thought my comment was going straight to that guy trying to get us to invest. Your video ( by thr way ) as always excellent. I was actually looking for a solution to ‘Mail merge’. Thanks

[@chandoo_]

Understood. Unfortunately, I don't control the ads that show up on YT. :)