Send email to multiple recipients with each their own attachment

[YelloBello]

Hi, I have VBA code that I got from online, it sends email out to several recipients with each their own text if necessary. Now i would like to add an attachment unique to each recipient.
In my code i see that it already has an attachment listed, but it does not work, when i sent out a test mail, it gives me the name of my file in my mail, it does not add the attachment to the mail.

 If atchmnt <> "" Then
        If Dir(atchmnt) <> "" Then .Attachments.Add atchmnt
      End If

This is the code I added in to create an attachment, but it doesnt work.

Can someone please help me?

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("Send_Email").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("C14:C" & lstRow)


'initializing outlook object to access its features
Set outApp = New Outlook.Application


'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
    msg = Range(cell.Address).Offset(0, 2).Value2 & "<br>" & "<br>" & Range(cell.Address).Offset(0, 3).Value2 & "<br>" & "<br>" & Range(cell.Address).Offset(0, 4).Value2 & "<br>" & "<br>" & Range(cell.Address).Offset(0, 5) & "<br>" & Range(cell.Address).Offset(0, 6).Value2
    atchmnt = Range(cell.Address).Offset(0, -1).Value2
    ccTo = Range(cell.Address).Offset(0, 7).Value2
    bccTo = Range(cell.Address).Offset(0, 8).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
        .HTMLBody = msg
        .Subject = subj
        If atchmnt <> "" Then
        If Dir(atchmnt) <> "" Then .Attachments.Add atchmnt
      End If
        .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 created
        Set outApp = Nothing
        Application.ScreenUpdating = True
Application.ScreenUpdating = True
End Sub

 

[davesexcel]

What do you have in the atchmnt range?

 

[YelloBello]

Hi,

I am extremely new with VBA, so all of the code was not written by me rather I looked it up online and adjusted a few things to my personal sheet.

I do not know what the .attachment.add does

To answer your question, in the atchmnt = Range, i have a hyperlink to the file in question.

 

[davesexcel]

It should be the full file name(folder.address & filename)

 

[YelloBello]

Hi,

I tried, but it doesnt work. It only gives the name of the filepath in the mail, not the file itself.

I suspect i am missing perhaps a string or something for the file path, but i don't know how to write vba code for that

 

[YelloBello]

it's been solved!

 

[human688]

Are you able to post you solution VBA code, YelloBello? Also, if possible, could you highlight the portions that I would need to edit to cater it to my own spreadsheet?

I have zero VBA experience other than knowing that it opens up a world of possibilities. I posted a thread, but no one responded, so I'm a bit stuck. My help request is pasted below.

Thanks!!

Excel Mail Merge with attachments

I need to send the same exact email (subject, greeting, body, and signature) to several different people but they all have their own individualized attachments. I know that mail merge is more often used (and designed) for the inverse effect of sending the same info with different greetings and...

www.mrexcel.com

<<I need to send the same exact email (subject, greeting, body, and signature) to several different people but they all have their own individualized attachments. I know that mail merge is more often used (and designed) for the inverse effect of sending the same info with different greetings and what not to multiple people, so I don't think it is quite robust enough for what I want.>>