Excel Macro for attaching pdf to outlook mail

jjj12345

New Member
Joined
Apr 12, 2021
Messages
1
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Watch MrExcel Video

Forum statistics

Threads
1,129,296
Messages
5,635,370
Members
416,856
Latest member
silentir

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
Top