You need a loop through rows with visible cells only (skip entry.Row = 1 if you have headers), and put the send email macro inside the loop: For Each entry In ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows If entry.Row>1 Then email = Cells(entry.Row, 2).Value 'here the other macro ------------------------------------------------------------------- End If Next entry
hi, thank you for this wonderful tutorial, i just want to know how about sending this email from other email address exept my personal mail, like groupmailbox? do i have to sign in to my groupmail box to send this? or is there any other way to lessen efforts? your response is much appreciated. thank you!
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
Thank you for the tutorial, the code worked flawlessly. I have an issue where our organisation added an extra step before sending emails that is "Azure information protection" requiring us to select a leabel before proceeding. Is there a way to code it so that it selects one of them for each row ? I understand that i may somehow get a label ID and define "sensitivity" but im clueless.
I don't think you can change that with VBA. But indeed there is a "Sensitivity" property that allows to set the sensitivity of the email as normal, personal, confidential, or private, however, I don't believe is linked to your company's labels.
You just need to refer to the row where you have the location of the attachment, so instead of referencing a single cell, you target each row as follows supposing you have the attachment location in column F and loop by row with r. OutMail.Attachments.Add Range("F" & r).Value
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
Hi Thanks for the code. Can i add a button next to each recipient in order to send email when needed? Not always need to send all emails to all recipients.thanks again
You can check how to add buttons to each row in this other video: ua-cam.com/video/jHd3UI4hi8Y/v-deo.htmlsi=Hnoh6cIBz6PcrMWd The video shows a first macro to add the buttons linked to each row and run a second macro when clicked. You just need to replace the second macro with the "sending email" macro instead of the "delete row" covered in that other video. Good luck!
@@mariamina4340 Check the code in this post: excelmacroclass.blogspot.com/2022/02/add-buttons-to-delete-rows.html Just rename the second macro and replace the last line (Rows.Delete...) with the send email macro. The variable target is the recipients email, so just change the column P to whatever column is your email, and the other properties (subject, body, etc) for that row.
Hi, thanks for the video! Is there a way to make it so the emails go into the drafts folder in outlook or into a folder on desktop? I need to review the emails before sending them. Thanks!
Well, you can use "Display" instead of "Send" as I did in the video to see the email and then click "Send". You can also use "Save" instead of "Send" or "Display" and it will save it to the folder so that you can review the email or emails. OutMail.Display or OutMail.Save or OutMail.SaveAs = "xxxx" ... to specify the location (saves as .msg in the given location within your drive I believe)
Hello, thank you for your help! I have emails with duplicate subjects where I only want to generate 1 email for each subject. Can you tell me how I can alter the code to help with that?
I think you don't need to alter the code, just the arrangement in the worksheet. Put all emails separated by semi-colon together for the email and subject you want to send. Or, if you mean to send various emails to various recipients but with the same subject refer to always the same cell, for example: OutMail.Subject = Range("D2").Value
@@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!
Is it possible if the signature using currently email signature that has been set, because the signature has a picture and gif inside? And, How to do that? Kindly need your tips Thank you
Thanks for the excellent tutorial. If my table contains, for example, 50 recipients, but currently through filtering it is required to send to only 8 recipients scattered in different and non-consecutive rows in the table (the other rows "disappear" temporarily in the filtering), is the loop you presented still correct to use? Will the Ir automatically skip those lines that were hidden in the filter or is another setting required?
You can use the same loop and a condition to check if the row is hidden or use a for each loop along special cells: For Each sheetRow In ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows ... Next sheetRow
I tried to run the code and got a message something like time error 1004. I checked the debug and the row: Lastrow= Cells (Rows.Count, “P”) .End(xlUp) .Row Has been flashed. What does it mean?
@@איוויקסשירותיהנדסהפיקוחוייעוץ May you declared "Lastrow" as Integer and your sheet has more than 32000 rows, so you need to declare it as Long instead.
Thanks. Now, if the last row is not known since I’m sorting the “To” column before clicking the SEND button, do I still use the command: For r=2 to last row? Do I still use: LastRow = Cells(Rows.Count, "B").End(xlUp).Row? You suggest me to add in the loop a command for searching a visible cells. 1. I’m confused 2. Is your suggestion replace the original command or it comes in addition? 3. If in addition where do I position the new command? In the loop or not? What is the exact command?
@@איוויקסשירותיהנדסהפיקוחוייעוץ You can put it outside the loop if you filter once and then loop through each of the rows that have been filter. You can get the last row after filtering in many different ways,... one could be as follows (there is probably and easier way but this is the first thing that came now to my mind): LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).Row
My issues is very simple and hopefully easy to address... I am just trying to send one email to multiple people however, when I type in an email then use : to seperate, it doesnt work. I get an error. It says Outlook doesnt recognize one or more names. I have two test emails Im using and when I only use one it works. Both of them work on their own but together it doenst work. Please help!
If you refer to scheduling the submission, here's an example to send the email at 13:00 today (email stays in the Outbox until delivery): OutMail.DeferredDeliveryTime = "5/22/2023 13:00" You can also use DateAdd function to send after a number of seconds, minutes, hours, days,..., this other example sends after 10 minutes from now (use "s" for seconds, "n" for minutes, "h" for hours, "d" for days, "m" for months) OutMail.DeferredDeliveryTime = DateAdd("n", 10, Now)
Hi thank you for the video. I am trying to send to multiple recipients but I do not have the emails in a single cell, they're distributed down my column B. But I also do not want to select the entire column B as I have multiple chunks of emails for different groups. How could I write a code to allow for this? Thanks!
You should probably concatenate the values in those cells with one of these two methods (first is simple loop, but second is better and shorter) for an example of have your emails in cells from B4 to B8. Dim recipients As String For Each cell In Range("B4:B8") recipients = recipients & cell.Value & ";" Next or simply recipients = Join(Application.Transpose(Range("B4:B8").Value), ";")
@ExcelMacroMania okay this worked! I'm not sure if you've encountered this but at first, all the emails populate in Outlook as expected, separated by semicolons (which is what we wanted), but then a few seconds later, outlook seems to clear a couple of the emails out when it does its profile search (which shows you the little dot beside their names like active/away status). Very odd. Wondering what's causing that. All emails are valid and the issue does not happen when manually copying and pasting the email list into Outlook. Thanks!
@@younghussla3032 Glad it worked! And the other thing you mentioned never happened to me, odd indeed. It may be due to settings, policies, etc? I don't really know and cannot think of any workaround.
Sure, you can. If you want to add the range as a table in the email body you need to convert the table first to HTML and then use OutMail.HTMLBody instead of .Body. Here's how you convert a range to HTML table: ua-cam.com/video/Ifc2I6wmWIk/v-deo.htmlsi=ZxSu-2k6Go4r9dcd
Hi, many thanks for the tutorial! 1. I have a problem when I run the code, it says to debug the line .display ? 2. May I know how I can add the big send button on the sheet to run the code? Thank you!
1. I don't why that happens, maybe it has something to do with your office package, outlook and excel not in sync (different versions or something like that). I am just thinking out loud. I wonder if it sends the email (if it cannot display probably cannot send). Please double-check the code, you can find it in my blog: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html 2. This video shows how to add a button to the sheet and assign a macro to it: ua-cam.com/video/t4916enC11I/v-deo.htmlsi=PBXZRB2zqDZItcr2
A small correction on point 2 (add button). That video shows you how to add it programatically with VBA, but you probably just want to add one button manually. In that case, you go to the Developer tab in the ribbon (if you don't have it, right-click to customize the ribbon and add the developer tab), and then you go to insert, under controls, and insert a command button form control - it will prompt a dialog to select the macro that you want to run when clicking the button. If it doesn't prompt, you simply right-click the button and choose to assign macro.
Of course it is. Add the location to each attachment in each row (in column F in this example) and then refer to that cell while looping through rows as you target each individual email or subject, for example as shown below: OutMail.Attachments.Add Range("F" & r).Value
Hi There, thank you for your work and providing free learning resources. however i want to point out an error in the multiple email sending macro. In this macro, the email is taking the first attachment only. and not as per the sheet. Can you please let us know how to fix this issue. Link is below Excel Macro Class: Send Multiple Emails From Excel VBA Macro Thanks & Regards, Faizan
Thanks for highlighting your concern. I am afraid to tell you that what you mention is NOT an error in the macro at all. As explained in the video, if you want to send different attachments (instead of always the same attachmend, same subject, etc), you need to refer to the cell in the sheet with that information. For example, if you have the attachment file path in column F and you loop through rows incrementing the value of a row counter r, you would use the following: OutMail.Attachments.Add Range("F" & r).Value That's explained in the video around the minute 8, please check again if it's not clear yet.
License information for this component not found. You don't have an appropriate license to use this functionality in the design environment This error is coming while running the macro
Is there a way to have the range() come up as a hyperlink in the email? Say the cell values in column D are URLs, so I do range(“D” & r).value and I run the macro, I want the URL to appear as a clickable link in the email . Is that possible?
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.
If you mean to add a hyperlink to the email body, you need to use HTMLBody as follows: You need to use OutMail.HTMLBody = ... and add HTML with 'a' tags along with 'href'. For example ...
Hi, that was so helpful but I have 2 questions. When we run the program I don’t want to push the send button for each and every email in outlook, how can I do that? And I tested it all my emails are gone to spam box, how can I stop it? Thanks a lot :)
1. You don't need to use the button, there are many other ways to run a macro. Have a look at this other video to see 10 different ways to run a macro: ua-cam.com/video/4-YBIPL1gsc/v-deo.htmlsi=azqSsZJpGxSrhzdv 2. This method is good to email your team members, work colleagues, friends, etc. When it comes to random email recipients, this or any other method/system will probably be block by anti-spam, because that's actually what you are trying to do. Spam filters have got very smart and can detect fraudulent emails. Your email address may have already been added to anti-spam DB, so any other way of sending multiple emails (or even just one individual email to a random person, although depends on the email provider/domain of that recipient), will eventually be blocked/labeled as spam.
Great video. But, unfortunately, when I click the button, nothing happens. I don't get any errors, just nothing. When I open the VBA editor, and press play nothing happens either. what should I do?
Do you have a "Send" "Display" function in the code? Did you add the Outlook library to the references? Or, maybe you have some restrictions to run macros or send email in your system (policies, etc). Check the code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
Yes, you can do something like this: 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
If you add your gmail account to Outlook, you can still use this method to send an email from google account adding this to the code: OutMail.SendUsingAccount = OutApp.Session.Accounts("youremail@gmail.com") If you cannot or don't want to add gmail to Outlook there are a couple of other methods. I will cover everything in another video soon.
how can I change one word in my email to a hyperlink ? I tried to create a seperate cell and concatenate it but it didnt translate to outlook. Is there a way to do it in VBA ?
You need to create item from template when you define the mail item (OutMail) referring to the email template (usually a .oft file rather than .msg that you have) in your system - see an example below: Set OutMail = OutApp.CreateItemFromTemplate("C:\Users\Administrator\Templates\template1.oft")
Hey New Subscriber here, this is perfect example of what I am looking for. My only problem is that how can I use an outlook template as the body of email?
You need to create item from template when you define the mail item (OutMail) referring to the email template (.oft file) in your system - see an example below: Set OutMail = OutApp.CreateItemFromTemplate("C:\Users\Administrator\Templates\template1.oft")
@@ExcelMacroManiaHi sir, is there any way I can add details to the template using macro? For instance, how can I make a center header added in the template?🤔
@@lorenzc811 You can edit the template body first saving the body to a string variable and then adding what you want with OutMail.Body or OutMail.HTMLBody (it's a 2-step process). To add your centered header you add these 2 lines: 1. myTemplateBody = OutMail.Body or OutMail.HTMLBody 2. OutMail.HTMLBody = "My header" & myTemplateBody
When you say "merge", you mean to merge the email body? If so, you can concatenate as much text (or HTML) as you want and send it to the same email recipient. For example: body1 = "This is the first paragraph or block of text in the email body" body2 = "Anything else here...." OutMail.Body = body1 & vbLf & vbLf & body2 & vbLf & vbLf & ...
great & useful video but when i click run its showing old outlook & asking me to add account , i am now using new version of the same , can you please help me get out of this
Thanks for your reply I am using new version and I also have subscription of the same can you help me when I click on send mail its showings add account but it's not working
I tried this solution but i think i got restrict. This message appear " This message couldn't be delivered because the sending email address was not recognized as a valid sender"
If I want to add a rules like if there is “YES”word maintain in certain cell that email will send. If there is “No”word maintain in certain cell that email will not send out. For example row one I want to send out so I maintain Yes in certain cell. Row two and row there I don’t want to send out so I maintain No in certain cell. Please advise. Appreciate for your help
Let's say you have that info in column M, so you need to add inside the loop but before the Set OutMail.. and With OutMail, the following condition: If Range("M" & r).Value = "Yes" Then Set OutMail... With OutMail etc.... End If
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
What Should we do if we don't Attach an attachments to every Mail ? .Attachments.Add Range("G" & r).Value If Not attach "" Then .Attachments.Add (attach) End If
I guess you mean to keep your default signature that is added automatically when creating a new email. That signature is part of the body, and is replaced with whatever you put in .Body = "bla bla...". So the trick is to first display and get the HTMLBody, which is just the default signature, and then concatenate that when adding the body, but 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
How do I send multiple pdf attachments to one person. All how to send different attachments (monthly inovices) to different customers. Each customer email is emailed with one click?
Just repeat that line as many times as needed. For example: With OutMail .Attachments.Add attachment1 .Attachments.Add attachment2 .Attachments.Add attachment3 ... End With
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
That sounds like your computer is running the code faster than emails are processed.... maybe you can try adding DoEvents before the loop or put a 1 second delay inside the For loop with Application.Wait.
When I am clicking on F5 then this is turning up in yellow color Sub SendMultipleEmails() and Compile Error: User-defined type not defined. Please solve
That's probably because you did not define some variable or function while writing the code. Have a look and compare with the code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
I believe you are mistyping some variable or function. Check the correct code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
@@ExcelMacroMania Thankyou the code is working, however can you tell how can I use it through outlook web instead of application because my administrator only has allowed my outlook account to be used through the web. Kindly Help
Thanks for the feedback, but you are wrong, it is NOT a mistake. I explained in the video that you can send various emails with the same subject and body, in which case we target D2 and E2 only, or if you want to send a different subject and body for each recipient then you target the respective row with Range("D" & r), etc. ;-)
You mean in body? If you want to add the range as a table in the email body you need to convert the table first to HTML and then use OutMail.HTMLBody instead of .Body. Here's how you convert a range to HTML table: ua-cam.com/video/Ifc2I6wmWIk/v-deo.htmlsi=ZxSu-2k6Go4r9dcd
Yes, but you need to display first to get the signature as HTML and then concatenate as follows: 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
I sent my multiple emails from concept shown here but with different code lines. But my each and every messages is being delivered in spam folder with warning message" This message seems dangerous .Similar messages were used to steal people's personal information. Avoid clicking links, downloading attachments or replying with personal information" although I have got outlook account from my side , on the other side all recipent emails is in Gmail. Please help me out . Thankyou
I feel your concern, but I don't think it has nothing to do with the "method" used to send the emails, and probably rather with the email account used. It does not really matter if you use a macro to send it or do it manually. I guess the problem comes when sending too many emails to the same recipient, or using an email account that is already "labeled" as spammer account, and such things, I guess.
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
Make sure you have Outlook configured, add the Outlook library to Excel VBA project references, and the code is correct - check the code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
@@ExcelMacroMania it worked, but I want to know if I use @CC it will allow me to add email addresses to CC of that email, correct? and how can I edit the fonts of the body of the email....
@@nayanjeetsinghdhadwal6142 Glad it worked. OutMail.CC will add the emails as carbon copy, yes. To update the font use HTMLBody along html with a given font or using CSS.
@@ExcelMacroMania didn’t understand any thing about the HTML and css, like you mentioned the body of the email in a column, how can we change the font or background colour of the text or change it into bold italic underline ?
@@nayanjeetsinghdhadwal6142 You need to learn the basics of HTML and CSS, please check online. Then you use HTMLbody to format the email body as you wish. For example, if you want to change the font size of a paragraph in the email you could do something like this: OutMail.HTMLbody = "This is a bigger paragraph."
You mean path of the attachment, right? Do you have the attachment in your local drive? or may in OneDrive? It should start with C:\... for example C:\Users\username\Documents\..., so check that, for example with ActiveWorkbook.FullName. If it starts with .... it means is in OneDrive and you'll need to convert to local path.
Sounds like you've used some other function or type, maybe you've just miss-typed some variable or object and VBA cannot find it. Please check the code in the blog article, that should work well: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
Sir ,,When the mail is sent, the message 'Mail sent successfully' should automatically appear in the next column of the attachment Which code needs to be entered for this
@@ExcelMacroMania Sir, if I delete the emails id from the To column (B), then the message sent successfully will also be automatically deleted from the H column. What will be the code for this
@@himanshu23457 But why would you delete the email? The macro I discuss in this video does not delete anything. You keep the list of emails there, and if you want to track which email has been sent, you add that message in column H or any ohter.
You need to do it with HTML and use HTMLBody property. For example, you can put your email body inside a p tag that is aligned to the center as follows: emailBody = "This is a test email message aligned to the center" emailHTMLBody = "" & emailBody & "" OutMail.HTMLBody = emailHTMLBody
great video, thanks for sharing!
Great video - just fyi to other users: this doesn't seem to work on a Mac, we are missing the Outlook Object Library.
You just saved me an hour of work every month for the foreseeable future. Thank you!
Very useful, thank you for the clear explanation!
THANKS!! Very helpful. You have secured my continued employment for the foreseeable future 😄
You're a gem of a kind❤
Man this is fantastic, it is really useful method.
Thank you for your efforts and keep up.
very good and helpful video! thank you!
Thank you very much, this is really Very helpful.
This is very helpful. Thank you so much. Just want to ask how send the email to filtered recepients? Your help is very much appreciated.
You need a loop through rows with visible cells only (skip entry.Row = 1 if you have headers), and put the send email macro inside the loop:
For Each entry In ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows
If entry.Row>1 Then
email = Cells(entry.Row, 2).Value
'here the other macro -------------------------------------------------------------------
End If
Next entry
@@ExcelMacroMania thank you so much. I will try this one 😊
hi, thank you for this wonderful tutorial, i just want to know how about sending this email from other email address exept my personal mail, like groupmailbox? do i have to sign in to my groupmail box to send this? or is there any other way to lessen efforts? your response is much appreciated. thank you!
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
Excellent video!!!!! Following. Question, how do you change the font style and size of the email body?
You will have to use HTMLBody along html/css formatting. You can do it just with html as follows:
OutMail.HTMLBody = ""This is a test email"
Thank you for the tutorial, the code worked flawlessly.
I have an issue where our organisation added an extra step before sending emails that is "Azure information protection" requiring us to select a leabel before proceeding.
Is there a way to code it so that it selects one of them for each row ? I understand that i may somehow get a label ID and define "sensitivity" but im clueless.
I don't think you can change that with VBA. But indeed there is a "Sensitivity" property that allows to set the sensitivity of the email as normal, personal, confidential, or private, however, I don't believe is linked to your company's labels.
Very interesting, thanks for sharing. Looking for this method.. Will try later. However, how to attached different file for each individu?
You just need to refer to the row where you have the location of the attachment, so instead of referencing a single cell, you target each row as follows supposing you have the attachment location in column F and loop by row with r.
OutMail.Attachments.Add Range("F" & r).Value
Nice job! Is it possible to use the signature allready saved in outlook? I have a company-signature with a logo i like to use with my mails
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
Hi Thanks for the code. Can i add a button next to each recipient in order to send email when needed? Not always need to send all emails to all recipients.thanks again
You can check how to add buttons to each row in this other video: ua-cam.com/video/jHd3UI4hi8Y/v-deo.htmlsi=Hnoh6cIBz6PcrMWd
The video shows a first macro to add the buttons linked to each row and run a second macro when clicked. You just need to replace the second macro with the "sending email" macro instead of the "delete row" covered in that other video. Good luck!
Thanks. Can i also change sender email. I want to send from companies email instead of personal.
@@mariamina4340 OutMail.SendUsingAccount = OutApp.Session.Accounts("emailaddress@yourdomain.com")
@@ExcelMacroMania thank you very much. You are really helpful. How should i modify email send code in order to sign to each button an email?thanks
@@mariamina4340 Check the code in this post: excelmacroclass.blogspot.com/2022/02/add-buttons-to-delete-rows.html
Just rename the second macro and replace the last line (Rows.Delete...) with the send email macro. The variable target is the recipients email, so just change the column P to whatever column is your email, and the other properties (subject, body, etc) for that row.
Hi, thanks for the video! Is there a way to make it so the emails go into the drafts folder in outlook or into a folder on desktop? I need to review the emails before sending them. Thanks!
Well, you can use "Display" instead of "Send" as I did in the video to see the email and then click "Send". You can also use "Save" instead of "Send" or "Display" and it will save it to the folder so that you can review the email or emails.
OutMail.Display
or
OutMail.Save
or
OutMail.SaveAs = "xxxx" ... to specify the location (saves as .msg in the given location within your drive I believe)
Hello, thank you for your help! I have emails with duplicate subjects where I only want to generate 1 email for each subject. Can you tell me how I can alter the code to help with that?
I think you don't need to alter the code, just the arrangement in the worksheet. Put all emails separated by semi-colon together for the email and subject you want to send. Or, if you mean to send various emails to various recipients but with the same subject refer to always the same cell, for example: OutMail.Subject = Range("D2").Value
hello thanks for the video I wanna ask,
How I add CC email what lines i need to add?
thanks
OutMail.CC = "emailaddress@domain.com"
(and the same for BCC if needed)
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!
Is it possible if the signature using currently email signature that has been set, because the signature has a picture and gif inside?
And, How to do that?
Kindly need your tips
Thank you
Thanks for the excellent tutorial. If my table contains, for example, 50 recipients, but currently through filtering it is required to send to only 8 recipients scattered in different and non-consecutive rows in the table (the other rows "disappear" temporarily in the filtering), is the loop you presented still correct to use? Will the Ir automatically skip those lines that were hidden in the filter or is another setting required?
You can use the same loop and a condition to check if the row is hidden or use a for each loop along special cells:
For Each sheetRow In ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows
...
Next sheetRow
I tried to run the code and got a message something like time error 1004.
I checked the debug and the row:
Lastrow= Cells (Rows.Count, “P”) .End(xlUp) .Row
Has been flashed.
What does it mean?
@@איוויקסשירותיהנדסהפיקוחוייעוץ May you declared "Lastrow" as Integer and your sheet has more than 32000 rows, so you need to declare it as Long instead.
Thanks.
Now, if the last row is not known since I’m sorting the “To” column before clicking the SEND button, do I still use the command:
For r=2 to last row?
Do I still use:
LastRow = Cells(Rows.Count, "B").End(xlUp).Row?
You suggest me to add in the loop a command for searching a visible cells.
1. I’m confused
2. Is your suggestion replace the original command or it comes in addition?
3. If in addition where do I position the new command? In the loop or not? What is the exact command?
@@איוויקסשירותיהנדסהפיקוחוייעוץ You can put it outside the loop if you filter once and then loop through each of the rows that have been filter. You can get the last row after filtering in many different ways,... one could be as follows (there is probably and easier way but this is the first thing that came now to my mind):
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).Row
My issues is very simple and hopefully easy to address... I am just trying to send one email to multiple people however, when I type in an email then use : to seperate, it doesnt work. I get an error. It says Outlook doesnt recognize one or more names. I have two test emails Im using and when I only use one it works. Both of them work on their own but together it doenst work. Please help!
Hi... this video was super helpful. I do have a question though. Is there a way to have the email come out of a different mailbox?
OutMail.SendUsingAccount = OutApp.Session.Accounts("yourotheraccount@outlook.com")
@@ExcelMacroMania Hi, where do I paste this within the code please?
@@andrewnessim3675 Somewhere after defining OutMail of course, maybe after OutMail.Subject,... .Body, etc, but BEFORE OutMail.Send.
How should I add a code for automatic submission? I want all emails gone when I run.
Thank you for your help
If you refer to scheduling the submission, here's an example to send the email at 13:00 today (email stays in the Outbox until delivery):
OutMail.DeferredDeliveryTime = "5/22/2023 13:00"
You can also use DateAdd function to send after a number of seconds, minutes, hours, days,..., this other example sends after 10 minutes from now (use "s" for seconds, "n" for minutes, "h" for hours, "d" for days, "m" for months)
OutMail.DeferredDeliveryTime = DateAdd("n", 10, Now)
I need to attached each attachments to each email ID. How to do it Sir
License information for this component not found. You don't have an appropriate license to use this functionality in the design environment
Pls help
Hi thank you for the video. I am trying to send to multiple recipients but I do not have the emails in a single cell, they're distributed down my column B. But I also do not want to select the entire column B as I have multiple chunks of emails for different groups. How could I write a code to allow for this? Thanks!
You should probably concatenate the values in those cells with one of these two methods (first is simple loop, but second is better and shorter) for an example of have your emails in cells from B4 to B8.
Dim recipients As String
For Each cell In Range("B4:B8")
recipients = recipients & cell.Value & ";"
Next
or simply
recipients = Join(Application.Transpose(Range("B4:B8").Value), ";")
@ExcelMacroMania okay this worked! I'm not sure if you've encountered this but at first, all the emails populate in Outlook as expected, separated by semicolons (which is what we wanted), but then a few seconds later, outlook seems to clear a couple of the emails out when it does its profile search (which shows you the little dot beside their names like active/away status). Very odd. Wondering what's causing that. All emails are valid and the issue does not happen when manually copying and pasting the email list into Outlook. Thanks!
@@younghussla3032 Glad it worked! And the other thing you mentioned never happened to me, odd indeed. It may be due to settings, policies, etc? I don't really know and cannot think of any workaround.
Can we select multiple range for bodymain and is there way i can add table to the mail?
Sure, you can. If you want to add the range as a table in the email body you need to convert the table first to HTML and then use OutMail.HTMLBody instead of .Body. Here's how you convert a range to HTML table: ua-cam.com/video/Ifc2I6wmWIk/v-deo.htmlsi=ZxSu-2k6Go4r9dcd
Hi, many thanks for the tutorial!
1. I have a problem when I run the code, it says to debug the line .display ?
2. May I know how I can add the big send button on the sheet to run the code?
Thank you!
1. I don't why that happens, maybe it has something to do with your office package, outlook and excel not in sync (different versions or something like that). I am just thinking out loud. I wonder if it sends the email (if it cannot display probably cannot send). Please double-check the code, you can find it in my blog: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
2. This video shows how to add a button to the sheet and assign a macro to it: ua-cam.com/video/t4916enC11I/v-deo.htmlsi=PBXZRB2zqDZItcr2
A small correction on point 2 (add button). That video shows you how to add it programatically with VBA, but you probably just want to add one button manually. In that case, you go to the Developer tab in the ribbon (if you don't have it, right-click to customize the ribbon and add the developer tab), and then you go to insert, under controls, and insert a command button form control - it will prompt a dialog to select the macro that you want to run when clicking the button. If it doesn't prompt, you simply right-click the button and choose to assign macro.
Hi, I just want to know is it possible to send multiple email with different attachment to multiple recipients?
Of course it is. Add the location to each attachment in each row (in column F in this example) and then refer to that cell while looping through rows as you target each individual email or subject, for example as shown below:
OutMail.Attachments.Add Range("F" & r).Value
Hi There,
thank you for your work and providing free learning resources. however i want to point out an error in the multiple email sending macro. In this macro, the email is taking the first attachment only. and not as per the sheet.
Can you please let us know how to fix this issue. Link is below
Excel Macro Class: Send Multiple Emails From Excel VBA Macro
Thanks & Regards,
Faizan
Thanks for highlighting your concern. I am afraid to tell you that what you mention is NOT an error in the macro at all. As explained in the video, if you want to send different attachments (instead of always the same attachmend, same subject, etc), you need to refer to the cell in the sheet with that information. For example, if you have the attachment file path in column F and you loop through rows incrementing the value of a row counter r, you would use the following:
OutMail.Attachments.Add Range("F" & r).Value
That's explained in the video around the minute 8, please check again if it's not clear yet.
@@ExcelMacroMania thank you
Yes. You are correct.
Already replied you on email about my problem.
License information for this component not found. You don't have an appropriate license to use this functionality in the design environment
This error is coming while running the macro
Is there a way to have the range() come up as a hyperlink in the email? Say the cell values in column D are URLs, so I do range(“D” & r).value and I run the macro, I want the URL to appear as a clickable link in the email . Is that possible?
Yes, you can do that inserting the body as HTML and adding tags with hyperlinks. For example:
OutMail.HTMLBody = "Follow Link"
Does this also work on gmail?
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.
And how to add the hyperlink to the body of the e-mail, same hyperlink from specific cell or different for each recipient
If you mean to add a hyperlink to the email body, you need to use HTMLBody as follows:
You need to use OutMail.HTMLBody = ... and add HTML with 'a' tags along with 'href'. For example ...
Hi, that was so helpful but I have 2 questions. When we run the program I don’t want to push the send button for each and every email in outlook, how can I do that?
And I tested it all my emails are gone to spam box, how can I stop it?
Thanks a lot :)
1. You don't need to use the button, there are many other ways to run a macro. Have a look at this other video to see 10 different ways to run a macro: ua-cam.com/video/4-YBIPL1gsc/v-deo.htmlsi=azqSsZJpGxSrhzdv
2. This method is good to email your team members, work colleagues, friends, etc. When it comes to random email recipients, this or any other method/system will probably be block by anti-spam, because that's actually what you are trying to do. Spam filters have got very smart and can detect fraudulent emails. Your email address may have already been added to anti-spam DB, so any other way of sending multiple emails (or even just one individual email to a random person, although depends on the email provider/domain of that recipient), will eventually be blocked/labeled as spam.
Thanks a lot thats perfect
@@ExcelMacroMania
How you have created sent button
Great video. But, unfortunately, when I click the button, nothing happens. I don't get any errors, just nothing. When I open the VBA editor, and press play nothing happens either. what should I do?
Do you have a "Send" "Display" function in the code? Did you add the Outlook library to the references? Or, maybe you have some restrictions to run macros or send email in your system (policies, etc). Check the code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
Hello, is it possible to add in your signature automatically within the VBA code?
Yes, you can do something like this:
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
@@ExcelMacroManiathanks v much!
Can this macro be used after modification for Gmail ? please let me know
If you add your gmail account to Outlook, you can still use this method to send an email from google account adding this to the code:
OutMail.SendUsingAccount = OutApp.Session.Accounts("youremail@gmail.com")
If you cannot or don't want to add gmail to Outlook there are a couple of other methods. I will cover everything in another video soon.
how can I change one word in my email to a hyperlink ? I tried to create a seperate cell and concatenate it but it didnt translate to outlook. Is there a way to do it in VBA ?
You need to use HTMLBody and add HTML with a tags along with href. For example ...
@@ExcelMacroMania also interested how to do this
@@Муатдиб-в9ь OutMail.HTMLBody = ""
what if i have a .msg template and want to do ur method, regarding that that msg template file is in html and has multible attachment
You need to create item from template when you define the mail item (OutMail) referring to the email template (usually a .oft file rather than .msg that you have) in your system - see an example below:
Set OutMail = OutApp.CreateItemFromTemplate("C:\Users\Administrator\Templates\template1.oft")
Hey New Subscriber here, this is perfect example of what I am looking for. My only problem is that how can I use an outlook template as the body of email?
You need to create item from template when you define the mail item (OutMail) referring to the email template (.oft file) in your system - see an example below:
Set OutMail = OutApp.CreateItemFromTemplate("C:\Users\Administrator\Templates\template1.oft")
@@ExcelMacroMania thanks for your instructions nailed it the first time i did it. 💪🏻
@@lorenzc811 Glad to know it worked out!
@@ExcelMacroManiaHi sir, is there any way I can add details to the template using macro? For instance, how can I make a center header added in the template?🤔
@@lorenzc811 You can edit the template body first saving the body to a string variable and then adding what you want with OutMail.Body or OutMail.HTMLBody (it's a 2-step process). To add your centered header you add these 2 lines:
1. myTemplateBody = OutMail.Body or OutMail.HTMLBody
2. OutMail.HTMLBody = "My header" & myTemplateBody
hello, what if your email is repeated, isit possible to merge 2 email together including the data and not create 2 email at once.
When you say "merge", you mean to merge the email body? If so, you can concatenate as much text (or HTML) as you want and send it to the same email recipient. For example:
body1 = "This is the first paragraph or block of text in the email body"
body2 = "Anything else here...."
OutMail.Body = body1 & vbLf & vbLf & body2 & vbLf & vbLf & ...
great & useful video but when i click run its showing old outlook & asking me to add account , i am now using new version of the same , can you please help me get out of this
It does not work with online version, maybe that's why.
Thanks for your reply
I am using new version and I also have subscription of the same can you help me when I click on send mail its showings add account but it's not working
a mi no me sale en las referencias el outlook ayuda
I tried this solution but i think i got restrict. This message appear
" This message couldn't be delivered because the sending email address was not recognized as a valid sender"
OMG SAME!!
If I want to add a rules like if there is “YES”word maintain in certain cell that email will send. If there is “No”word maintain in certain cell that email will not send out.
For example row one I want to send out so I maintain Yes in certain cell.
Row two and row there I don’t want to send out so I maintain No in certain cell.
Please advise. Appreciate for your help
Let's say you have that info in column M, so you need to add inside the loop but before the Set OutMail.. and With OutMail, the following condition:
If Range("M" & r).Value = "Yes" Then
Set OutMail...
With OutMail
etc....
End If
@@ExcelMacroMania thank you for quick reply. I just tried but it show complie error:End if without block if
@@emilteh3448 You probably forgot to add the "End If" at the end. Check again the code I wrote above, there's a If statement...and End If at the end.
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
What Should we do if we don't Attach an attachments to every Mail ?
.Attachments.Add Range("G" & r).Value
If Not attach "" Then
.Attachments.Add (attach)
End If
How would I code it to allow for my existing email signature to display?
I guess you mean to keep your default signature that is added automatically when creating a new email. That signature is part of the body, and is replaced with whatever you put in .Body = "bla bla...". So the trick is to first display and get the HTMLBody, which is just the default signature, and then concatenate that when adding the body, but 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
How can we bypass the external recipients message that we get in outlook via macro?
You need to setup that in Outlook under File->Options->Mail->MailTips
How do I send multiple pdf attachments to one person.
All how to send different attachments (monthly inovices) to different customers. Each customer email is emailed with one click?
Just repeat that line as many times as needed. For example:
With OutMail
.Attachments.Add attachment1
.Attachments.Add attachment2
.Attachments.Add attachment3
...
End With
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 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: ua-cam.com/video/T7G_EnKyS_0/v-deo.htmlsi=cH50psvDsP29qUZz
@@ExcelMacroMania Thanks very much i fixed that ❤
Sir can we add multiple emails in "TO" Column.. Can you please explain that
You can add as many emails as you want in the "TO" column, but they must be separated with a semi-colon (;)
I want to send 40 emails in one go to carrier's need help
I am trying but run time error and showing automation error & exception occurred Help for resolve this error
That sounds like your computer is running the code faster than emails are processed.... maybe you can try adding DoEvents before the loop or put a 1 second delay inside the For loop with Application.Wait.
how to add picture in mail body within same code?
Add email body as HTML and embed an image in the HTML code, for example as follows:
bodypic = ""
OutMail.HTMLBody = body & bodypic
When I am clicking on F5 then this is turning up in yellow color Sub SendMultipleEmails() and Compile Error: User-defined type not defined. Please solve
That's probably because you did not define some variable or function while writing the code. Have a look and compare with the code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
@@ExcelMacroMania
I am getting a pop up complie error user defined type not defined
Can anyone let me know how to fix it
Looks like you didn't declare/define some object or variable... check the code here: excelmacroclass.blogspot.com/2021/12/send-email-from-excel.html
It says compile error user defined type not identified. Kidnly help
I believe you are mistyping some variable or function. Check the correct code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
@@ExcelMacroMania Thankyou the code is working, however can you tell how can I use it through outlook web instead of application because my administrator only has allowed my outlook account to be used through the web. Kindly Help
@@maliktaha95 Unfortunately this does not work with the web version of Outlook.
I think there are two small mistakes.
.Subject = Range("D" & r).Value
bodyMain = Range("E" & r).Value
;-)
Thanks for the feedback, but you are wrong, it is NOT a mistake. I explained in the video that you can send various emails with the same subject and body, in which case we target D2 and E2 only, or if you want to send a different subject and body for each recipient then you target the respective row with Range("D" & r), etc. ;-)
In subject we need table how to insert
You mean in body? If you want to add the range as a table in the email body you need to convert the table first to HTML and then use OutMail.HTMLBody instead of .Body. Here's how you convert a range to HTML table: ua-cam.com/video/Ifc2I6wmWIk/v-deo.htmlsi=ZxSu-2k6Go4r9dcd
Will it work for default signature?
Yes, but you need to display first to get the signature as HTML and then concatenate as follows:
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
I sent my multiple emails from concept shown here but with different code lines. But my each and every messages is being delivered in spam folder with warning message" This message seems dangerous .Similar messages were used to steal people's personal information. Avoid clicking links, downloading attachments or replying with personal information" although I have got outlook account from my side , on the other side all recipent emails is in Gmail. Please help me out . Thankyou
I feel your concern, but I don't think it has nothing to do with the "method" used to send the emails, and probably rather with the email account used. It does not really matter if you use a macro to send it or do it manually. I guess the problem comes when sending too many emails to the same recipient, or using an email account that is already "labeled" as spammer account, and such things, I guess.
last row: Ir, is it
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
Hi,
It is not working for me, please help.
Make sure you have Outlook configured, add the Outlook library to Excel VBA project references, and the code is correct - check the code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
@@ExcelMacroMania it worked, but I want to know if I use @CC it will allow me to add email addresses to CC of that email, correct? and how can I edit the fonts of the body of the email....
@@nayanjeetsinghdhadwal6142 Glad it worked. OutMail.CC will add the emails as carbon copy, yes. To update the font use HTMLBody along html with a given font or using CSS.
@@ExcelMacroMania didn’t understand any thing about the HTML and css, like you mentioned the body of the email in a column, how can we change the font or background colour of the text or change it into bold italic underline ?
@@nayanjeetsinghdhadwal6142 You need to learn the basics of HTML and CSS, please check online. Then you use HTMLbody to format the email body as you wish. For example, if you want to change the font size of a paragraph in the email you could do something like this:
OutMail.HTMLbody = "This is a bigger paragraph."
Facing path file error
You mean path of the attachment, right? Do you have the attachment in your local drive? or may in OneDrive? It should start with C:\... for example C:\Users\username\Documents\..., so check that, for example with ActiveWorkbook.FullName. If it starts with .... it means is in OneDrive and you'll need to convert to local path.
"User-defined type not defined" error
Sounds like you've used some other function or type, maybe you've just miss-typed some variable or object and VBA cannot find it. Please check the code in the blog article, that should work well: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
Sir ,,When the mail is sent, the message 'Mail sent successfully' should automatically appear in the next column of the attachment
Which code needs to be entered for this
If the next column is H and row r:
Range("H" & r).Value = "Message sent successfully"
@@ExcelMacroMania Sir, if I delete the emails id from the To column (B), then the message sent successfully will also be automatically deleted from the H column. What will be the code for this
@@himanshu23457 But why would you delete the email? The macro I discuss in this video does not delete anything. You keep the list of emails there, and if you want to track which email has been sent, you add that message in column H or any ohter.
How can you center the text through VBA?
You need to do it with HTML and use HTMLBody property. For example, you can put your email body inside a p tag that is aligned to the center as follows:
emailBody = "This is a test email message aligned to the center"
emailHTMLBody = "" & emailBody & ""
OutMail.HTMLBody = emailHTMLBody