Merge email with excel list AND add an attachment

ColinCoady

New Member
Joined
Mar 21, 2019
Messages
35
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.


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

New Member
Joined
Mar 21, 2019
Messages
35
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this

Code:
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

New Member
Joined
Mar 21, 2019
Messages
35

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Mar 21, 2019
Messages
35
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,269
Messages
5,571,234
Members
412,372
Latest member
JON_ROCKS
Top