bhsoundman
Board Regular
- Joined
- Jul 17, 2010
- Messages
- 64
- Office Version
- 365
- Platform
- MacOS
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
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
VBA Code:
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
