Help! VBA save to pdf - file name issues

Hi All

New here and hoping for some advice please...

I have a macro created which automatically saves a pdf version of an excel sheet to a specified folder and then opens outlook to send.

I've managed to get it all working well, except the file name is pulling from the tab name rather than cell Q1 in the sheet I need it to. I've tried various different combinations but keep getting a syntax error further down so have completely hit the wall now

Offending lines are in bold

Any advice you could give would be fab! Worth noting I've never had much to do with VBA - this is my first go so apologies if I lack understanding!

My code is as per below:

Sub SaveAsPDFandSend()
Dim xSht As Worksheet
Dim xFileDlg As FileDialog
Dim xFolder As String
Dim xYesorNo As Integer
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim xUsedRng As Range
Dim xPath As String
Set xSht = ActiveSheet
xPath = "\\CUKNSFSIPS001\InsulationUK\Sales\05. Customer Pricing\02. Customer Support\01. Pending Support Applications\" 'here "workshet to pdf" is the destination folder to save the pdf files
xFolder = xPath + "\" + xSht.Name + ".pdf"
If Len(Dir(xFolder)) > 0 Then
xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
vbYesNo + vbQuestion, "File Exists")
On Error Resume Next
If xYesorNo = vbYes Then
Kill xFolder
Else
MsgBox "if you don't overwrite the existing PDF, I can't continue." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
Exit Sub
End If
End If

Set xUsedRng = xSht.UsedRange
If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
'Save as PDF file
xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard

'Create Outlook email
Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
'Create message
On Error Resume Next
With xEmailObj
.To = Range("Q3")
.CC = Range("Q4") & "; " & Range("Q5")
.Subject = Range("Q6")
.Body = "Hello " & Range("Q7") & vbNewLine & _
"" & vbNewLine & _
"Please see attached Support Contract " & Range("Q8") & " for " & Range("Q9") & ", " & Range("Q10") & "." & vbNewLine & _
"" & vbNewLine & _
"Kind Regards"
.Attachments.Add xFolder
.Display 'Use only during debugging ##############################
'.Send 'Uncomment to send e-mail ##############################
End With
Else
MsgBox "The active worksheet cannot be blank"
Exit Sub
End If
End Sub

Hi cubangt

Thank you for taking the time to reply - unfortunately that didn't work

I have tried changing the following (in bold) but I then get a syntax error below (in red)

Sub SaveAsPDFandSend()
Dim xSht As Worksheet
Dim xFileDlg As FileDialog
Dim xFolder As String
Dim xYesorNo As Integer
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim xUsedRng As Range
Dim xPath As String
Set xSht = ActiveSheet
xPath = "\\CUKNSFSIPS001\InsulationUK\Sales\05. Customer Pricing\02. Customer Support\01. Pending Support Applications\" 'here "workshet to pdf" is the destination folder to save the pdf files
xFolder = xPath & "\" & Range("Q1") & ".pdf"
If Len(Dir(xFolder)) > 0 Then
xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
vbYesNo + vbQuestion, "File Exists")
On Error Resume Next
If xYesorNo = vbYes Then
Kill xFolder
Else
MsgBox "if you don't overwrite the existing PDF, I can't continue." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
Exit Sub
End If
End If

Set xUsedRng = xSht.UsedRange
If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
'Save as PDF file
xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard

'Create Outlook email
Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
'Create message
On Error Resume Next
With xEmailObj
.To = Range("Q3")
.CC = Range("Q4") & "; " & Range("Q5")
.Subject = Range("Q6")
.Body = "Hello " & Range("Q7") & vbNewLine & _
"" & vbNewLine & _
"Please see attached Support Contract " & Range("Q8") & " for " & Range("Q9") & ", " & Range("Q10") & "." & vbNewLine & _
"" & vbNewLine & _
"Kind Regards"
.Attachments.Add xFolder
.Display 'Use only during debugging ##############################
'.Send 'Uncomment to send e-mail ##############################
End With
Else
MsgBox "The active worksheet cannot be blank"
Exit Sub
End If
End Sub

