You're a gem of a kind❤

Very useful, thank you for the clear explanation!

Man this is fantastic, it is really useful method. Thank you for your efforts and keep up.

great video, thanks for sharing!

Thank you very much, this is really Very helpful.

You just saved me an hour of work every month for the foreseeable future. Thank you!

very good and helpful video! thank you!

Works perfectly. Thx. Is it possile to insert the signature stored in outlook?

@ExcelMacroMania

8 күн бұрын

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

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.

@ExcelMacroMania

8 ай бұрын

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

@rial.3912

8 ай бұрын

@@ExcelMacroMania thank you so much. I will try this one 😊

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?

@ExcelMacroMania

20 сағат бұрын

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

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?

@ExcelMacroMania

6 ай бұрын

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

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

@ExcelMacroMania

3 ай бұрын

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... 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?

@ExcelMacroMania

6 ай бұрын

OutMail.SendUsingAccount = OutApp.Session.Accounts("yourotheraccount@outlook.com")

@andrewnessim3675

6 ай бұрын

@@ExcelMacroMania Hi, where do I paste this within the code please?

@ExcelMacroMania

6 ай бұрын

@@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

@ExcelMacroMania

10 ай бұрын

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, 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!

@ExcelMacroMania

10 ай бұрын

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: kzread.info/dash/bejne/pmhtk5ienqaXYaw.htmlsi=PBXZRB2zqDZItcr2

@ExcelMacroMania

10 ай бұрын

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

@ExcelMacroMania

7 ай бұрын

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

@ExcelMacroMania

Ай бұрын

What exactly do you want to define? When you say email ID, do you mean email address?

@nazimkm

Ай бұрын

I have more than one email address in my outlook application. May I know the code to choose ‘from’ email ID .

@ExcelMacroMania

Ай бұрын

@@nazimkm OutMail.SendUsingAccount = OutApp.Session.Accounts("youremail@outlook.com")

@nazimkm

Ай бұрын

@@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

@ExcelMacroMania

Ай бұрын

@@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!

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

@ExcelMacroMania

7 ай бұрын

You can check how to add buttons to each row in this other video: kzread.info/dash/bejne/nHyYlbeCZMvPaLw.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

7 ай бұрын

Thanks. Can i also change sender email. I want to send from companies email instead of personal.

@ExcelMacroMania

7 ай бұрын

@@mariamina4340 OutMail.SendUsingAccount = OutApp.Session.Accounts("emailaddress@yourdomain.com")

@mariamina4340

7 ай бұрын

@@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

@ExcelMacroMania

7 ай бұрын

@@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.

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?

@ExcelMacroMania

11 ай бұрын

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")

@lorenzc811

11 ай бұрын

@@ExcelMacroMania thanks for your instructions nailed it the first time i did it. 💪🏻

@ExcelMacroMania

11 ай бұрын

@@lorenzc811 Glad to know it worked out!

@lorenzc811

11 ай бұрын

@@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?🤔

@ExcelMacroMania

11 ай бұрын

@@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

Can we select multiple range for bodymain and is there way i can add table to the mail?

@ExcelMacroMania

2 ай бұрын

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: kzread.info/dash/bejne/e5qXlKtvp9C9ec4.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!

@ExcelMacroMania

7 ай бұрын

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), ";")

@younghussla3032

7 ай бұрын

@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!

@ExcelMacroMania

7 ай бұрын

@@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

@ExcelMacroMania

5 ай бұрын

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")

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

@ExcelMacroMania

10 ай бұрын

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.

@idealfaizan7804

10 ай бұрын

@@ExcelMacroMania thank you Yes. You are correct. Already replied you on email about my problem.

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 ?

@ExcelMacroMania

11 ай бұрын

You need to use HTMLBody and add HTML with a tags along with href. For example ...

@user-xv7rb5kw2g

3 ай бұрын

@@ExcelMacroMania also interested how to do this

@ExcelMacroMania

3 ай бұрын

@@user-xv7rb5kw2g OutMail.HTMLBody = ""

hi thanks for all this but how can i use another app than outlook like mail in windows 11

@ExcelMacroMania

19 күн бұрын

