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

StedDOOM

New Member
Joined
Jul 15, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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:

VBA Code:
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
I've used that kind of code before, but never used vbNullString with it. You might try it with:

VBA Code:
If Dir(FullName) <> "" Then
 
Upvote 0
I've used that kind of code before, but never used vbNullString with it. You might try it with:

VBA Code:
If Dir(FullName) <> "" Then
Thanks for the quick response! Unfortunately, that gives the same runtime error.
 
Upvote 0
What's the value of "FullName" when that code executes?
You can place this line just above the "If Dir(FullName <> "" Then" statement:

VBA Code:
MsbBox "FullName value is: " & FullName

And you'll get a pop-up that shows what the value is.
 
Upvote 0
What's the value of "FullName" when that code executes?
You can place this line just above the "If Dir(FullName <> "" Then" statement:

VBA Code:
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:

VBA Code:
MsgBox "FullName value is: " & FullName
 
Upvote 1
Solution
Ugh, I had a typo, it should be a 'g' instead of a 'b' in 'Msgbox:

VBA Code:
MsgBox "FullName value is: " & FullName
So, I think I MAY have found the issue.

Firstly, the Fullname is defined in the code:

VBA 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!
 
Upvote 0

Forum statistics

Threads
1,224,465
Messages
6,178,820
Members
452,881
Latest member
motivationgyan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top