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 .

WHOTO	First Name	D-Last	D-Email	Recipient 2	Recipient 3	Recipient 4	Recipient 5	Path
BERLN	Karim	1	123	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
LEIPZ	Akram	2	124	TEST@CompanyAB.com		TEST@CompanyAB.com		C:\Users\kmahraz\Desktop\May 2013
CINCI	Ismail	3	125	TEST@CompanyAB.com			TEST@CompanyAB.com	C:\Users\kmahraz\Desktop\May 2013
IND	Meryem	4	126	TEST@CompanyAB.com	TEST@CompanyAB.com			C:\Users\kmahraz\Desktop\May 2013
ODESA	Kamal	5	127	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
TULSA	Farid	6	128	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
KONE	Hafid	7	129	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
OKC	Driss	8	130	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
ALBNY	Abdeljalil	9	131	TEST@CompanyAB.com		TEST@CompanyAB.com	TEST@CompanyAB.com	C:\Users\kmahraz\Desktop\May 2013
ESSEX	Hassania	10	132	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
SWLOU	Hicham	11	133	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
WSTCH	samad	12	134	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
BADGR	Faysal	13	135	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
DBORN	Khalid	14	136	TEST@CompanyAB.com	TEST@CompanyAB.com			C:\Users\kmahraz\Desktop\May 2013
NORWK	Isam	15	137	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
ARBOR	Youssef	16	138	TEST@CompanyAB.com		TEST@CompanyAB.com	TEST@CompanyAB.com	C:\Users\kmahraz\Desktop\May 2013
MEMPH	Samir	17	139	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
FRANK	Hassan	18	140	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
MAFST	Zakaria	19	141	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
STAVA	Lamia	20	142	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
AMSTR	Janat	21	143	TEST@CompanyAB.com	TEST@CompanyAB.com	TEST@CompanyAB.com	TEST@CompanyAB.com	C:\Users\kmahraz\Desktop\May 2013
BANGA	Imane	22	144	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
CHNAI	Inass	23	145	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
SWDLI	Houda	24	146	TEST@CompanyAB.com	TEST@CompanyAB.com			C:\Users\kmahraz\Desktop\May 2013
XHITE	Zineb	25	147	TEST@CompanyAB.com		TEST@CompanyAB.com		C:\Users\kmahraz\Desktop\May 2013
XINDU	Chamae	26	148	TEST@CompanyAB.com				C:\Users\kmahraz\Desktop\May 2013
XOGBA	Nawal	27	149	TEST@CompanyAB.com	TEST@CompanyAB.com			C:\Users\kmahraz\Desktop\May 2013

<tbody>
</tbody>

 

[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.