Send Emails to Multiple Persons with Multiple Attachments
HTML-код
- Опубликовано: 29 ноя 2019
- How to send emails to multiple persons with multiple attachments automatically with VBA.
Here's the complete VBA code:
Option Explicit
Sub sendEmailsToMultiplePersonsWithMultipleAttachments()
'NOTE: Because RUclips doesn't allow angular brackets 'NOT GREATER THAN' and 'NOT EQUAL TO' have been inserted in the code
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("Sheet1")
Set OutApp = CreateObject("Outlook.Application")
For Each cell In sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants)
'path/file names are entered in the columns D:M in each row
Set rng = sh.Cells(cell.Row, 1).Range("D1:M1")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) GREATER THAN 0 Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = sh.Cells(cell.Row, 1).Value
.CC = sh.Cells(cell.Row, 2).Value
.Subject = "Details attached as discussed"
.Body = sh.Cells(cell.Row, 3).Value
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell.Value) NOT EQUAL TO "" Then
If Dir(FileCell.Value) NOT EQUAL TO "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
'.Send
.display
End With
Set OutMail = Nothing
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Dear Sir, People like you are the true gems, true guru's. The way you teach really is exceptional, expecting more of such from you.
Man you saved our asses and our souls at the same time....GOD BLESS YOU!!!
Thank you for the script. You saved me 20 minutes a month. It's not a lot of time savings BUT all of my incremental improvements add up.
Keep watching and also share with your friends too.
Thank you sir. I was trying from long time and searched many sites to create a macro to send multiple attachments in a single mail but finally get a solution from you.....thank you very much
Dinesh - you are a legend, thank you so much for being you!
God bless!
This is so useful. Thank you Dinesh!
Dear sir,
Thanks so much for this video 📹 💗.
I learned new things from you.
Thanks so much your time.
Thank you Dinesh, another great video for learning
You're welcome. Please share with your friends also.
Really great 👍..Sir..please continue with such learning videos. For begginers it's more than a magic feeling..😊..Thank you..stay safe tc
So nice of you. Please share the VBA tutorials with your buddies too.
Hello Dinesh, thank you for the video it's easy to understand and follow.
Thanks. Please share with your friends.
This is very helpful! You are a great teacher! How do you customize the email subject?
Glad it was helpful! To customize the email subject you can capture data from the worksheet like for 'To' or 'Body.
Great video! Very Helpful. Thank you :D
Glad it was helpful! Please share the Excel VBA tutorial with your friends too.
Great tutorial, thank you👏
Thanks Dinesh! Awsome video! I am using a similar technic with Lotus Notes and it works perfectly...... Thumbs Up !
You are welcome. Please share with your friends also.
Hi Sir: Can demonstrate the same example using Power Automate? Thanks.
Dear Mr. Takyar,
Thank you very much for your kind information. I am a teacher and I get a lot of emails and I have wanted to send an automatic reply with different attachments to different students. They will be sending me emails with different subject lines or body text to explain what they need.
Thank you very much.
From Paris.
These links will also guide:
www.exceltrainingvideos.com/tag/auto-acknowledge-receipt-of-mail-with-a-unique-tracking-number/
www.exceltrainingvideos.com/automatically-send-email-to-users-queries-in-excel-with-vba/
@@Exceltrainingvideos Sir,
Thank you. However what I want is:-
I get an email from student ABC asking for precise .doc or PDF which is named Paris.doc and same time I got another email from student EFG asking for doc file named as London.doc
Please help me Sir.
Try this: ruclips.net/video/wjSvoWgb5Lk/видео.html and ruclips.net/video/DU_Vg5QJAKQ/видео.html
@@ExceltrainingvideosSir, Thank you. Please do check your email inbox. I have found your email ID on one of your youtube videos. Sorry to send you an email without asking your permission.
Hello sir , I am able to create a sample pdf as receipt from xls macro and send using gmail and i am creating file name from particular cell value and run time creating folders for years/month etc and also storing file on my local system , now what i want is to send an email to particular user say b101 with his email id , but while i am addind .Addattachment it is not ablel to pick automatically the value and send that file as an attachment
Hello Sir,
I am having an outlook limit of 10mb..then also while executing the code it's throwing an error as "attachment size is exceeding the limit".kindly help over this issue of possible
Hi Dinesh.. Thanks for this.. it so helpful. Two questions 1. How do i add hard text from the macros + picking up text from the excel sheet?
2. How do I choose the email to send from? I have several email addresses in my outlook.. how do I choose which one to send from. Currently I have to .display and then select the send from email address.
This was great. Simple and explained perfectly. I wonder if instead of starting with a blank email, I could start with an email template (.oft) that has everything needed except the recipients. So the template would have the subject, body, etc. I just need to open the template, add the names from my spreadsheet to the TO: box, and display or send. Let me know if you have a suggestion. Thank you
Go for it! Also share your success right here with the RUclips community.
Hi - thank you for this video! I am getting an error on the Set OutApp = CreateObject ("Outlook.Application") step. When I go to tools & references my microsoft outlook shows "15" and not "16" like yours. I think I must have a much older version of office. Could you advise what I need to change the language to accommodate? Thank you!
Use 15.
amazing, many thanks
Can we use this for other app than outlook?
I need your help sir with one code. How to filter based on customer name and send their individual data to all customers in a single click. so basically its filter their data and convert their data as a PDF file and send to all the customer individually
This is awesome! But I noticed that the body of the e-mail must be written in the C2 cell... Is there a chance let`s say if I have a certain e-mail template to use that template to send it ? If yes what changes should i need to do in VBA? Thanks in advance!
You'll have to tweak your code according to the cell addresses of the email template if its in Excel. This VBA tutorial will also help: ruclips.net/video/wjSvoWgb5Lk/видео.html
Thank You Sir. Is it possible to share macro in any link?
Very Very helpful, Thank you so much
Glad it was helpful! Please share the Excel tutorial with your friends too.
Hello Dinesh.
I am having issue with this particular combination
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) GREATER THAN 0 Then
It's not allowing me to run the Marco.
Replace GREATER THAN with the symbol '>'
Thank you so much sir..
It's Very helpful and this works for me even i modified it according to my need. 😊
So nice of you
What code would need to be added to be able to use desired bold text in the body of the email (Column C)? If there are some words that are to be bold, Highlighted etc.
thank you!
These tutorials will help:
ruclips.net/video/k_YJ-XHdt_I/видео.html
www.exceltrainingvideos.com/nested-do-while-loop-instr-function-in-excel-vba/
Or search my channel or website.
very well done sir thanks
So nice of you
Thanks if you have the updated version please
Thank you! But my macro does not recognize the file path on spread sheet. It is MS offfice 2013 and outlook object library 15. Can you please advise if there is any solution? it just said invalid directory :(
Query not at all clear.
hi sir, thank you so much for this video. the macro can send the emails successfully but can I check with you why doesn’t the emails appears in the Sent Folder? Thanks
Check your code.
'.Send
.display
Remove the apostrophe from Send and add an apostrophe to Display.
Hi Sir, thank you so much for this tutorial, really helps! Just wanted to ask, what if the file I need to attach isn’t constant, I mean every week the name of the file changes, how do we put this?
Try this link: ruclips.net/video/wjSvoWgb5Lk/видео.html
@@Exceltrainingvideos sir, I have similar query as above. But the link that you shared doesn't cover for attachment issue. Could you please help.
sir, i am working on a multipage inventory userform , i have four tabs, what i want is every time i change tabs i want to display a relevant listbox after a progress bar. is this possible, if so can you make a video with a example.
OK
hello sir
i have one question in the given code..
say for example email id repeats for more than 1 row then in such cases i would like to send one email for such range
Check for duplicate IDs and send only to one.
In my library list Microsoft Outlook library is not show
Hi sirs, I seem to have error msg: user-defined type not defined when I try to click Run. Pls advise
This link will guide: www.exceltrainingvideos.com/send-emails-to-multiple-persons-with-multiple-attachments/
Hi Dinesh, I am getting below error:Run time error 429ActiveX component can't create objectSet OutApp = CreateObject("Outlook.Application")Pls let me how to fix this.
Check your code here:
www.exceltrainingvideos.com/send-emails-to-multiple-persons-with-multiple-attachments/
Did you activate the Outlook library?
Hi, any suggestions. Want to send multiple emails to different contacts/cc , with different headline and email body.
Search my channel.
Sir.. i have to send multiple ZIP files each of minimum 5mb but we have outlook email limitation 10 mb can you pls help me with the VBA code that will open new email once the attachment size crosses 10 mb
How to Keep "From Group Address".
How to Keep BCC.
And is there any possibility that we can directly browse for attachment from the Folders.
Lastly in this Code Email Sent Code, Failed Code is Missing.
Check your code. BCC can be done like CC.
This was so helpful! Is there a way to just have the code send the emails without reviewing them?
This Excel tutorial will guide: ruclips.net/video/EGXPQs0R5YI/видео.html
Can you share the code if we don't want to update the file path in columns but rather select the folder path and it should match pdf file name with column cell values say for an example pan numbers in excel to match to pdf file name which has pan number with year details.
These VBA tutorials will help:
www.exceltrainingvideos.com/how-to-loop-through-subfolders/
www.exceltrainingvideos.com/copy-specific-files-from-folder-and-subfolders-into-destination-folder/
www.exceltrainingvideos.com/how-to-open-folder-in-excel-using-vba/
www.exceltrainingvideos.com/tag/validate-alphanumeric-data-in-text-box-on-user-form/
www.exceltrainingvideos.com/validate-alphanumeric-data/
Or search www.exceltrainingvideos.com or this channel
Sir, my mail is outlook 365 and is configured to Microsoft Mail application and not Outlook 2007. When I run the macro the mails are shown in Outbox of outlook 2007 and not sent. The macro is not picking Mircrosoft Mail instead it picks outlook 2007. I cannot configure outlook 2007. Pls help in sending mails using outlook 365.
Did you activate the Outlook Object Library?
Sir, microsoft outlook 12.0 object library is already activated but still not working
Hi there. I am trying to send emails but my organization does not use traditional email addresses with the @ symbol. We use a unique 7-digit identifier. My email at work is "AV12345". How can I edit the code to recognize the text instead of looking for the @?
Ask your system administrator how he converts the 7-digit identifier to an email address.
Hi Dinesh, I am using MS Office 2016 and when I run the VBA code, it gives me the following error as a pop-up
Run-time error '-2146959355 (80080005)':
Server execution failed.
When i click on debug it takes me to the line
Set OutApp = CreateObject("Outlook.Application")
... and highlights this.
Can you tell me what am i doing wrong, please?
thanks,
This link will guide: docs.microsoft.com/en-us/troubleshoot/windows-server/application-management/error-8008005-when-start-complus-applications
@@Exceltrainingvideos thanks for your reply. I changed the key in the registry editor (added,512 to SharedSection), restarted the computer, and still get the same error when I run the VBA script. any thoughts please?
Hey, this is a great tutorial. But what do we do if we are to send different emails to different customers. Like Client001 has a different email body and different subject., so are the different clients. How can we customize the code?
Simply make an individual column of subject and mail body.
This way u can send different mail to different client with different subject and mail body.
I myself have prepared that way and mailed 700 clients yesterday only.
Hello, thank you, works perfectly! What should i add if i want to ship one row? I tried to add (') before email address in excel however its still sending
This VBA tutorial will guide:
ruclips.net/video/XSR4gmBuqiE/видео.html
You can also search this channel or visit my website www.exceltrainingvideos.com/ for more solutions.
This is good but the sending emails can't be shown in 'SENT' folder of my email.
Great help sir
Please share with your friends too. Thanks.
Dinesh sir i want to send multiple mails with multiple attachements but body lines data should be in html with signtaure graphic _please share the code like to replace
I don't have the code for your query.
Hi. Is there a command that will automatically click send button? Everything is automatic, but the send button click is manual.
Good question!
This would be nice to have.
just remove the ' in '.Send
Next FileCell
'.Send
.display
End With
Really good video. I am a complete ignorant about this stuff but I need this very much. It looks like I did it right (i am not getting any errors) but when I click on "run" nothing happens. Any tips? Please note I don't know how to code. Excel and Outlook 2016.
This link will guide: www.exceltrainingvideos.com/tag/send-emails-to-multiple-persons-with-multiple-attachments/
@@Exceltrainingvideos It worked, thanks!
Sir where did u click to declare the variables as mentioned in the video
Watch the video again.
I want to mention the name of the particular recipient in the body. How do I do it?
Try this VBA tutorial: ruclips.net/video/wjSvoWgb5Lk/видео.html
@@Exceltrainingvideos Thanks a lot. I also wanted to add some styles to a particular part of the text to highlight it. Like Mentioning the name or the date in bold when it goes in the form of an email. Could you please share a video on the same?
Would love to see a more advance feature for each email have option to include specific verbiage for each email. This would probably require word mail merge set up. How would you do that? Everything in your video is great but add the option to add tailored email verbiage for each email. Thanks. Maybe future video? I’d be happy to make contribution for your efforts.
Thanks. Can you demonstrate verbiage with an example?
Dinesh Kumar Takyar your video tutorial is perfect. My question has to do with the email body only. Example: I have list of 40 people I need to send email and PDF attachments. For each email (40 people) I need to tailor the email body and subject line with specific data. Example email 1 subject: abc124-CL
email body:
Your assignment is below:
abc-CL
Area: England
Contact Joe Hunger
Email 2 subject: def-4321
email body:
Your assignment is below:
def-4321
Area: Idaho
Contact: Hunter Pho
Hope this explains better. Everything else in your video is perfect. I was just wondering if it was possible to have a more dynamic feature for email subject line and email body for each email recipient. If you send me your email we can discuss more specifics offline.
Got it! That will require much more coding.
Hi, can i send two attachments? Just try with semi colon, wont work
Did you read the description accompanying the Excel VBA training tutorial on how to send emails to multiple persons with multiple attachments?
Thank you Dinesh, This is a great video for learning, my request to you is how can we combine 1. Send Emails to Multiple Persons with Multiple Attachments and 2. Send Email from Particular Mail box.
Please help me as this would be very helpful for me
This Excel VBA tutorial in Excel will guide: ruclips.net/video/R0qzfYJr6d8/видео.html
How can I make each email folder its own when I send it takes all the attachments from this file and so on for each email
Interesting question.
Hello Sir, Thank you for the code it really helps. I have one query, my file path has some spaces in it then attachment is not happening when I run the macro. How can I overcome this scenario.? Kindly help. Thanks in advance.
I don't think that spaces in a file path matter. If you wish you can rename your files and directories or create new ones temporarily.
Hi, we just want to use our group "From" mail address to send email so how can we change to our default to group mail
Change it from Outlook!
Hi Sir, why .send is not working with error "Outlook does not recognize one or more names"
Try again. The link to this VBA tutorial will guide: www.exceltrainingvideos.com/tag/send-emails-to-multiple-persons-with-multiple-attachments/
Sir thanks you are really awesome
So nice of you
Super sir it's working thanks a lot
Welcome 👍 Please share the Excel VBA tutorial with your friends.
very helpfull for my daily work
Happy to help.
Hello Dinesh Kumar sir how to fill database data in invoice template or pdf file or extract data automatically with customer name and date wise in excel vba
This link should help solve your problem: www.exceltrainingvideos.com/invoice-tracker/
Or search www.exceltrainingvideos.com
Dear Sir, can i put my questions here?
1) Can we add multiple file path in one column?
2) can we add multiple emails in CC?
Great tutorial sir, I've a doubt sir.
Can we automate this sending process because I need to hit sent button. How should I do it
Remove the apostrophe from the '.Send' line of code and place an apostrophe before the '.display'.
Hi Sir, can you please help me while creating multiples emails from excel If we want to copy paste multiple range in each drafts how we can copy paste through excel.
The links to this Excel VBA tutorial will guide: ruclips.net/video/TwTPecUOS_A/видео.html
www.exceltrainingvideos.com/tag/send-numbered-message-automatically-through-outlook-using-vba/
Or search this RUclips channel or our website www.exceltrainingvideos.com
@@Exceltrainingvideos Thank you sir
Sir, It is showing compile error: syntax error and highlighting this If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) GREATER THAN 0 Then
Instead of GREATER THAN use >. Read the description accompanying the video.
@@Exceltrainingvideos Thank You, Sir. It's running but in more than 100 emails, it is getting Run time unknown error and asking to restart outlook.
Hi sir how to add the receiver name in the body as
Hi Naren,
These Excel VBA training tutorials will guide:
www.exceltrainingvideos.com/how-to-use-automation-to-send-ms-outlook-mail-using-excel-vba/
www.exceltrainingvideos.com/send-numbered-message-automatically-through-outlook/
You can also search my website or this channel.
Thanks!
Welcome!
Sir,
Can we have a customized mesaage refering some data like name, address, mobile num in the body, refering from other cells and drfat the mail.
This Excel VBA tutorial on Outlook will guide: www.exceltrainingvideos.com/how-to-use-automation-to-send-ms-outlook-mail-using-excel-vba/
Hello, I am using the below code to send mail to multiple persons based on listbox selection:
Private Sub Valider_Click() 'choisir les personnes
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True Then temp = temp & Me.ListBox1.list(i) & " "
Next i
SelectDestChoice = Me.ListBox1.ListIndex
Unload Me
End Sub
with
Public SelectDestChoice 'allows me to slect the recipients
My problem is that this code choses random recipents and only one person not those who I have selected from my listbox, can you identify the error please ?
Noted
Hi sir,
I am getting a Run-time error '438'
Object doesn't support this property or method, Can you please help me with this error
Check your code. This link will help: www.exceltrainingvideos.com/tag/send-emails-to-multiple-persons-with-multiple-attachments/
Did you check the code given in the description accompanying this video?
Hello Sir. Thanks a bunch for the video. I receive Run time error type mismatch error for this line :If Dir(FileCell.Value) "" Then". Could you please help me to resolve this issue.
Can you share your code or read the description or visit www.exceltrainingvideos.com/tag/send-emails-to-multiple-persons-with-multiple-attachments/
@@Exceltrainingvideos Sir, I am also getting same Run time error: If Dir(FileCell.Value) "" Then. please help on this.
Hello sir , thanks for this video
But I don't want display screen in last .. I want to send directly all mails
I have tried many times but I'm help less. Please guide me
.Visible=False
Great tutorial. I am getting a runtime error "287" message with "application-defined or object-defined error" messages. Debug indicates the ".Send" command. Any help is appreciated.
Most probably you forgot to activate a few references:
From Tools --> References activate
1. Microsoft HTML Object Library
2. Microsoft Internet Controls
@@Exceltrainingvideos Thanks for the reply. I do have those references activated but I am still getting the same error message.
@@amiraziminia5270 Is there an apostrophe before send? You need to delete it.
@@Exceltrainingvideos The apostrophe is before ".display". There is nothing before .Send.
@@amiraziminia5270 Can you share the code in the comments section?
Thank you sir
Welcome
Can we send mails through lotus notes also or only Outlook
This link will help: www.rondebruin.nl/win/s1/notes/notes.htm
Sir how to Keep/add " FROM Group email address" :- VBA code
Will work on this idea.
Thankyou Sir
Welcome!
Hi thank you very helpful, when I try to run the code, nothing happens. No error, nothing. No outlook window comes up.
This tutorial will help: www.exceltrainingvideos.com/send-emails-to-multiple-persons-with-multiple-attachments/
Don't copy paste and check your code line by line.
Sir, when i pasted ur code showing Run time error 9 subscript out of range
Copy/Paste doesn't work!
Can we do the same in outlook 365office
Run time error 1004
Unable to get the SpecialCells property of the range class
Kindly help for this error
Check your code again.
Hello sir great video.
I want to send production report to my senior in automatically by outlook.
Production report genrate every morning
Please suggest something.
This Excel VBA tutorial on time will guide: www.exceltrainingvideos.com/automate-calculations-with-time-using-vba/
i am using only your source code as it is. and excel file too. but there is syntax error line no. 27 col. 66
How can you do that? You need to replace 'NOT EQUAL TO' with
Hi Sir,
I wanted to send mail to more than 10 mail ID's. what will be command.. .to= .......... can we set range for Mail ID's... please help
Isn't that what you learnt just now? You may like to view this video: ruclips.net/video/wjSvoWgb5Lk/видео.html
Hi sir it's very helpful can you tell me how can i add more then one mail ids in cc and to
This Excel VBA tutorial will guide: www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/
@@Exceltrainingvideos thanks sir got from the above link just need little more help can we add column as per the attachments
how do i make the script send the emails without having the user click "send"?
This Excel tutorial will help: ruclips.net/video/EGXPQs0R5YI/видео.html
respected sir
please make a video mail merger multiple files with winword also. i am waiting for this with coding
Sir can you help me please.
I getting compiler error.
Variable not defined
Did you check the description accompanying the video carefully?
@@Exceltrainingvideos yes sir
Please help
xlCellTypeContants
Hi sir your video is very helpful for vba beginner, this is one of the task here I'm struggling can you please help me, I've multiple data list for example lead 1 , lead 2and 3 with team details of each lead multiple employee tagged like for lead 1 10 details, lead 2 20 employee details it's not constant Evey week it's changed dynamicly , now I've to filter and send mail lead1 with team details and then lead 2 with team details until lead list end, may I know if it's possible to create the same concept with using pivot table, or this same way we watch in this video, if anything possible I'm ok with that please make a video, I'm happy to learn from you
Try doing as shown in the video by arranging the data carefully in the proper columns.
@@Exceltrainingvideos sir in this video shows only one row data for one mail I'd , but my concept is 1 mail I'd multiple rows data ,how to create dynamicly range select and how to filter next lead automatically and send mail
@@muthuraja3128 hi Muthu ... did you find the solutions
@@JustonclicksBlogspotIn not yet do you have any solution, please suggest
Sir I am getting an error message run-time error 429 ActiveX component can't create object
Check your code by watching the video again!
@@Exceltrainingvideos ok sir
Respected sir
please make a video mail merger multiple files with winword also. i am waiting for this
Hi Sir, thanks for.this vídeo It really worked... What Should I do If I want to release automatically the emails??
Query not clear.
@@Exceltrainingvideos i was trying to see how to send the emails not only stop on Outlook preview, but I already found how to do that, thanks
Sir it works great. I just wanted to know the code of I need to send from group mailbox.
This VBA tutorial will guide: ruclips.net/video/R0qzfYJr6d8/видео.html
@@Exceltrainingvideos Hi, can i know how to put 2 attachments?
@@Exceltrainingvideos i try put semi colon but error 52 pop up