Hi list, first time caller here
I'm writing a script that takes web-form output that arrives as individual emails.
Much of the email contents is placed into to a range of cells in column A (A1:A50) — or in AS grammar range "$A$1:$A$50" (why are the dollar signs required when scripting?). Then I have a row of cells with formulas on row 52 (and headings on row 51) that does my text parsing etc.
I wish to iterate thru the emails placing the text to the previously mention range and then copying the values (not formulas) of the results in row 52 into to successive rows down the sheet from Row 54 onwards.
I'm not sure if it's best practice to use the clipboard to paste special to get the values from the formulas or not although I'd take any method ATM as I'm grasping at straws to get any headway at all, this AS grammar lottery is a steep learning curve for me.
In pseudo code: repeat i from 1 thru to (count emails) paste some of the contents of (email item i) into sheet range "A1:A50" copy or get row 52 # or range "A52:AZ52" if that's easier/faster/better paste or set the values of row 52 into row (53+i) end repeat
script so far: # 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"
-- set unsubscribe_workbook to workbook "test4.csv" -- activate unsubscribe_workbook -- set unsubscribe_sheet to sheet 1
end tell
tell application "Mail" try 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 --show_Dialog("tester")
# 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 -- email was sent from the link in an HTML mail-out -- show_Dialog("Yes it's from the website") copy a_msg to the end of website_emails log ("Website: " & email_subject) else if email_source = {"Subscribe", "Terra", "Publica"} then -- email was sent from the link in an HTML mail-out -- show_Dialog("No it's from the mailout") copy a_msg to the end of mailout_emails log ("TP Mailout: " & email_subject) else -- show_Dialog("Subject: " & email_subject as string) copy a_msg to the end of other_emails end if
end repeat
log ("total emails: " & (count of website_emails)) repeat with a_msg in website_emails log ("another email") set theText to content of a_msg set their_name to words 2 thru -1 of paragraph 9 of theText set comments to paragraph -5 of theText set subscriber to paragraph 5 of theText
-- parse paras (3rd to last paras) into Excel savy 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) log {r, cellText} -- for test
-- set form_text to theText's paragraphs 4 thru -1 -- this creates data as a list of items, but a list of lists each with single item in each child list is what Excel requires as object notation for the successful set value operation 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 results_row to value of range row "52" 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
Best Alastair
Alastair I Leith
Useful Design 03 9480 5506 0432 889 831 30 Birch St West Preston VIC 3072 Au
|