Hi All,
I am new to the automator and applescript but I have a requirement to take the selected mail messages and process them into an excel format (with 5 lines of preview) and then subsequent print, or save (or use one of the other excel actions).
I have gotten to the part where I have the mail messages selected → passed them in to my applescript which formats the excel the way I want.
Now I want to return the entire excel object from applescript back to automator so that other actions can be performed on the excel? How can this be acheived - obviously hoping for a solution which does not require saving the file then reopening them later on but just directly passing the excel stream to the output object?
My automator startes with the mail action “get selected mail messages” and passes them into the following applescript;
on ModifyBorder(thisBorder)
tell application “Microsoft Excel”
try
set line style of thisBorder to dash
end try
try
set color index of thisBorder to 3
end try
end tell
return
end ModifyBorder
on run {input, parameters}
set output to {}
set crlf to (ASCII character 13) & (ASCII character 10)
set tabs to (ASCII character 11)
(* Open workbook *)
tell application "Microsoft Excel"
set screen updating to false
set theWorkbook to make new workbook
(*set column width of range "A:C" to 20 characters (columns)*)
set column width of column 1 to 7
set column width of column 2 to 20
set column width of column 3 to 2
set column width of column 4 to 90
(* set up paper in landscape *)
tell page setup object of active sheet
set page orientation to portrait (* landscape *)
set zoom to false
set left footer to "Page &P of &N"
end tell
(* hide grid lines *)
set display gridlines of window 1 to false
end tell
set currentRow to 1
tell application "Mail"
repeat with eachMessage in input
set theContent to content of eachMessage
set theSubject to subject of eachMessage
set theSender to sender of eachMessage
set theDateRecv to date received of eachMessage
set dayString to day of theDateRecv
set monthString to month of theDateRecv as string
set yearString to year of theDateRecv
set shortMonthString to characters 1 thru 3 of monthString
set mailDate to dayString & "-" & shortMonthString & " " & yearString as string
set lineCount to 5
set numbLin to count of paragraphs of theContent
if numbLin is less than 5 then set lineCount to numbLin
set theLine1 to ""
set theLine2 to ""
set theLine3 to ""
set theLine4 to ""
set theLine5 to ""
repeat with i from 1 to lineCount
if i is equal to 1 then set theLine1 to paragraph i of theContent as string
if i is equal to 2 then set theLine2 to paragraph i of theContent as string
if i is equal to 3 then set theLine3 to paragraph i of theContent as string
if i is equal to 4 then set theLine4 to paragraph i of theContent as string
if i is equal to 5 then set theLine5 to paragraph i of theContent as string
end repeat
set theIndent to tabs
set thePreview to theLine1 & crlf & theLine2 & crlf & theLine3 & crlf & theLine4 & crlf & theLine5
(* now add 5 lines of the text to the output beneath the text and indented *)
(* theContent *)
(* now add the lines located to individual columns of excel
Row 1;
Col A(1):Date -> mailDate
Col B(2):From -> theSender
Col C(3):Subject -> theSubject
Row 2;
Col C(3):thePreview *)
tell application "Microsoft Excel"
tell worksheet "Sheet1" of active workbook
(* Apply format to the first row*)
(* Height 20 *)
(* A 7
B 20
c 2
D 90 *)
set currentRow to currentRow + 1
set value of cell 1 of row currentRow to mailDate
set value of cell 2 of row currentRow to theSender
set value of cell 3 of row currentRow to theSubject
set row height of row currentRow to 20
tell font object of row currentRow of active sheet
set {name, font style, font size, strikethrough, superscript, subscript, ¬
outline font, shadow, font color index} to {"Arial Narrow", "Bold", ¬
10, false, false, false, false, false, color index automatic}
end tell
set currentRow to currentRow + 1
set value of cell 4 of row currentRow to thePreview
tell font object of row currentRow of active sheet
set {name, font style, font size, strikethrough, superscript, subscript, ¬
outline font, shadow, font color index} to {"Arial Narrow", "Italic", ¬
10, false, false, false, false, false, color index automatic}
end tell
set currentRow to currentRow + 1
tell row currentRow
set thisBorder to get border which border border bottom
my ModifyBorder(thisBorder)
end tell
set row height of row currentRow to 5
end tell
end tell
end repeat
end tell
tell application "Microsoft Excel"
set screen updating to true
end tell
return output
end run
Any pointers much appreciated
/MiB