If Dir(FullName) <> vbNull String Then Giving Runtime Error 52

[StedDOOM]

Howdy!

This VBA code has been running strong for a year, then all of a sudden I got a call today that it's started giving an error, and I'm really not sure what to do to fix it, as I essentially backwards engineered some other code to get this to work and I have no actual clue what I'm doing.

In a nutshell, I have a workbook with an email macro that, when clicked, auto-creates an email for the user with the to line and subject filled in basis the worksheet, and then includes a .pdf of the worksheet.

Sub EmailVesselLog()
Dim pdfName As String
Dim Sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim DataCell As Range
Dim myrange As String
Dim PathFileName As String

Set Sht = Worksheets("Vessel Log")
Set StartCell = Range("A1")
Set DataCell = Range("B27")

    LastRow = Sht.Cells(Sht.Rows.Count, DataCell.Column).End(xlUp).Row
    LastColumn = Sht.Cells(StartCell.Row, Sht.Columns.Count).End(xlToLeft).Column
pdfName = Range("AA2").Text
FullName = ThisWorkbook.Path & "\" & pdfName & ".pdf"

Sht.Range(StartCell, Sht.Cells(LastRow, LastColumn)).Select

With ActiveSheet.PageSetup
    .PrintArea = Selection.Address
        .Orientation = xlPortrait
        .Zoom = False
            .FitToPagesTall = False
            .FitToPagesWide = 1
End With

If Dir(FullName) <> vbNullString Then

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & pdfName & " - " & Format(Now, "mm.dd.yyyy hh mm") & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
Else

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & pdfName & " - " & Format(Now, "mm.dd.yyyy hh mm") & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

End If

 Dim xCell As Range
    Dim xRg As Range
    Dim xEmailAddr As String
    Dim xTxt As String
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Range("R5")
    If xRg Is Nothing Then Exit Sub
    Set xOTApp = CreateObject("Outlook.Application")
    For Each xCell In xRg
        If xCell.Value Like "*@*" Then
            If xEmailAddr = "" Then
                xEmailAddr = xCell.Value
            Else
                xEmailAddr = xEmailAddr & ";" & xCell.Value
            End If
        End If
    Next
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = xEmailAddr
.CC = ""
.Subject = Range("AA1").Value
.HTMLBody = ""
PathFileName = ThisWorkbook.Path & "\" & pdfName & " - " & Format(Now, "mm.dd.yyyy hh mm") & ".pdf"
.Attachments.Add PathFileName
.Display 'or use .Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing

Kill ThisWorkbook.Path & "\" & pdfName & " - " & Format(Now, "mm.dd.yyyy hh mm") & ".pdf"

End Sub

However, as of today, I've started getting a run-time error '52': Bad file name or number, and debug highlights the row:

If Dir(FullName) <> vbNullString Then

Any help for how to patch this up would be greatly appreciated, as I'm really not too experienced with all this so I'm not even really sure where to start in fixing it.

 

[Vogateer]

I've used that kind of code before, but never used vbNullString with it. You might try it with:

If Dir(FullName) <> "" Then

 

[StedDOOM]

I've used that kind of code before, but never used vbNullString with it. You might try it with:

If Dir(FullName) <> "" Then

Thanks for the quick response! Unfortunately, that gives the same runtime error.

 

[Vogateer]

What's the value of "FullName" when that code executes?
You can place this line just above the "If Dir(FullName <> "" Then" statement:

MsbBox "FullName value is: " & FullName

And you'll get a pop-up that shows what the value is.

 

[Vogateer]

What's the value of "FullName" when that code executes?
You can place this line just above the "If Dir(FullName <> "" Then" statement:

MsbBox "FullName value is: " & FullName

And you'll get a pop-up that shows what the value is.

Ugh, I had a typo, it should be a 'g' instead of a 'b' in 'Msgbox:

MsgBox "FullName value is: " & FullName

 

[StedDOOM]

Ugh, I had a typo, it should be a 'g' instead of a 'b' in 'Msgbox:

MsgBox "FullName value is: " & FullName

So, I think I MAY have found the issue.

Firstly, the Fullname is defined in the code:

pdfName = Range("AA2").Text
FullName = ThisWorkbook.Path & "\" & pdfName & ".pdf"

And the MsgBox did help confirm that.

So, the people using this spreadsheet are SUPPOSED to pull this workbook from Teams as a template, then immediately save a copy into a shared drive. If you don't save it first, it gives FullName a wonky file name based off the path it's deriving from Teams, which is a hyperlink. The MsgBox was giving:

"FullName value is:
https://[companyname].sharepoint.com/teams/CrewLeaders-MainDock/Shared Documents/Main Dock/#2 Vessel Loading Logs/[AA2 value].pdf

I'm assuming the issue is that the hyperlink-as-a-name format is throwing a wrench into the works when using the file saved in Teams.

So, I saved one to the shared drive and accessed via Windows explorer, it started giving a different error, but that was just because they had made a data entry error causing a "/" to go into the file name. Once I fixed that, it seems to be in working order, and the MsgBox was giving a normal path you'd expect to see.

What's odd is I was fairly sure it used to work when I was using the macro straight out of the file saved in Teams, but either way, the users should be saving a copy to the shared drive every time, so that should circumvent this issue.

Thanks so much for the help!