Excel Macro for attaching pdf to outlook mail

[jjj12345]

Hi,

I have a spreadsheet with names in Column A and email address in column B. There are PDF attachments for all names mentioned in Column A, in a single folder.

I found a macro that can send an email to all recipients mentioned in column B, and attach the corresponding attachments from that folder. In the body of each mail, I want the recipient's name to come after the salutation (Eg. Hi Mark) using a macro.

Any help would be really appreciated.

Please see my current VBA below:



Sub Dr_Statememt_Send_Mail_PDF()
Dim OutApp As Object
Dim OutMail As Object
Dim OutAccount As Outlook.Account

For I = 2 To 18 'change to number recipients
If Worksheets("Recipient").Range("B2").Value <> "" Then 'sheetname!
SendName = Worksheets("Recipient").Range("B" & I).Value 'sheetname!

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
'Set OutAccount = OutApp.Session.Accounts.Item(2)
With OutMail
.Subject = "Subject"
.To = SendName
.Body = "Hi," & vbNewLine & " " & vbNewLine & "Please find attached" & vbNewLine & " " & vbNewLine & " " & vbNewLine & "Regards," & vbNewLine & " " & vbNewLine & "Jane Doe" & vbNewLine & "Finance Officer" & vbNewLine & "ABC Ltd"
'Set .SendUsingAccount = OutAccount
Filename = "\\D\Pdf_files\*.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 "\\D\Pdf_files\" & PDFFile 'path
End If
PDFFile = Dir
Loop

.Display '.send
End With

Set OutMail = Nothing
Set OutApp = Nothing
Set OutAccount = Nothing
End If
Next I
End Sub



Please advise.

Thanks in advance.