VBA help to send pdf attachments via Outlook to Excel distribution list

[dbwiz]

I need help with VBA code to add to my Excel Distribution list that when run, would look for the path, grab the file that starts with the client name in Col A, and attach to the email, along with a predefined message/header.

The distribution list has the following:

Excel Workbook
	A	B	C	D	E
1	Client Name	Email	First Name	Last Name	Path
2	ABC	JaneDoe@abc.com	Jane	Doe	Q:\Agency Bill\PDF Invoices\2012 Installments\Jan 12
Sheet1

Excel 2007



I would want the email heading to be Installment Invoice and to say something like Dear First Name,
editable text (for example, "Attached please find a copy of your invoice dated"
last portion of path name ( in this case, Jan 12).

More editable text.
Insert Outlook signature.

I have been looking around and have found ways to generate and email pdfs from Excel - however our invoicing system generates bulk pdfs which we have to break up and save to file, in a path like the one above, and then manually select them one by one, typing in the email address, etc. We are hoping to automate the last step of this. Any help/tips would be greatly appreciated!

 

[MWomack]

I have a similar situation. I have an email (in Outlook) that I need to send to a list of recipients located in an excel file. I have the email in a word document, but want to add an attachment. How can I send the email with an attachment? I have done coding in the past, but it has been a long time. I would appreciate your help.
Thanks,

 

[ewdong2002]

I've got a similar question here: I created a macro that created PDFs into a folder and am trying to create a macro that automates the sending theses PDFs as attachments in outlook. My reference table is currently set up as follows:

Name Email File
Name1 Email1 File_Loc1
Name2 Email2 File_Loc2
Name3 Email3 File_Loc3

My code is as follows:

Sub Mail_FSPDF()


Dim OutApp As Object
Dim OutMail As Object
Dim FS As String
Dim Path As String
Dim Dte As String
Dim FSFile As String
Dim AttachFile As String
Dim MailBody As String
'Dim Rng As String




Set outlookOBJ = CreateObject("Outlook.Application")

Set Rng = ThisWorkbook.Sheets("Emails").Range("a2:A215")
For Each cell In Rng

Path = cell.Value
If Path <> "" Then

FS = cell.Offset(0, 2).Value
Dte = Right(File, Len(File) - InStrRev(File, "\"))

FSFile = Dir(File & "\*.*")

Do While FSFile <> ""

If InStr(FSFile, FS) > 0 Then

AttachFile = Path & "\" & FSFile

MailBody = "See the February 2015 Field Scorecard Attached"

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
With OutMail
.Subject = "February 2015 Field Scorecard"
.To = SendName
.Body = MailBody
.Attachments.Add (AttachFile)
.Display
'.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If
FSFile = Dir
Loop
End If
Next
End Sub

 

[kmahraz]

Hello,
I know this is an old post; but this is exactly what i was looking for. I was hoping i can get some assistance, Everything in the code work perfectly beside the CC portion where it duplicate the recipients email in the CC in some cases, i would like to be able to have recipient 2, 3 , 4 and 5 in CC case. can any of you help with this please .
[TABLE="width: 1480"]
<tbody>[TR]
[TD]WHOTO[/TD]
[TD]First Name[/TD]
[TD]D-Last[/TD]
[TD]D-Email[/TD]
[TD]Recipient 2[/TD]
[TD]Recipient 3[/TD]
[TD]Recipient 4[/TD]
[TD]Recipient 5[/TD]
[TD]Path[/TD]
[/TR]
[TR]
[TD]BERLN[/TD]
[TD]Karim[/TD]
[TD]1[/TD]
[TD]123[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]LEIPZ[/TD]
[TD]Akram[/TD]
[TD]2[/TD]
[TD]124[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]CINCI[/TD]
[TD]Ismail[/TD]
[TD]3[/TD]
[TD]125[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]IND[/TD]
[TD]Meryem[/TD]
[TD]4[/TD]
[TD]126[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]ODESA[/TD]
[TD]Kamal[/TD]
[TD]5[/TD]
[TD]127[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]TULSA[/TD]
[TD]Farid[/TD]
[TD]6[/TD]
[TD]128[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]KONE[/TD]
[TD]Hafid[/TD]
[TD]7[/TD]
[TD]129[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]OKC[/TD]
[TD]Driss[/TD]
[TD]8[/TD]
[TD]130[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]ALBNY[/TD]
[TD]Abdeljalil[/TD]
[TD]9[/TD]
[TD]131[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]ESSEX[/TD]
[TD]Hassania[/TD]
[TD]10[/TD]
[TD]132[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]SWLOU[/TD]
[TD]Hicham[/TD]
[TD]11[/TD]
[TD]133[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]WSTCH[/TD]
[TD]samad[/TD]
[TD]12[/TD]
[TD]134[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]BADGR[/TD]
[TD]Faysal[/TD]
[TD]13[/TD]
[TD]135[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]DBORN[/TD]
[TD]Khalid[/TD]
[TD]14[/TD]
[TD]136[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]NORWK[/TD]
[TD]Isam[/TD]
[TD]15[/TD]
[TD]137[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]ARBOR[/TD]
[TD]Youssef[/TD]
[TD]16[/TD]
[TD]138[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]MEMPH[/TD]
[TD]Samir[/TD]
[TD]17[/TD]
[TD]139[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]FRANK[/TD]
[TD]Hassan[/TD]
[TD]18[/TD]
[TD]140[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]MAFST[/TD]
[TD]Zakaria[/TD]
[TD]19[/TD]
[TD]141[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]STAVA[/TD]
[TD]Lamia[/TD]
[TD]20[/TD]
[TD]142[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]AMSTR[/TD]
[TD]Janat[/TD]
[TD]21[/TD]
[TD]143[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]BANGA[/TD]
[TD]Imane[/TD]
[TD]22[/TD]
[TD]144[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]CHNAI[/TD]
[TD]Inass[/TD]
[TD]23[/TD]
[TD]145[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]SWDLI[/TD]
[TD]Houda[/TD]
[TD]24[/TD]
[TD]146[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]XHITE[/TD]
[TD]Zineb[/TD]
[TD]25[/TD]
[TD]147[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]XINDU[/TD]
[TD]Chamae[/TD]
[TD]26[/TD]
[TD]148[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
[TR]
[TD]XOGBA[/TD]
[TD]Nawal[/TD]
[TD]27[/TD]
[TD]149[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD]TEST@CompanyAB.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\kmahraz\Desktop\May 2013[/TD]
[/TR]
</tbody>[/TABLE]

 

[LINAC]

Hi Dave,

This code works perfect. However, on my data, there are values in column B that have the same value in column A. How can I make the code to look for the pdf files in the path that matches values in column B and attach them to the same email if they have the same value in Column A?

 

[daverunt]

You'd need to post the actual code you are using, an example of your sheet with at least one matching and one non-matching row and what you expect it to mail.