VBA - Customizing PDF file name and attaching to email

[AmeliaBedelia]

I am wanting to see if there is a way to change the name of the attached PDF. In the code below it chooses the FileName = wB.FullName. I want to customize the name of the file to be [ Range("LearnerLFName").Value + "-" + Range("CourseName").Value + "-Weekly Report" ]. I have tried:

FileName = Range("LearnerLFName").Value + "-" + Range("CourseName").Value + "-Weekly Report"

And can see it saving something with this name, however when the email displays there is no attachment. The Ranges are referring to named cells in my workbook.

I am hoping someone has a resolution for this. Here is the complete VBA code:


Sub SendEmailPDF()

Dim wB As Workbook
Dim FileName As String
Dim myFile As Variant
Dim OutlookApp As Object
Dim OutlookMail As Object
On Error Resume Next

Set wB = Application.ActiveWorkbook
FileName = wB.FullName
xIndex = VBA.InStrRev(FileName, ".")
If xIndex > 1 Then FileName = VBA.Left(FileName, xIndex - 1)
FileName = FileName & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileName
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
.To = ""
.CC = ""
.BCC = ""
.Subject = Range("LearnerLFName").Value + " " + Range("CourseName").Value + " Weekly Report"
.body = ""
.Attachments.Add FileName
.Display
End With
Kill FileName
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub


Thanks!

 

[Chameleon64]

Try putting the file path in the .Attachments.Add Filename command (eg .Attachments.Add "H:" & Filename)

 

[AmeliaBedelia]

Yes this worked perfectly! Thanks so much!!