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:
- Have Excel VBA’s MacScript command run a hard coded AppleScript that will read the text of NewRecipientStr.scpt
- change some of that text (in lieu of passing arguments)
- 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