Excel 2004 MacScript issue

I’m working on a Mac with Excel 2004.
To import data from the application Mail, I have written an AppleScript and put it in the file NewRecipientStr.scpt

I don’t want to hard code NewRecipientStr into VB, it would make editing/testing more difficult.
So, the plan was to:

  1. Have Excel VBA’s MacScript command run a hard coded AppleScript that will read the text of NewRecipientStr.scpt
  2. change some of that text (in lieu of passing arguments)
  3. use VBA’s MacScript to run the modified text, returning the data that I seek

I’ve tried that approach on simpler scripts and it worked.

There is a problem. When I run the test VBA code below, returnedString gets the text of the file as expected, but MacScript(returnedString) returns “error”.
I conclude that the script is running (no applescript compile errors), but an applescript/Mail error is triggering the script’s error handling.

However, when I call the script directly from the file, it returns the expected value, with no Applescript/Mail error.

I have tried replaced Chr(10) with Chr(13) in returnedScript and tried removing Chr(9) from returnedScript with no success.

Does anyone have any thoughts? Thank you.

(Cross posted on http://www.mrexcel.com/forum/showthread.php?p=2017679#post2017679 to approach the issue from both ends.)

This is the test VBA

[code]Sub test()
Dim returnedScript As String

returnedScript = MacScript(ScriptToRun)
    Rem returnedScript is NewRecipientStr.scpt script as text

MsgBox returnedScript
    Rem check, yes the text of the script has been returned
    
MsgBox MacScript(returnedScript)
    Rem returns "error"
    
MsgBox MacScript("Macintosh HD:Users:merickson:Desktop:MailAutomation:NewRecipientStr.scpt")
    Rem returns expected value

End Sub

Function ScriptToRun() As String
ScriptToRun = ScriptToRun & vbCr & “tell application ““Script Editor”” "
ScriptToRun = ScriptToRun & vbCr & " set myDoc to open file ““Macintosh HD:Users:merickson:Desktop:MailAutomation:NewRecipientStr.scpt”” "
ScriptToRun = ScriptToRun & vbCr & " set myText to text of myDoc”
ScriptToRun = ScriptToRun & vbCr & " return myText"
ScriptToRun = ScriptToRun & vbCr & “end tell”
End Function[/code]
This is NewRecipientStr.scpt

--AppleScript
-- given a mailbox returns a delimited string of message properties
-- id, subject, date received, sender's name, sender's address, {to recipients}, {ccRecipients}, {bCCRecipients}

global btwNA, btwRecipients, btwMessages, btwproperties
set btwNA to ","
set btwRecipients to ":"
set btwMessages to linefeed
set btwproperties to "--" & linefeed

set retVal to "error"

try
    tell application "Mail"
        set myBox to mailbox "inStaffReportFolder" of mailbox "09WEFrecords"
        set myMsgs to a reference to (messages of myBox)
    end tell
    
    set sendersNames to {}
    set sendersAddresses to {}
    set toRecptval to {}
    set ccRecptval to {}
    set bccRecptval to {}
    
    using terms from application "Mail"
        set AppleScript's text item delimiters to {btwMessages}
        tell myMsgs
            set headerData to {id as string, subject as string, date received as string}
        end tell
        set AppleScript's text item delimiters to {btwproperties}
        set headerData to headerData as string
        repeat with oneMsg in myMsgs
            copy (extract name from (sender of oneMsg)) to end of sendersNames
            copy (extract address from (sender of oneMsg)) to end of sendersAddresses
            
            copy oneMessagesRecipientStr(to recipients of oneMsg) to the end of toRecptval
            copy oneMessagesRecipientStr(cc recipients of oneMsg) to the end of ccRecptval
            copy oneMessagesRecipientStr(bcc recipients of oneMsg) to the end of bccRecptval
        end repeat
    end using terms from
    
    set AppleScript's text item delimiters to {btwMessages}
    set sendersNamesStr to (sendersNames as string)
    set sendersAddressesStr to (sendersAddresses as string)
    set toRecptStr to (toRecptval as string)
    set ccRecptStr to (ccRecptval as string)
    set bccRecptStr to (bccRecptval as string)
    
    set AppleScript's text item delimiters to {btwproperties}
    set yval to {headerData, sendersNamesStr, sendersAddressesStr, toRecptStr, ccRecptStr, bccRecptStr} as string

    ]set retVal to yval
end try
return retVal

on oneMessagesRecipientStr(someRecipients)
    set retVal to {}
    tell application "Mail"
        repeat with oneRecipient in someRecipients
            tell oneRecipient
                copy ("" & name & btwNA & address) to end of retVal
            end tell
        end repeat
    end tell
    set AppleScript's text item delimiters to {btwRecipients}
    return retVal as string
end oneMessagesRecipientStr

I found the problem!!!

Apparently MacScript does not work properly with apple script handlers. Replacing calls from the main routine to the handler oneMessagesRecipientStr with identical code, the problem was fixed.