Macro to Send e-Mail with PDF Attachments

Gaura215

Board Regular
Joined
Feb 2, 2011
Messages
97
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,520
Messages
6,179,270
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top