Maccro to send email and attach file related to the recipient

[arthur8]

Hi y'all , I have a tricky issue here ;

I'm working on a code that would send an email to a list of clients with attachment that are related to these people.
The Files (Attachments) are saved via a maccros that uses the name of the client in Sheet 2 Col A to integrate their names in the file's name.ie : ClientName.xlsm (Sheet2 is a list of name generated by another maccros, therefor I cant modify it ) :


Dim pt As Pivottable
Dim Field As PivotField
Dim ClientName As String
Dim wb As Workbook


Set pt = Worksheets("Sheet4").PivotTables("PivotTable1")
Set Field = pt.PivotFields("SUPPLIER")

(((((This first part copies the name of the client in a pivot table filter and generate a report , the report is what I want to send )))))
Dim r As Integer

r = 1

Do Until Worksheets("Sheet2").Cells(r, 1).Value = ""

ClientName = Worksheets("Sheet2").Cells(r, 1).Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewSup
pt.RefreshTable
End With


Set wb = Workbooks.Add
ThisWorkbook.Sheets("Sheet4").Copy before:=wb.Sheets(1)
Columns("E:F").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveWindow.SmallScroll Down:=-9
Range("A1").Select
ActiveSheet.Protect "

wb.SaveAs "C:\Users\FolderXXX & ClientName & ".xls

wb.Close False

r = r + 1

Loop

so this code works .. now I want to send those attachement to the clients ... The clients contact details are in sheet 3 of the same workbook , Col A = ClientName Col D = clientEmailAdress. Don't know if thats clear enough ..
Thanks for your help !!

 

[JimM]

I had to do something similar recently and found this thread very helpful, hopefully you can use it a base for what you want as well

http://www.mrexcel.com/forum/excel-...-code-convert-excel-pdf-email-attachment.html

 

[daverunt]

I did something similar too a while ago.

http://www.mrexcel.com/forum/excel-questions/569216-macro-send-e-mail-pdf-attachments.html

 

[arthur8]

Hah yesm I've already checked this thread , but I think it's missing somethg , thanks for your time and help, appreciated: )

 

[arthur8]

Thanks DaveRuntm this is definitely gonna help, its the exact same issue , MERCI