Help with VBA script please

[indiglo]

I'm in need of some help please and thank you.

I have to submit a timesheet for work every week.
It must be submitted by emailing a pdf of the completed timesheet (complete by hand then scan then email).

In 2020, I re-created the timesheet into a spreadsheet and created a VBA macro so that a single button click would perform 2 functions: (1) export a specified range of cells (print area) into a PDF (2) set filename of the newly created pdf to the value of a specified cell. I really value both functions.

I have been using it successfully for years on Microsoft Excel.
A few months ago the script stopped working on Excel at home (no idea why, I never changed it). This would be the latest M365 version.
My workplace uses Excel 2016, and the script works fine there.

I was hoping I could get some help ammending or re-writing the script so it works on Excel (versions 2016+) across both Windows and MacOS.

The script below is what I used - it was lifted from another site/forum - I can't remember where:

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Option Explicit
Sub SaveAsPDF()
    Dim wSheet As Worksheet
    Dim vFile As Variant
    Dim sFile As String

    Set wSheet = ActiveSheet
    sFile = Worksheets("Data").Range("F2")
    sFile = ThisWorkbook.Path & "\" & sFile

    vFile = Application.GetSaveAsFilename _
    (InitialFileName:=sFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

    If vFile <> "False" Then
    wSheet.Range("A1:S38").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=vFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    End If
End Sub

The cell with the filename value is held in the "Data" worksheet.
The print area range is held in the "Timesheet" worksheet, which should be set as ActiveSheet.

 

[jolivanes]

Sub How_About_So()
Dim PDF As String
Sheets("Timesheet").PageSetup.PrintArea = "A1:S38"    '<----Change Sheet Name/Print Range as required
PDF = ThisWorkbook.Path & "\" & Sheets("Data").Range("F2").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
End Sub

If you have the email addresses in a sheet, email (with outlook) the PDF file with your macro also.

 

[jolivanes]

Sub Save_As_PDF_And_Email()
Dim PDF As String, MailBody As String
MailBody = "Please see attached."
Sheets("Timesheet").PageSetup.PrintArea = "A1:S38"    '<----Change Sheet Name/Print Range as required
PDF = ThisWorkbook.Path & "\" & Sheets("Data").Range("F2").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
Application.ScreenUpdating = False
With CreateObject("Outlook.Application").CreateItem(0)
    .Display
    .To = Sheets("Timesheet").Range("D2").Value    '<---- Change as required
    .Subject = ""
    .HTMLBody = MailBody & .HTMLBody
    .Attachments.Add PDF
    .Send
End With
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

I guess it did not work. Have not seen a reply.