Send Multiple Emails From Excel
HTML-код
- Опубликовано: 28 май 2024
- Excel macro/VBA code available in this post: excelmacroclass.blogspot.com/...
In a previous video, we saw how to send an email from Excel (via Outlook) using VBA macros. We could add as many recipients as needed separated by a semi-colon when sending just one email. However, if we want to send separate emails to each recipient, we need to loop through the list of recipient emails and send an email to each of them individually. In this video, we see how to send multiple emails to recipients listed in a worksheet in Excel. We can choose to send the same or a different subject, body, or attachments to each recipient.
You can find many other macro examples and Excel VBA learning materials (including the Excel VBA Guide for Beginners) in the blog under the link: excelmacroclass.blogspot.com
And yet, if you want more, you can find various Excel applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun.blogspot.com/)
Excel Macro Business (excelmacrobusiness.blogspot.com/)
Excel Macro Sports (excelmacrosports.blogspot.com/)
You just saved me an hour of work every month for the foreseeable future. Thank you!
very good and helpful video! thank you!
Very useful, thank you for the clear explanation!
great video, thanks for sharing!
Man this is fantastic, it is really useful method.
Thank you for your efforts and keep up.
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 😊
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
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
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)
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.
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: ruclips.net/video/t4916enC11I/видео.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.
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)
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"
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: ruclips.net/video/jHd3UI4hi8Y/видео.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.
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: ruclips.net/video/Ifc2I6wmWIk/видео.htmlsi=ZxSu-2k6Go4r9dcd
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.
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
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.
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!
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 & ...
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: ruclips.net/video/4-YBIPL1gsc/видео.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 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
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.
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!
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
@@user-xv7rb5kw2g OutMail.HTMLBody = ""
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
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 (;)
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 ...
I am really struggling to attach files from my pc (pdf images) to the cells. Any help?
Assuming the pdf file you want to attach is in your local drive you just need to do as below (it's also covered in the video):
Dim attachment_FileNameAndLocalPath As String
attachment_FileNameAndLocalPath = "C:\Users\username\Documents\myattachment.pdf" 'replace with yours
OutMail.Attachments.Add attachment_FileNameAndLocalPath
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.
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
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: ruclips.net/video/Ifc2I6wmWIk/видео.htmlsi=ZxSu-2k6Go4r9dcd
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
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 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
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.
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. ;-)
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
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.