Attach PDF to Outlook Using Variable Filename

[gyster]

Hello, and thanks to those who spend their valuable time to help others - I appreciate this very much because I value my own time. I've been working for a few days cobbling together code I've found at various websites, and I'm pretty close to having everything working. I am converting a spreadsheet to PDF, assigning a filename from a cell in the sheet, and then I would like to attach that file to an Outlook email. This is where things break down. When I run the macro, the file is created with the appropriate name, and stored in the appropriate folder, Outlook is started with the correct address inserted, but there's no attachment. Here's the code I have so far:

Sub Save_BOL_As_PDF
' Create a PDF from the current sheet and email it as an attachment through Outlook

Dim EmailSubject As String, EmailSignature As String
Dim CurrentMonth As String, DestFolder As String, PDFFile As String
Dim OpenPDFAfterCreating As Boolean, AlwaysOverwritePDF As Boolean, DisplayEmail As Boolean
Dim OutlookApp As Object, OutlookMail As Object
CurrentMonth = ""
EmailSubject = "Attached Bill of Lading "
OpenPDFAfterCreating = True
AlwaysOverwritePDF = True
DisplayEmail = True
Email_To = "tstransport@aol.com"
Email_CC = ""
Email_BCC = ""

'Create the PDF
'Set Print Range to Save Entire Sheet
With ActiveSheet.PageSetup
  .PrintArea = "$A$1:$O$59"
  .FitToPagesTall = 1
  .FitToPagesWide = 1
End With
  'Set PDFFile to current filename (latest invoice number)
  PDFFile = "C:\Users\ts transport\documents\ts transport\Bill of Ladings" & "\" & ActiveSheet.Range("B8").Value
  'Save to Bill of Ladings folder and set filename as Invoice number (not including Truck Number)
  ActiveSheet.ExportAsFixedFormat _
  Type:=xlTypePDF, _
  Filename:="C:\Users\ts transport\documents\ts transport\Bill of Ladings" & "\" & ActiveSheet.Range("B8").Value, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=False, _
  IgnorePrintAreas:=False, _
  From:=1, _
  To:=1, _
  OpenAfterPublish:=True
  'Create an Outlook object and new mail message
  Set OutlookApp = CreateObject("Outlook.Application")
  Set OutlookMail = OutlookApp.CreateItem(0)
  'Display email and specify To, Subject, etc
  With OutlookMail
  .Display
  .To = Email_To
  .CC = Email_CC
  .BCC = Email_BCC
  .Subject = EmailSubject & CurrentMonth
  .Attachments.Add PDFFile
  If DisplayEmail = False Then
  .Send
  End If
End With
End Sub

Debugger highlights the .Attachments.Add PDFFile line.

I'm pretty sure there is some unnecessary code here, but I don't think any of that is causing a problem. I think it has to do with assigning the filename to the variable PDFFile. What am I missing?

BTW, I had to copy the code one line at a time because it wasn't copying everything and it looked like everything was in reverse order.

Again, I appreciate any help you can offer.

 

[Chihiro]

What is the string contained in cell B8?

 

[gyster]

What is the string contained in cell B8?

Hi Chihiro, and thank you for the response. I found the culprit during my lunch break today...I needed to add & ".pdf" to the end of the filename:= line. All is right with the world once again.