- Видео 148
- Просмотров 875 442
Excel Macro Mania
Добавлен 27 ноя 2021
Everything's possible in Excel. Come along to learn Excel VBA and create amazing macros with me here.
Get Last Row with Data Excel VBA Macro
Get the Excel VBA Objects Guide for Intermediate with extended and revised content here: excelmacroclass.blogspot.com/p/excel-vba-objects-guide.html
In this video we see how to get the last row with data in Excel using VBA macros. There are several ways to do it and here we see two or three different methods. The video also explains the limitations and situations where we need to be cautious to get the last row with content in Excel. In the next video we'll see how to get the last column with Excel VBA.
Find more content and numerous macro examples and other Excel VBA learning materials in the Excel Macro Class blog under the direct link: excelmacroclass.blogspot.com/
And yet, if you want mo...
In this video we see how to get the last row with data in Excel using VBA macros. There are several ways to do it and here we see two or three different methods. The video also explains the limitations and situations where we need to be cautious to get the last row with content in Excel. In the next video we'll see how to get the last column with Excel VBA.
Find more content and numerous macro examples and other Excel VBA learning materials in the Excel Macro Class blog under the direct link: excelmacroclass.blogspot.com/
And yet, if you want mo...
Просмотров: 76
Видео
Send Meet Invites From Excel (via Outlook)
Просмотров 25214 дней назад
Excel macro/VBA code available in this post: excelmacrobusiness.blogspot.com/2024/02/excel-email-management.html We can integrate Excel with other Microsoft Office applications using VBA. In this video we see how to send meet invites from Excel and via Outlook using VBA macros. The macro automatically creates an appointment item and defines the properties to create a meet invite in the recipien...
Excel VBA Functions SPLIT, LBOUND, UBOUND
Просмотров 24728 дней назад
See the full list of VBA functions here: excelmacroclass.blogspot.com/p/vba-functions.html Welcome to Excel VBA Functions Series. In this video we see the array functions SPLIT, LBOUND, and UBOUND, used to split a string by a delimiter to create an array of elements with a lower and upper bound. We see several practical examples of the functions in detailed. Furthermore, we see a one line of co...
Conditional Formatting Based On Another Cell Excel VBA Macro
Просмотров 508Месяц назад
Excel macro/VBA code available in this post: excelmacroclass.blogspot.com/2024/03/conditional-formatting-for-another-cell.html Conditional formatting is a feature of Microsoft Excel that allows highlighting certain values to easily identify those cells. Excel offers various options for numerical values (greater or less than, between, top 10, data bars, color scales, icons, etc.), but there are ...
Sort Values In Range Excel VBA Macro
Просмотров 195Месяц назад
Get the Excel VBA Objects Guide for Intermediate with extended and revised content here: excelmacroclass.blogspot.com/p/excel-vba-objects-guide.html In this video we see how to sort values in a range or table in Excel using VBA macros. The sort method accepts a key that refers to the column to be sorted and allows to specify the order in which values are sorted (either ascending or descending)....
Extract Emails In Excel (From Outlook)
Просмотров 8752 месяца назад
Extract Emails In Excel (From Outlook)
Clear Cells Based On Value Using Loop Excel VBA Macro
Просмотров 6733 месяца назад
Clear Cells Based On Value Using Loop Excel VBA Macro
Clear Cells Based On Value Using Replace Excel VBA Macro
Просмотров 5653 месяца назад
Clear Cells Based On Value Using Replace Excel VBA Macro
Send Google Mail From Excel - 3 Different Options
Просмотров 2,8 тыс.4 месяца назад
Send Google Mail From Excel - 3 Different Options
Add Slicers To Table Excel VBA Macro
Просмотров 9165 месяцев назад
Add Slicers To Table Excel VBA Macro
Import RESTRICTED data from Google Sheets Part 2/2
Просмотров 3895 месяцев назад
Import RESTRICTED data from Google Sheets Part 2/2
Import RESTRICTED data from Google Sheets in Excel Part 1/2
Просмотров 1,2 тыс.6 месяцев назад
Import RESTRICTED data from Google Sheets in Excel Part 1/2
Excel VBA Functions: LEFT, MID, RIGHT, LEN
Просмотров 7706 месяцев назад
Excel VBA Functions: LEFT, MID, RIGHT, LEN
Add Sheet With Name Excel VBA Macro
Просмотров 1,9 тыс.7 месяцев назад
Add Sheet With Name Excel VBA Macro
Maze in Excel with Kruskal's Algorithm
Просмотров 8677 месяцев назад
Maze in Excel with Kruskal's Algorithm
Add Conditional Formatting To Unique Values Excel VBA Macro
Просмотров 1,4 тыс.8 месяцев назад
Add Conditional Formatting To Unique Values Excel VBA Macro
Excel VBA Functions: Excel Functions vs VBA Functions
Просмотров 5079 месяцев назад
Excel VBA Functions: Excel Functions vs VBA Functions
5 Methods To Get Data From Google Sheets with Excel VBA
Просмотров 3,8 тыс.9 месяцев назад
5 Methods To Get Data From Google Sheets with Excel VBA
Convert Range To HTML Table Excel VBA Macro
Просмотров 2,4 тыс.10 месяцев назад
Convert Range To HTML Table Excel VBA Macro
Insert All Pictures In Folder Excel VBA Macro
Просмотров 4,2 тыс.10 месяцев назад
Insert All Pictures In Folder Excel VBA Macro
Excel VBA Objects: Chart Object (Part 2)
Просмотров 96910 месяцев назад
Excel VBA Objects: Chart Object (Part 2)
Create Custom Functions in Excel (User Defined Functions)
Просмотров 4,6 тыс.11 месяцев назад
Create Custom Functions in Excel (User Defined Functions)
Compare Everything in Excel Part 3: Datasets
Просмотров 1,5 тыс.11 месяцев назад
Compare Everything in Excel Part 3: Datasets
Export Table To Word Excel VBA Macro
Просмотров 3,2 тыс.Год назад
Export Table To Word Excel VBA Macro
Customize Excel Ribbon - All You Need To Know
Просмотров 4,5 тыс.Год назад
Customize Excel Ribbon - All You Need To Know
Excel VBA Objects: Chart Object (Part 1)
Просмотров 2,6 тыс.Год назад
Excel VBA Objects: Chart Object (Part 1)
Add Button To Cell Right-Click Menu Excel VBA Macro
Просмотров 4,4 тыс.Год назад
Add Button To Cell Right-Click Menu Excel VBA Macro
Hello I wrote the code for sending multiple emails in accordance with your great video. I wrote: Dim OutApp as new outlook. Application By clicking on send button I got a message: Compile error User defined type not defined. Please advise
7:24
Sir, not able to use this code once I am saving this in module under personal macro workbook and apply for other open workbook. Any solution to this?
maybe bc my excel version is new maybe the file version is old bc your video published 1 year ago so some function might not work
Sir can you please help me with code - Object is i need to reconcile to data between two files using unique id.
For that check this other video: ruclips.net/video/X6KTCuqiZOU/видео.htmlsi=uWfvsWks3eXP9mQS
Kelly Roland was close
it's not working too I tried to download the file and then launch it on Microsoft excel it says does not support
It works. Many people have downloaded and had no issues. What is not supported? which line is highlighted with the error?
Works perfectly. Thx. Is it possile to insert the signature stored in outlook?
Yes, you can do that if you first display the email body to get the signature as HTMLBody and then concatenate that with the body you want to add, all as HTMLbody - see below: With OutMail .Display signature = .HTMLBody .To = "recipientemail@outlook.com" .Subject = "email subject" body = "Dear Customer,... bla bla" .HTMLBody = body & signature '.Send 'uncomment to send End With
Great video!
Hi Sir, Thanks for the explanation. it's really very helpfull for me. I have 2 questions 1. how to send table - How can I select range for table 2. how can I send 1st sheet of excel via mail Thanks for your support
1. Check this other video to see how to convert range to html and put it in a variable (e.g. rangehtml). Then you have to add html with OutMail.HTMLBody = rangehtml. Here's the video: ruclips.net/video/Ifc2I6wmWIk/видео.htmlsi=LU_Yci_g5bt6E6rA 2. You can use UsedRange to get the range in that sheet and then convert it to html as per point 1. If you have more things in the sheet such as charts, shapes, etc, that you want to send via email, is more complicated and you would need to loop through objects and maybe save as picture and send as attachment or also in the html with <img src = "....">... there are many options there.
@@ExcelMacroMania Hi Sir, Thanks for your support. I just started learning VBI macro. I have one request from my team. I need to do automation using Excel macro. could you please support on this ? I can pay money for the support. Thank you.
@@raghumahadeva3013 Unfortunately I do not have time to do support or do any other jobs. You can find VBA experts in freelancer, upwork, etc. You can also learn quickly following my tutorials here in the channel (start with tutorial for beginners playlist), and in my blogs: excelmacroclass.blogspot.com/
@@ExcelMacroMania okay sir. Thanks for your valuable inputs.
Hello, thanks for the great video and instructions. I have 2 questions: 1. How do you align the raw to the right or left? 2. How do you separate between sentences in the body, so each one of them will start in a new raw. Thanks for your support.
1. To align content in the email you need to use HTMLBody property instead of just Body. Then you need to have the html alone or with css to align the content. For example: OutMail.HTMLBody = "<html><body><p align="left">" & htmlbody1 & "</p>" & .... 2. If you use HTMLBody as above in point 1, you can just add the break as htlml ("<br>"). If you use Body you can use vbLf as follows: OutMail.Body = bodyHeader & vbLf & vbLf & bodyMain & vbLf & vbLf & bodySignature
Thanks for your help and kindness. I wrote the Macro but from some reason the emails haven’t been sent. What have I done wrong? Can I send you a screenshot?
@@user-wc2jk4pr2g But were they sent before? You just changed HTMLBody? where do you get the error? Check your code is correct comparing with macros here: excelmacrobusiness.blogspot.com/2024/02/excel-email-management.html
I don’t get error. I push the “send” button, it gives the feeling that emails were sent but there is no sign foe emails sent or received.
9:31 compile error User- defined type not defined This error is coming
That's probably because you miss-typed some of the objects/variables. Check the correct code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
Thanks 🙏🏻 I have multiple mails in my outlook account, how I select the account I want to send the mail from ? .from?
OutMail.SendUsingAccount = OutApp.Session.Accounts("youremail@outlook.com")
@@ExcelMacroMania doesn't work, maybe because is the shared mail of my company
@@alexf2863 Yes, that could be the reason.
@@ExcelMacroMania but the res works!! Thanks you 😀
Hi there, Thanks for amazing video please can you share VBA code for organization chart? Many Thanks
You can find it here: excelmacrobusiness.blogspot.com/2020/06/org-chart-creator.html
Hello I needed his services when I came to create a Google form with images that showed the images with a title Url I need it to show me the pictures inside the cell. Can I do that?
Not sure I understand what you want to do. Does that really have something to do with sending meet invites? or is it something completely different? Because this video is just about sending meet invites from Excel.
How do i put mail in "CC" ? Thanks 🙏🏻
OutInvite.OptionalAttendees = "optional1@outlook.com;optional2@outlook.com"
Can you please help to solve the error "Runtime error '429' ActiveX component cant create object"
Make sure you have added the Outlook library to references and you have properly declared/defined the objects. Check the code is correct here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
Hi, this is something i have been trying to replicate. I tried using the second method from your tutorial. But unable to make it work. can you please help? I keep getting the error "Run-time error. The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available"
Make sure the code is correct, there was a mistake in the video that I corrected later, maybe you didn't watch the whole video. Also, go to your google account and get the exact address of the smtp server (usually is smtp.gmail.com, but can be different). Try with other port (465 vs 25 vs other?). Make sure you have added the right references to the VBA project. There might be some restrictions in your network, etc. You can find the code here: excelmacroclass.blogspot.com/2024/01/send-google-mail-from-excel-vba-macro.html
Very useful.. Thanks a lot Sir I want to confirm the news about vba support from Microsoft is ending soon????
In my opinion those are just rumors. I don't think is ever gonna happen in my and probably also you lifetime 🙂There are so many companies and millions of Excel users who rely on VBA automation. And I am talking of desktop Excel of course, we all know VBA/macros are not supported in the online version.
@ExcelMacroMania Thanks
i am calling same macro in my side giving me error ActiveX component cann't create object
Make sure the code is correct, check it here: excelmacroclass.blogspot.com/2022/05/connect-to-api-excel-vba-macro-part-1.html Also, check if your API requires an API key and you are using the correct URL along a valid API key.
hi dear i have a question how can i add the my special email signature to the macro regards
Here's how you do it for an email. If you want to do add it to the invite just replace OutMail with OutInvite as defined in this video. With OutMail .Display signature = .HTMLBody .To = "recipientemail@outlook.com" .Subject = "email subject" body = "Dear Customer,... bla bla" .HTMLBody = body & signature '.Send 'uncomment to send End With
@@ExcelMacroMania .HTMLBody doesn't show in my macro
@@mohanedalwhaibi576 Make sure you add the Outlook library to references and declare OutMail as MailItem, then when OutMail.HTMLBody would get or set the whole body as HTML.
@@ExcelMacroMania oh thanks bro already solved the problem
i am using an app called mail came in windows 11 how can i send through it regards
I believe Mail in Windows 11 is Outlook as part of Microsoft 365, so you should be able to send an in email from Excel using the method described in the other videos (Send Mail from Excel) or this one if you manage to setup a gmail account there.
hi thanks for all this but how can i use another app than outlook like mail in windows 11
You can also send via Gmail, check out this other video: ruclips.net/video/T7G_EnKyS_0/видео.htmlsi=cH50psvDsP29qUZz
@@ExcelMacroMania Thanks very much i fixed that ❤
you are amazing!
Hi, I've tried running the code multiple times without the Display cuz I want to try sending it automatically but it's not sending. Sometimes it goes automatically to outbox. Why is that?
That's the normal behavior of Outlook. Emails go first to the Outbox folder and then to the Sent folder. It usually happens very fast and we do not even realize about it. As you are not automating with Excel, sending many emails takes longer and they may stay longer in the Outbox folder, but eventually should be all sent out. If that does not happen, you could add a short delay within the For loop to let your computer process everything. You can use the function Wait for that.
Simple and easy to follow video; helped me a great deal after failing miserably with PowerAutomate. Question - Can I select a specific Row and attach this in the email that goes out?
Yes, that's actually covered in this other video, check it out: ruclips.net/video/XpGzWvSr9j4/видео.htmlsi=XBwBw9wIQF82hOaB
how can i do this to assign a task?
Dim OutApp As Outlook.Application, NewTask As Outlook.TaskItem Set OutApp = New Outlook.Application Set NewTask = OutApp CreateItem(olTaskItem) NewTask.Display
You're videos are BY FAR the best, most helpful VBA tutorials I have ever seen. You are a great teacher, and thank you so much for everything you've made!
Thanks a lot for your feedback. It means a lot! 🙂
Hey, thank you very much, when I run the macro it starts loading and after 10-15 secs, it shows “Run-time error” Server execution failed. If anyone can help?
Uhmm.. that's weird. Please check the code here and make sure is correct: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
Great video! Im trying to recreate this with cells that are NOT UNDERLINED in literally the same situation as you and im running into an error and the debugging points me to the last statement after "Next r" Runtime error '1004' Method 'Range' of object '_Global' failed Here is my code: Sub GroupCellsSameFormat() Dim r As Integer, lr As Integer, startr As Integer, endr As Integer lr = Cells(Rows.Count, "B").End(xlUp).Row For r = 5 To lr If Range("B" & r).Font.Underline = False Then If startr = 0 Then startr = r + 1 Else endr = r - 1 Range("B" & startr & ":B" & endr).Rows.Group startr = r + 1 End If End If Next r Range("B" & startr & ":B" & lr).Rows.Group End Sub Please let me know what i should do :)
I think the problem is with Range...Font.Underline = False, use instead the following: If Range....Font.Underline = xlUnderlineStyleNone Then ...
Is this possible to copy same data in new workbook instead of same sheets. Please help
Yes, you can incorporate the macro in this other video: ruclips.net/video/9GgjoF7eJsg/видео.htmlsi=AU2yu23aeiOFQRJk
@@ExcelMacroMania thank you for replying my msg 😀 Let's understand . I have one excel file which has contents 5 sheets with different sheets name , and each sheet have one same column name is state, and total 20 states data each sheet in some sheets have more raw and some sheets have less, and i do filter state data in all sheets and paste in new workbook and save as state name file , and i do same work for every state, this consuming my lots of time . Please help to make a video on this topic.please help🙏
@@AmanKumar-eb5vd It is difficult to understand exactly what you mean. Maybe you send me the file and I will try to have a look: xlsgsdata@gmail.com
Thank you for this great video
You're a gem of a kind❤
what is IFQ in URL ??? doing? what does it do ? ITs constants or seperate for us ?
ifq is just a parameter of the google url that indicates the following parameters belong to the various entries in the google form. You do not need to change it or do anything with it, just keep it in the main url that concatenates with each of the entries in the form as explained here: excelmacrofun.blogspot.com/2022/09/excel-data-export-to-google-sheets.html
Thank you so much
Thank you very much, this is really Very helpful.
Could please explain how to add an attachment to gmail when the attachment is defined by a variable and not as hard coded path/filename in VBA. Example .Addattachment "c:\data\testmail.xlsx". I'm looking for something along the lines of .Addattachment path & fname & ".pdf" where both the path and fname are variables defined as strings in the macro. Thank you in advance for your help
CDOmail.Attachment "c:\data\testmail.xlsx"
@@ExcelMacroMania Thank you for your kind and timely answer
This playlist is amazing, thank you! Any chance you could do a video expanding more on how to do the "2nd level authentication" with a token and encoding?
Thanks! And yes, I will definitely do it but I don't be able to work on it until after the summer.
Thank you so much 😭
Thank you :)
Looks cool. Definitely will watch again to understand the code.
What if I want to copy it to a new excel file? Kindly help
Use Workbooks.Add to create a new workbook and set it to a variable that you can use later. You probably need at least 2 variables, wb1 and wb2. Then refer each accordingly. Check this video for example: ruclips.net/video/9GgjoF7eJsg/видео.htmlsi=hT9QfVcFdiePXNd3 Also, you can probably check some videos of the workbook object to learn that well: ruclips.net/video/rNqaL-JgBxU/видео.htmlsi=Pn0cWJ6FpGruXbjy ruclips.net/video/oTzY44uou1Q/видео.htmlsi=bAk0D9ZPcoZyAQm1
hi, thanks for sharing, but if i want to cut duplicates in another worksheet?
Reference the target worksheet with Sheets("targetSheet").... for example. See more about the worksheet object here: ruclips.net/video/vhPnRQhODBw/видео.htmlsi=oVc2D11DP3uTo6A2
Excellent technique!
Hey, can AI comvert vba code to sheets style??
I wouldn't call it AI, but there are indeed some programs out there that convert Excel macros to sheets. I haven't used it though because I prefer to code it myself. I doubt they are too accurate, is not as easy as a simple "translation", but you may give it a try. See for example here: workspace.google.com/marketplace/app/macro_converter/383201976440
Imagine we got an xslm file that has a macro somewhere that made all the workbook have an 'Calulcate model' option when we right click on the rows. How can we identify where is it coming from? I even have it on empty new workbooks, it s super persistent.
when I try to click it it says that macro could not run due to some error
@@madalinmd1119 You might have installed some add-in before and is not available anymore.... maybe. Try to locate the add-in in the add-ins folder and/or remove it if it bothers you. If the cell menu still pops-up, you can also delete it targeting the name as follows: CommandBars("Cell").Controls("Calculate model").Delete
Great... It worked smoothly... Thanks a lot sir
Thank you for the Video!! Really helpful and much appreciated ❤ Is there any way I can define from email ID please
What exactly do you want to define? When you say email ID, do you mean email address?
I have more than one email address in my outlook application. May I know the code to choose ‘from’ email ID .
@@nazimkm OutMail.SendUsingAccount = OutApp.Session.Accounts("youremail@outlook.com")
@@ExcelMacroMania I tried this, but did not work. Where exactly I should add this code? Also, Can you tell us how to add ‘from’ for each email separately from excel
@@nazimkm You need to put it inside the loop, at the end, and instead of the Send function. You need to define OutApp and OutMail before. Check how to define those in this post: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html There is no "From" method, you need to use SendUsingAccount with the corresponding email account as explained earlier. Good luck!
Thank you! What if you want to put the differences on another tab instead of highlighting them?
That's covered in some of the videos of the "Compare Everything in Excel" mini-series. Check out the first one here: ruclips.net/video/oTzY44uou1Q/видео.htmlsi=rM3WAX0ew6q_hpTn But I think that's specifically covered in 2.1 or 2.2, see here: ruclips.net/video/jyZHOIbHtv8/видео.html ruclips.net/video/T4QMCoFlVdc/видео.html
I have Outlook, but Microsoft Outlook 16.0 Object Library isn't appearing in my references. How can I fix that?
And you also don't have 15.0 or 14.0? In such case, you will need to either reinstall Office package with everything or try to find just that library online. If you bought a license, check with Microsoft's support.
@@ExcelMacroMania I didn't have a license. I have it now, thanks.