VBA, PDF code. Previously working, now inexplicably stopped

[sanantonio]

Hi All,

Happy new year and all that. For the past 6 months and right up until the holidays I've been using a VBA code passed onto me by a colleague that automatically PDF's 1 worksheet, attached to an email and sends it out.

I've come back after the holidays and now get an error?

Sub Button10_Click()

  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object

 
  ' Not sure for what the Title is
  Title = Range("Q2")
 
  ' Define PDF filename
  PdfFile = Range("Q2")
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ".pdf"
 
  ' Export activesheet as PDF
  With Worksheets("Data")
        ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
  
    ' Prepare e-mail
    .Subject = Title
    .To = "xxxx@xxxx.us" ' <-- Put email of the recipient here
    .CC = "xxxx@xxxx.us" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "Please see the attached." & vbLf & vbLf _
          & "Regards xxxx Team" & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
  
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
  
  End With
 
 On Error Resume Next
 
  ' Delete PDF file
  Kill PdfFile
 
  On Error Resume Next
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

The line .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False being the one that all of a sudden is showing an error despite months of it working?

Q2 contains the name. Which is a concat of "Subrange Audit and the date". (Thought it might be this as the date has changed to the new year? But it doesn't seem to have a difference. And "Data" is the tab it PDFs, that tab still exists in the same exact form it did previously.

Any advice?

 

[Fluff]

What error do you get?
Also you missing the period from the beginning of that line.

 

[sanantonio]

What error do you get?
Also you missing the period from the beginning of that line.

Sorry that would help. It's this error:

Yeah period is there in the code but deleted here when copying and pasting.

 

[Fluff]

In that case what is the exact value of Q2?

 

[sanantonio]

 

[Joe4]

View attachment 54911

View attachment 54912

You are using slashes in the date portion of the file name. Slashes are not valid characters for file names.
Maybe try using the text format of "mm-dd-yyyy" instead.

 

[sanantonio]

You are using slashes in the date portion of the file name. Slashes are not valid characters for file names.
Maybe try using the text format of "mm-dd-yyyy" instead.

That was it!

Not sure how it was working previously or how it got changed to / but that's solved it! Thanks muchly

 

[Fluff]

Glad we could help & thanks for the feedback.