Sending e-mail, multiple attachment, different names VBA

ayman helmy

New Member
Joined
Mar 17, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hello!

A have a working macro (sending email), but I want to upgrade it.

File Name
AttachmentSend Mail ToSubjectMassage BodyCCBCC
Aman Jain​
C:\Users\ExcelTip.com\Desktop\Excel Tip\Splitter Test/Aman Jain.xlsx​
Aman Jain​
Hi Aman Jain​
Monu Chauhan​
C:\Users\ExcelTip.com\Desktop\Excel Tip\Splitter Test/Monu Chauhan.xlsx​
Monu Chauhan​
Hi Monu Chauhan​
Rouhan Dayal​
Hi Rouhan Dayal​
Gaurav Kapoor​
Hi Gaurav Kapoor​
Robin Jain​
Hi Robin Jain​
Tarun Arora​
C:\Users\ExcelTip.com\Desktop\Excel Tip\Splitter Test/Tarun Arora.xlsx​
Tarun Arora​
Hi Tarun Arora​



Here is the macro:
Sub BulkMail()
Application.ScreenUpdating = False

ThisWorkbook.Activate
'Creating references to Application and MailItem Objects of Outlook
Dim outApp As Outlook.Application
Dim outMail As Outlook.MailItem

'Creating variable to hold values of different items of mail
Dim sendTo, subj, atchmnt, msg, ccTo, bccTo As String

Dim lstRow As Long

'My data is on sheet "Exceltip.com" you can have any sheet name.

ThisWorkbook.Sheets("Exceltip.com").Activate
'Getting last row of containing email id in column 3.
lstRow = Cells(Rows.Count, 3).End(xlUp).Row

'Variable to hold all email ids

Dim rng As Range
Set rng = Range("C2:C" & lstRow)

'initializing outlook object to access its features
Set outApp = New Outlook.Application
On Error GoTo cleanup 'to handle any error during creation of object.

'Loop to iterate through each row, hold data in of email in variables and send 'mail to each email id.

For Each cell In rng
sendTo = Range(cell.Address).Offset(0, 0).Value2
subj = Range(cell.Address).Offset(0, 1).Value2 & "-MS"
msg = Range(cell.Address).Offset(0, 2).Value2
atchmnt = Range(cell.Address).Offset(0, -1).Value2
ccTo = Range(cell.Address).Offset(0, 3).Value2
bccTo = Range(cell.Address).Offset(0, 4).Value2

On Error Resume Next 'to hand any error during creation of below object
Set outMail = outApp.CreateItem(0)

'Writing and sending mail in new mail
With outMail
.To = sendTo
.cc = ccTo
.BCC = bccTo
.Body = msg
.Subject = subj
.Attachments.Add atchmnt
.Send 'this send mail without any notification. If you want see mail
'before send, use .Display method.
End With
On Error GoTo 0 'To clean any error captured earlier
Set outMail = Nothing 'nullifying outmail object for next mail
Next cell 'loop ends

cleanup: 'freeing all objects ceated
Set outApp = Nothing
Application.ScreenUpdating = True
Application.ScreenUpdating = True
End Sub







How it is possible to send multiple attachment by ONE email?
For exemple do not send for Neo two emails with one-one attachment, send just ONE, with two attachment or more . I think I need DO - while command... Can you help me out?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top