Email Excel Reports with Power Automate & Office Scrips - One Click Automation 🤖
HTML-код
- Опубликовано: 23 июл 2024
- Ever wanted to send an email of your Excel report to the relevant people with a single click? In this video, let me show you how we used Office Scripts & Power Automate to solve the problem.
You will learn:
▶ Email Automation concept
▶ How to use Office Scripts
▶ How to set up Power Automate Flows
▶ How to integrate Excel with Power Automate & Office Scripts
⏱ IN THIS VIDEO 👇
=================
0:00 - Email Automation - Quick demo
2:37 - Setting up the Email Contents in Excel
3:50 - Introduction to Office Scripts
8:07 - Sending Email - Office Script
20:23 - Setting up the Power Automate Flow
29:12 - Send Email Button in Excel
31:57 - Closing Remarks (what I think about Power Automate + Office Script option)
👩💻 CODE & RESOURCES
====================
Please visit URL to see the sample code and a demo file to play with.
👉 chandoo.org/wp/send-email-fro...
⚙ MORE ON EXCEL + AUTOMATION
=============================
If you like to speed up your work with the Automation features of Excel, check out below videos too.
How to use VBA in Excel - • Learn VBA & Excel Macr...
How to use Power Query in Excel - • Power Query Tutorial -...
How to use AI in Excel - • 10x your productivity ...
How to use Formulas in Excel - • Top 10 Excel Functions...
🙋♂️ Want more?
Check out my full-length Excel course and become a PRO at work.
chandoo.org/wp/excel-school-p...
🙏 Thanks to Mark
================
Special thanks to @ExcelOffTheGrid Mark Proctor for the code inspiration. See his article here - exceloffthegrid.com/power-aut...
~
Do you ever think if an AI bot is working for another AI bot, would the first bot act busy when the boss bot is around 🤣
#powerautomate #excel 
Наука
![Rob49 - On Dat Money (with Cardi B) [Official Video]](http://i.ytimg.com/vi/fTFS58mHJh8/mqdefault.jpg)
This was splendid...Bro pls do more videos on Power Automate
Much awaited video..waiting for a series. Thanks Chandoo!
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.
Thank you so much for your tutorials Chandoo!👌
Very cool stuff, thanks Chandoo!
Thank you for sharing your knowledge.
Thanks a lot Chandoo. This is awsome
Mind blowing man..
Thanks and great work
Nice T-shirt ! 👍 and thanks for the useful video as always 😊
My pleasure!
Didn't understand much but its awesome
Thanks a lot Boss
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
Happy Ugadi Anna
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.
awesome boss
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?
Hi Chandoo, thanks for the tutorial. May u suggest how to refresh power query using power automate?
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.
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.
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.
you can do either
Nice
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..
Hi sir, do's can we automate Data cleansing process in Power Automate?
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
This was awesome but where do we find the code you mentioned in the end of the video?
Hey Chandoo nice video but it would be better if you have used power bi in place of excel.
Hello Good morning sir.
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.
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
How significant is Power Automate for web scraping?? Is it a substitute for programming languages like python to scrape data??
This is amazing but I need to get Record action button and after that I would have to use that only.
Hi Sir How can I Find data For data Analysis with task todo For practices pls
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."
}
}
Sir Maths concept for data analyst ?
Then power BI or tableau ?
Please please please create more videos on Power automate 🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏
Thanks ... Can please add same with vba
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
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?
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.
Skill
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
Make a video on Microsoft copilot.
It will replace data analysts?
userful and helpful video thank you @Chandoo
Interesting video, Chandoo! I'm curious if the solution could be created in Power Automate only. Maybe a consult / collab with Anders Jensen?
Power Automate can only do some basic tasks on the spreadsheet. That is why we are using OS + PA option.
Whew over my head
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!
I think it is possible. But you need to see if the sharepoint connectors are enabled. I haven't tried it.
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?
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.
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.
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.
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 ?
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.
Hi Chandoo! Is it possible for you to do some videos on using zoho sheets?
Probably the Power Automate the Desktop version may be easy
👍
Does the office script kill VBA coding....?
available for office 365?
Please tell me how to merge two cells in the same row in power query
I think you mean two columns. Select both columns (hold shift) and then from transform ribbon use the merge columns option.
@@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?
Power Query is not Excel.
You can't create merges like that.
@@chandoo_ Thank you for helping
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
Hi chandoo will this going to work on excel 2016
No. Office Scripts is not available in older versions.
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
Hi Chandoo, i cannot find demo file... Possible to get link?
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.
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.
Is power automate a free tool? What is the difference between power automate and power automate destop? Kindly answer
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.
@@chandoo_ Thanks a lot. Request you to do some power automate tutorial
I learn VBA from online but not in use. Also, watch this video but it seems too complicated.
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
hmm.. what are you talking about? Did you even watch the video or just being an npc?
@@chandoo_ sorry chandoo. Is not your video at all ! Sorry. Is actually the ‘RUclips’ 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
Understood. Unfortunately, I don't control the ads that show up on YT. :)