Merge email with excel list AND add an attachment

[ColinCoady]

Looking for VBA code to run a merged email to recipients listed on an excel spread sheet that has email addresses, first names and last names.
I want email address in the BCC outlook field and first name and last name in body with the rest of my text.
The clincher is how to add an attachment (pdf file) to the email.

Anybody solve this already without 3rd party software?

Thanks
-Colin

 

[DanteAmor]

This is an example to send an email by outlook taking the data from the sheet.


What do you mean by third-party code?

You can take the example to make your shipment.
If you need help, explain how your data is on the sheet and I'll gladly help you update the code.

Sub email_test()
    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    dam.To = Range("B2").Value              'recipients
    dam.Cc = Range("C2").Value              'with copy
    dam.Bcc = Range("D2").Value             'with copy hide
    dam.Subject = Range("E2").Value         'Subject
    dam.Body = Range("F2").Value            'body
    dam.Attachments.Add Range("G2").Value   'file
    dam.Display                             ' Change Send to send email
End Sub

 

[ColinCoady]

Thank you DanteAmor
I meant a 3rd party Outlook add-ins that can be purchased.

i appreciate your help on this. Here is more info
Email addresses are in "sheet1" A2:A31 but not all the cells are filled. The number of emails will fluctuate.
First name in B2:B31
Last Name in C2:C31
Subject in G17
Attachment file name in G18
Valediction in G19
Body of email in G20

The excel workbook is in the same directory as the file to be attached. Not sure if the whole path is needed.
I also need my standard outlook signature to be included.

Hope you can help.
-Colin

 

[DanteAmor]

Try this

Sub email_test()
    For i = 2 To 31
        If Cells(i, "A").Value <> "" Then
            eBcc = eBcc & Cells(i, "A").Value & "; "
            eName = eName & Cells(i, "B").Value & " " & Cells(i, "C").Value & vbCr
        End If
    Next
    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    dam.to = "email@gmail.com"
    dam.Cc = "other@gmail.com"
    dam.Bcc = eBcc
    dam.Subject = Range("G17").Value         'Subject
    dam.Body = Range("G20").Value & vbCr & vbCr & eName & vbCr & vbCr & Range("G19").Value
    If Range("G18").Value <> "" Then
        eFile = ThisWorkbook.Path & "\" & Range("G18")
        If Dir(eFile) <> "" Then
            dam.Attachments.Add eFile
        End If
    End If
    dam.Display                             ' Change Send to send email
End Sub

Notes: in the cell you must put the name of the file with extension
The signature is not shown, you will have to put it in the body of the mail or in Valediction.

 

[ColinCoady]

Thank you DanteAmor

It puts all the email addresses into the BCC field but I want to send a separate email with the same info and attachment to each recipient on the list.
can you make that work?

-Colin

 

[DanteAmor]

Looking for VBA code to run a merged email to recipients listed on an excel spread sheet that has email addresses, first names and last names.
I want email address in the BCC outlook field and first name and last name in body with the rest of my text.
The clincher is how to add an attachment (pdf file) to the email.

Anybody solve this already without 3rd party software?

Thanks
-Colin

But that was not explained in your original request.

You have a couple of examples that I sent you.

 

[ColinCoady]

Thank you Dante Amor
Sorry I didn't explain myself very well at the beginning.
I'll work with what you gave me.
Regards,
-Colin

 

[DanteAmor]

OK, check the code and try to adapt it. if you have difficulties, then put the requirement in detail and with examples, of what you have in which cells you have it and how you expect the result.