Apple

paste_cells_into_Excel_and_Process(cellText, rowCounter)

end tell

I thought I'd already tried that but obviously not correctly :-)

I'm very interested if anyone has any philosophical, style guide or optimisation type answers and/or preferences to the question of when to use tell blocks. And how to use tell blocks in nested or unnested ways (pref for parallel or series usage).

Begin forwarded message:

From: Alastair Leith <email@hidden>
Subject: Scope riddle I can't seem to solve. (Probably very easy to solve for an experienced coder)
Date: 19 March 2014 10:24:20 pm AEDT
To: asu <email@hidden>

I have a script that makes calls to Mail.app to get some emails and to Excel.app to process the contents of these emails.

Basically it processes the currently selected emails into three groups and then processes the first group only so far (all inside a tell Mail block). Last bit of code in loop is telling Excel to paste in a range of cells and copy the resulting row of formula values to lower down the sheet in the next available blank row.

I wrote the basic functional skeleton to get the script going:

# Declare some workbook and worksheet pointers
tell application "Microsoft Excel"
set database_workbook to workbook "Contacts Database (for script testing).xls"
activate database_workbook
set database_sheet to sheet "database" of database_workbook
set from_tp_sheet to sheet "TP from mailout"
set from_website_sheet to sheet "TP from website"
end tell

tell application "Mail"
try
# initialise lists
set notes to {""}
set website_emails to {}
set mailout_emails to {}
set other_emails to {}
set email_selection to (get selection)
# loop thru all selected email messages and sort into three groups
repeat with a_msg in email_selection
# Determine if source is website or email
set email_subject to subject of a_msg
set email_source to {""}
if the (count of words of email_subject) > 2 then
set email_source to words 1 thru 3 of email_subject
end if
if email_source = {"Enquiry", "from", "Website"} then
copy a_msg to the end of website_emails
log ("Website: " & email_subject)
else if email_source = {"Subscribe", "Foo", "Bar"} then
copy a_msg to the end of mailout_emails
log ("TP Mailout: " & email_subject)
else
-- email was sent to email@hidden but unknown provenience
copy a_msg to the end of other_emails
end if
end repeat

# Process group 1 of emails (from website forms)
repeat with i from 1 to (count website_emails)
set theText to content of item i of website_emails
set their_name to words 2 thru -1 of paragraph 9 of theText -- for later functionality
set comments to paragraph -5 of theText -- for later functionality
set subscriber to paragraph 5 of theText -- for later functionality

# parse paras (3rd to last paras) into Excel savvy cell range object
set pasteText to (paragraphs from paragraph 5 to the last paragraph) of theText
set cellText to {}
repeat with p in pasteText
set cellText's end to {p's contents}
end repeat
repeat 50 - (count cellText) times
set cellText's end to {""}
end repeat
set r to "A1:A" & (count cellText)
# ====================================================================
tell application "Microsoft Excel" --
tell database_workbook --
activate --
tell from_website_sheet --
tell range ("A1:A50") --
set value to cellText --
end tell --
-- set value of range "A52" to first_name --
set results_row to value of range "A52:AZ52" --
set the value of range ("A" & (54 + i) & ":AZ" & (54 + i)) to results_row --
end tell --
end tell --
end tell --
# ====================================================================
end repeat
on error the error_message number the error_number
tell application "Script Debugger Demo"
display dialog "Error: " & the error_number & ". " & the error_message buttons {"OK"} default button 1
end tell
end try
end tell

In order to structure the script away from linear code to subroutines I wish to remove code inside the pair of # ======== comments which calls Excel and pastes in the cells, copies the resulting values from a row of formulas on Row 50 to a blank row (which is is determined by the incrementing i property)

Problem is when I pull out the tell Excel code into new handler, i.e.
on paste_cells_into_Excel_and_Process(cellText, rowCounter)
tell application "Microsoft Excel"
tell database_workbook
activate
tell from_website_sheet
tell range ("A1:A50")
set value to cellText
-- delay 1
end tell
-- set value of range "A52" to first_name
set results_row to value of range "A52:AZ52"
set the value of range ("A" & (rowCounter) & ":AZ" & (rowCounter)) to results_row
end tell
end tell
end tell
end paste_cells_into_Excel_and_Process

And add appropriate call line replacing removed code:
paste_cells_into_Excel_and_Process(cellText, 50 + i)

When I run the script I get the error (you all know what's coming):
Error: -1708. Mail got an error: Can’t continue paste_cells_into_Excel_and_Process.

I don't know how to direct my script to my new paste_cells_into_Excel_and_Process handler (since Mail has no such handler obviously).

I've tried a few different ways of moving the tell scoping around but I'm not there yet. Do I need to pull the top level tell application "Mail" statements and locate them just where I reference Mail application methods to get email properties and end them again quickly (quite a few places but I could copy the text as string to break the dependancies). Problem is I want to pull more subroutines out of this to modularise the code and help with the repetition which will follow when I process other groups of emails and other aspects of content. I need a way to call them no matter what tell application blocks I'm inside.