PDF attaches with one file name, but not another v2

[bhsoundman]

Sorry about my previous post on the same topic, but I can't delete or edit that one, so I'm starting a new one.

I’m stuck. I’ve got this VBA script that will save worksheet to pdf, then attach it to a newly created draft email via Mac Script. Everything works as intended IF the pdf file named in the 'pdfFileName = "Carl.pdf" line. If I change the file name to 'pdfFileName = "Dave.pdf" (or any other name) I get an invalid procedure error. In both cases, the pdf is correctly saved in the correct path, just “carl” will attach the file & “dave” will not. So, I know the files both exist where they should, so they should both work. I’ve tried a ton of variations and have been trying suggestion from AI. Any insight would be appreciated. I’ve attached a truncated version if the VBA & Apple Script. Thanks in advance

Sub PDFEMAIL()
    Dim ws As Worksheet
    Dim pdfFilePath As String
    Dim pdfFileName As String
    Dim pdfFolder As String
    Dim subject As String
    Dim bodyText As String
    Dim macScriptStr As String
    Dim i As Long
    Dim emailAddresses As String
    Dim emailArray() As String
    Dim pdfFilePathForScript As String

    ' Use the active worksheet
    Set ws = ActiveSheet
pdfFolder = ""
pdfFileName = ""

    ' Set folder path on the desktop
                        pdfFolder = "/Users/prodcutionservicesinternational-23/Desktop/AA2025/"        'THIS WORKS
'pdfFolder = "/Users/prodcutionservicesinternational-23/Desktop/CrewCallSheet/"

                                'pdfFileName = "Karl.pdf"        'THIS Does not
                pdfFileName = "Carl.pdf"        'THIS WORKS
                'pdfFileName = "Dave.pdf"
                                'pdfFileName = "TestCarol123.pdf"

    ' Replace any illegal characters in the file name
    pdfFileName = Replace(pdfFileName, "/", "") ' Remove slashes
  '  Debug.Print pdfFileName
    pdfFileName = Replace(pdfFileName, ":", "") ' Remove colons
  '  Debug.Print pdfFileName
    pdfFileName = Replace(pdfFileName, "|", "") ' Remove pipes
  '  Debug.Print pdfFileName
   pdfFileName = Replace(pdfFileName, " ", "") ' Remove spaces
       pdfFileName = Trim(pdfFileName)


    ' Build the full file path
    pdfFilePath = pdfFolder & pdfFileName


    Debug.Print "PDF File Path: " & pdfFilePath

    ' Export active sheet as PDF

ws.ExportAsFixedFormat Type:=xlTypePDF, _
                       FileName:=pdfFilePath, _
                       Quality:=xlQualityStandard, _
                       IncludeDocProperties:=True, _
                       IgnorePrintAreas:=False, _
                       OpenAfterPublish:=False

' After exporting PDF, confirm its existence:
If Dir(pdfFilePath) = "" Then
    MsgBox "PDF file was not created: " & pdfFilePath, vbExclamation
    Exit Sub
Else
    MsgBox "PDF Created Successfully: " & pdfFilePath
End If

    ' Email details
    subject = ws.Range("C6").Value & " | Crew Call | " & ws.Name

    ' Initialize emailAddresses string
    emailAddresses = ""

    ' Loop through Job_Crew_Array to collect email addresses
    For i = LBound(Job_Crew_Array, 1) To UBound(Job_Crew_Array, 1)
        emailAddresses = emailAddresses & Job_Crew_Array(i, 2) & "; "
    Next i

    ' Remove trailing semicolon and space
    If Len(emailAddresses) > 0 Then
        emailAddresses = Left(emailAddresses, Len(emailAddresses) - 2)
    End If

    ' Split the email addresses into an array
   emailArray = Split(emailAddresses, "; ")

    ' Convert file path for AppleScript
pdfFilePathForScript = "POSIX file " & Chr(34) & Replace(pdfFilePath, " ", "\\ ") & Chr(34)

    ' Construct the AppleScript
    macScriptStr = "tell application ""Microsoft Outlook""" & vbNewLine & _
                   "set newMessage to make new outgoing message with properties {subject:""" & subject & """, content:""" & bodyText & """}" & vbNewLine & _
                   "tell newMessage" & vbNewLine

    ' Add BCC recipients dynamically
    For i = LBound(emailArray) To UBound(emailArray)
       If Trim(emailArray(i)) <> "" Then
            macScriptStr = macScriptStr & _
              "make new bcc recipient with properties {email address:{address:""" & Trim(emailArray(i)) & """}}" & vbNewLine
        End If
    Next i
    
Debug.Print macScriptStr
Debug.Print pdfFileName
    ' Add static BCC recipients
    macScriptStr = macScriptStr & _
                   "make new bcc recipient with properties {email address:{address:""bhart@psiiusa.com""}}" & vbNewLine & _
                   "make new bcc recipient with properties {email address:{address:""nwhittemore@psiiusa.com""}}" & vbNewLine

    ' Attach the PDF file
    macScriptStr = macScriptStr & _
                   "make new attachment with properties {file:" & pdfFilePathForScript & "}" & vbNewLine & _
                   "end tell" & vbNewLine & _
                   "open newMessage" & vbNewLine & _
                   "activate" & vbNewLine & _
                   "end tell"

' Run the Mac Script
On Error Resume Next
MacScript macScriptStr
If Err.Number <> 0 Then
    MsgBox "Error executing AppleScript: " & Err.Description & vbCrLf & _
           "PDF File Path: " & pdfFilePath & vbCrLf & _
           "Script: " & vbCrLf & macScriptStr, vbExclamation
End If
On Error GoTo 0

End Sub

 

[Peter_SSs]

Sorry about my previous post on the same topic, but I can't delete or edit that one, so I'm starting a new one.

I have deleted the other post but in such a circumstance in future, you should click the 'Report' link at the bottom left of the unwanted post and explain that you would like it deleted and why.

 

[myall_blues]

Where exactly does the error occur - on what line?
I can't test the MacScript part and I don't know what your jobcrew array looks like, but it works ok for me up to the creation of the PDF file.

 

[Peter_SSs]

Reminder:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: PDF attaches with one file name, but not another
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.