Thanks cubangt

Still not working unfortunately - returning a error again. (Assuming I am following your instructions correctly - complete new to VBA)

Sub SaveAsPDFandSend()
Dim xSht As Worksheet
Dim xFileDlg As FileDialog
Dim xFolder As String
Dim xYesorNo As Integer
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim xUsedRng As Range
Dim xPath As String
Set xSht = ActiveSheet
xPath = "\\CUKNSFSIPS001\InsulationUK\Sales\05. Customer Pricing\02. Customer Support\01. Pending Support Applications\" 'here "workshet to pdf" is the destination folder to save the pdf files
xFolder = xPath + "\" & Range("Q1").Value & ".pdf"
If Len(Dir(xFolder)) > 0 Then
xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
vbYesNo + vbQuestion, "File Exists")
On Error Resume Next
If xYesorNo = vbYes Then
Kill xFolder
Else
MsgBox "if you don't overwrite the existing PDF, I can't continue." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
Exit Sub
End If
End If

Set xUsedRng = xSht.UsedRange
If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
'Save as PDF file
xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard

'Create Outlook email
Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
'Create message
On Error Resume Next
With xEmailObj
.To = Range("Q3")
.CC = Range("Q4") & "; " & Range("Q5")
.Subject = Range("Q6")
.Body = "Hello " & Range("Q7") & vbNewLine & _
"" & vbNewLine & _
"Please see attached Support Contract " & Range("Q8") & " for " & Range("Q9") & ", " & Range("Q10") & "." & vbNewLine & _
"" & vbNewLine & _
"Kind Regards"
.Attachments.Add xFolder
.Display 'Use only during debugging ##############################
'.Send 'Uncomment to send e-mail ##############################
End With
Else
MsgBox "The active worksheet cannot be blank"
Exit Sub
End If
End Sub

You have to build an existing drive:\path\ and a legal filename.ext. Likely, your Q1 is empty/blank or a date. If a date, use Format() to fix that. e.g.

Please Login or Register  to view this content.

Hi Kenneth

Thank you for the reply.

"Q1" holds a formula that concatenates a load of data elsewhere in my workbook to create a file name. The template returns the following text currently: Contract Ref: xyz - BELGRADE INSULATIONS LTD LEEDS (LEEDS) - abc

I just tried your suggestion above but I still get a runtime error (see picture).Attachment 732245

When trying to 'debug' it points me to the line in the following image. Capture3.PNG

Your help is most appreciated. This is all well and truly out of my comfort zone so apologies if I'm being a bit dopey.

That looks like a legal base filename.

#5 compiles just fine for me. In VBE, Debug > Compile.

Have you tried the unc path manually? I would copy that, \\CUKNSFSIPS001\InsulationUK\Sales\05. Customer Pricing\02. Customer Support\01. Pending Support Applications, and then Win+R and paste it. File Explorer should show the folder.

You can put this in and Run it to help debug.

Please Login or Register  to view this content.

After the Run, in the VBE View menu, select Immediate Window, if you don't have it docked. I use it all the time. The result from Debug.Print will be put there after the run.

You can also try saving it to some other folder to see if the UNC path is causing the run-time issue. If the Win+R shows the folder, we can assign a virtual drive to it and see if that helps.

I think I’ve now managed to add an example spreadsheet - password 123

As you can hopefully see, when using the sheet name as a file name the macro works.

But every change I’ve tried to get this file name to generate from cellQ1 has failed this far.

Really appreciate the responses

You have a colon character in your base filename. That is an illegal character...

Use Replace() to replace illegal characters like that. e.g.

Please Login or Register  to view this content.

Oh my gosh!!! Yes...slightly embarrassed I didn't see that little terror hiding there!!

Thank you so much for all your help. I've updated it and all working ok

Extremely grateful!!