Macro to Send e-Mail with PDF Attachments

[Gaura215]

Hello

I have a spreadsheet with email address written in coloum B, and the names in colooum A. There are PDF attachments for all names mentioned in Coloum A, in a single folder.

I am looking for a macro which can send an email to all recipients mentioned in coloum B, and attach the corresponding attachments from that folder.
Note: PDF File name is in format "RBS" & "mm (month)" & Name (which is mentioned in coloum A).

Please advise.

Thanks in advance to all excel Gurus.

 

[daverunt]

Hi

Have a play with this basic code.
It checks the name in Column A against the filename string
i.e 'Fred' in 'RBS 02 Fred.PDF' and attaches all files with said name from the (currently) fixed directory c:\junk to a mail and creates a mail for all addresses in Column B currently fixed for B1 to B4.

Change sheet names and paths

Sub Mail_PDF()
Dim OutApp As Object
Dim OutMail As Object
 
For i = 1 To 4 'change to number recipients
If Worksheets("Contacts").Range("B1").Value <> "" Then 'sheetname!
SendName = Worksheets("Contacts").Range("B" & i).Value 'sheetname!
 
   Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = "Attached pdf file"
            .To = SendName
            .Body = "PDF Test"
 
             filename = "C:\junk\*.pdf" 'path
 
             PDFFile = Dir(filename)
 
     Do While PDFFile <> ""
 
       'match name with column A name                
            If InStr(PDFFile, Range("A" & i).Value) > 0 Then
 
            .Attachments.Add "C:\junk\" & PDFFile 'path
            End If
            PDFFile = Dir
            Loop
 
            .Display '.send
    End With
 
        Set OutMail = Nothing
        Set OutApp = Nothing
End If
Next i
End Sub