You can also send via Gmail, check out this other video: kzread.info/dash/bejne/hmt7waene9y5j5M.htmlsi=cH50psvDsP29qUZz

@mohanedalwhaibi576

19 күн бұрын

@@ExcelMacroMania Thanks very much i fixed that ❤

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?

@ExcelMacroMania

2 ай бұрын

Yes, you can do that inserting the body as HTML and adding tags with hyperlinks. For example: OutMail.HTMLBody = "Follow Link"

hello, what if your email is repeated, isit possible to merge 2 email together including the data and not create 2 email at once.

@ExcelMacroMania

8 ай бұрын

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 :)

@ExcelMacroMania

9 ай бұрын

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: kzread.info/dash/bejne/ZmGNpKuJfJTNo8Y.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.

@notadhominem4743

9 ай бұрын

Thanks a lot thats perfect @@ExcelMacroMania

How would I code it to allow for my existing email signature to display?

@ExcelMacroMania

9 ай бұрын

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

Can this macro be used after modification for Gmail ? please let me know

@ExcelMacroMania

7 ай бұрын

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?

@ExcelMacroMania

3 ай бұрын

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

@CiaraHarlin

3 ай бұрын

@@ExcelMacroManiathanks v much!

And how to add the hyperlink to the body of the e-mail, same hyperlink from specific cell or different for each recipient

@ExcelMacroMania

3 ай бұрын

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 ...

Sir can we add multiple emails in "TO" Column.. Can you please explain that

@ExcelMacroMania

11 ай бұрын

You can add as many emails as you want in the "TO" column, but they must be separated with a semi-colon (;)

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?

@ExcelMacroMania

Ай бұрын

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

Will it work for default signature?

@ExcelMacroMania

2 ай бұрын

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

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?

@ExcelMacroMania

10 ай бұрын

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"

@ExcelMacroMania

9 күн бұрын

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

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

@ExcelMacroMania

4 ай бұрын

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

@emilteh3448

4 ай бұрын

@@ExcelMacroMania thank you for quick reply. I just tried but it show complie error:End if without block if

@ExcelMacroMania

4 ай бұрын

@@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 can we bypass the external recipients message that we get in outlook via macro?

@ExcelMacroMania

2 ай бұрын

You need to setup that in Outlook under File->Options->Mail->MailTips

I am really struggling to attach files from my pc (pdf images) to the cells. Any help?

@ExcelMacroMania

4 ай бұрын

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

how to add picture in mail body within same code?

@ExcelMacroMania

2 ай бұрын

Add email body as HTML and embed an image in the HTML code, for example as follows: bodypic = "" OutMail.HTMLBody = body & bodypic

In subject we need table how to insert

@ExcelMacroMania

2 ай бұрын

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: kzread.info/dash/bejne/e5qXlKtvp9C9ec4.htmlsi=ZxSu-2k6Go4r9dcd

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?

@ExcelMacroMania

20 күн бұрын

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.

I am trying but run time error and showing automation error & exception occurred Help for resolve this error

@ExcelMacroMania

6 ай бұрын

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.

I am getting a pop up complie error user defined type not defined Can anyone let me know how to fix it

@ExcelMacroMania

3 ай бұрын

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

@ExcelMacroMania

3 ай бұрын

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

@priyakalled745

6 күн бұрын

@@ExcelMacroMania

9:31 compile error User- defined type not defined This error is coming

@ExcelMacroMania

9 күн бұрын

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

I think there are two small mistakes. .Subject = Range("D" & r).Value bodyMain = Range("E" & r).Value ;-)

@ExcelMacroMania

5 ай бұрын

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. ;-)

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

@ExcelMacroMania

9 ай бұрын

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.

Facing path file error

@ExcelMacroMania

4 ай бұрын

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.

How can you center the text through VBA?

@ExcelMacroMania

5 ай бұрын

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

"User-defined type not defined" error

@ExcelMacroMania

11 ай бұрын

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

7 ай бұрын

If the next column is H and row r: Range("H" & r).Value = "Message sent successfully"

@himanshu23457

7 ай бұрын

@@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

@ExcelMacroMania

7 ай бұрын

@@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.