Bulk Outlook Emails with CC and Attachments and Priority using Excel VBA (Code included)

Welcome to my ExcelPowerTips Channel!
This video shows you the power of Excel VBA to dynamically via a loop compose and send an Outlook Email for each row of an Excel Dataset containing the required information.
It also shows you how to add the primary and multiple CC circular email addressees with multiple attachments and set the priority for each one.
This method also uses late binding where the Outlook Application object is created during runtime so you don't need to go the the VBA editor menu to select references for the Outlook Object library.
Doing it this way makes it easy to share your Excel workbook with users without issues.
If you enjoyed watching this video consider subscribing and share this amazing Excel Power Tip with others.
Download link to my Excel VBA Code right here:
drive.google.c...
Enjoy!
LIKE - SUBSCRIBE - COMMENT - SHARE - ENJOY!

[@jeevanbabu3187]

Thankyou so much for the support. It worked!!!

[@excelpowertips7447]

You're welcome! happy to help.

[@yodikiwan8450]

Hi Sir,
If the email has a template that contains a table range to send in each email body, please help.
The table range is depends on Employee Name [ Column A ]

[@excelpowertips7447]

Hi, please explain further what you want the VBA macro to perform.

[@ninahuang5607]

i use power automate (personal-free),export outlook 2016 mail to excel 2016, but there is no outlook flag selection can be use in outlook action. Its very important to manage email task progress for deportment. Can you help me how to setting? thank you.

[@excelpowertips7447]

Hi thanks for your comment however I don't know as I haven't used Power Automate before. Maybe refer to forums relating to this may help you.

[@excelpowertips7447]

Yes you should be able to attach any document file as you specify the full path in the Excel sheet for the VBA code to pick.
Thanks for watching.

[@carmenwong9360]

Why the body email with "Please find attached ...." is unable to move to new line, it is on same line with "Hi"?

[@excelpowertips7447]

The email body text is within a single cell and in the formula bar you press ALT + ENTER to move to a new line as a formula text in the separate sheet. This cell is then referenced in the main email list sheet for distribution.

[@siddharth9643]

Hii sir..
I want to send excel table to outlook to more recipients... Can you help me how to create that

[@excelpowertips7447]

So my example contains Two columns in the Excel sheet in columns C and D to pick up the To and CC email address in the VBA Loop code. To include more recipients you will either need to add new columns for each recipient or add the new recipients directly in code with a new CC list like this anonymous example:
' Set email properties
With OutlookMail
.To = wSht.Range("C" & intRow).Value ' Employee main email
.CC = wSht.Range("D" & intRow).Value ' Manager Circular email
' For more than one CC separate with a semi-colon
.CC = "[e-mail]" & ";" & _
"[e-mail]" & ";" & _
"[e-mail]" & ";" & _
"[e-mail]" & ";" & _
"[e-mail]" & ";" & _
"[e-mail]" & ";" & _
"[e-mail]" & ";" & _
"[e-mail]"
This is the part of the code you need to adjust to include the new recipients (make sure you separate each email address with a semi-colon ";" as per above.
It's a great question and hope this helps you.
Thanks for watching and please share this Excel Power Tip.

[@tameressam6622]

where can i get the Macro code please?

[@excelpowertips7447]

Coming soon...

[@excelpowertips7447]

You can download Excel VBA code from this link:
drive.google.com/file/d/1_LYVZ9OwyA1posuBv7M097Dxj-er0evI/view?usp=sharing
If you like my content and downloads, consider supporting me here so I can create more quality content:
paypal.com/donate/?hosted_button_id=KY9HN97ZNPYGU

[@aishwaryapatra4975]

Hi Sir, I have a doubt. If in a single sheet multiple customers are there and I need only one email for that while creating the bulk email, then what should be the code

[@excelpowertips7447]

If you want to just one email with all the recipients in that email then this code might help you:
Remove the outer loop and create a single loop just to gather all email addresses and put into one string like below to a string variable like below:
strSingleBulkEmailAddress = ""
' Compose the single bulk email To string for multiple recipients via loop
For intRow = 2 To intLastRow
strSingleBulkEmailAddress = strSingleBulkEmailAddress + wSht.Range("C" & intRow).Value + ";"
Next intRow
Hope this helps you and please share this very useful Excel Power Tip.

[@aishwaryapatra4975]

@@excelpowertips7447 I am sorry that I explained wrong might be. My doubt is if in a sheet there is a single customer for 5 times but I want to use only one email for those 5 times with a same customer. I can't delete it because different invoices are the for one customer

[@ManagerGCTower2]

Why am i getting out of range remark

[@excelpowertips7447]

To help you how are you using my Excel code ?

[@ManagerGCTower2]

getting out of range error 9

[@excelpowertips7447]

Hi, the VBA code runs fine for me.
Follow the video and look at the guidance notes on the downloaded VBA code (link in the description) and follow the video tutorial.
Thanks for your interest.