I wrote the following script having come across this article: AppleScript finds the same message multiple times.
set {theIDs, theMessageIDs} to {{}, {}}
tell application "Mail"
repeat with anAccount in (accounts)
repeat with aMailBox in (mailboxes of anAccount)
repeat with aMessage in (messages of aMailBox)
set anID to id of aMessage
set aMessageID to message id of aMessage
if anID is not in theIDs then
set end of theIDs to anID
set end of theMessageIDs to aMessageID
end if
end repeat
end repeat
end repeat
end tell
tell application "Microsoft Excel"
set myExcelWorkbook to (make new workbook)
set aSheet to active sheet of myExcelWorkbook
set startRow to 3
repeat with i from 1 to (count theIDs)
set anID to item i of theIDs
set aMessageID to item i of theMessageIDs
set formula of range ("A" & startRow & ":B" & startRow) of aSheet to [anID, aMessageID]
set startRow to startRow + 1
end repeat
end tell
To get rid of the loop in the tell block targeted at Excel…
set {theIDs, theMessageInfo} to {{}, {}}
tell application "Mail"
repeat with anAccount in (accounts)
repeat with aMailBox in (mailboxes of anAccount)
repeat with aMessage in (messages of aMailBox)
set anID to id of aMessage
if theIDs does not contain anID then
set end of theIDs to anID
set end of theMessageInfo to {anID, message id of aMessage}
end if
end repeat
end repeat
end repeat
end tell
tell application "Microsoft Excel"
set myExcelWorkbook to (make new workbook)
set aSheet to active sheet of myExcelWorkbook
set startRow to 3
set value of range ("A" & startRow & ":B" & (startRow + (count of theMessageInfo) - 1)) of aSheet to theMessageInfo
end tell
Since the value of an Excel range is represented in AppleScript by a nested list of lists (each inner list being one row), you can simply create that structure in the loop where you fetch all the IDs and then blast it to the spreadsheet in one fell swoop rather than having to loop through all that data again and target Excel with AppleEvent after AppleEvent. On my machine, just looping through the messages of one mail account took nearly 6 minutes for 3444 messages, so doing that for multiple accounts and then having to re-loop through them all to put them into Excel would probably be a nightmare.
(It would be even faster and easier to do this if we could outsource the handling of duplicates to Excel using the remove duplicates command, but I couldn’t get it to work without throwing up a GUI dialog to select the columns that contain dupes. Oh well.)
Yes, you are right. The repeat loop is not needed a second time, and your script is a clear improvement. But I’m still completely dissatisfied. I will try to improve speed significantly and provide this best solution.
UPDATE.
The following script is much faster than the 2 scripts above:
use AppleScript version "2.4"
use framework "Foundation"
use scripting additions
property NSArray : a reference to current application's class "NSArray"
property NSOrderedSet : a reference to current application's class "NSOrderedSet"
-- GET QUICKLY ALL IDs (nested structure)
tell application "Mail"
repeat with anAccount in (accounts)
set MessagesIDs to id of every message of every mailbox of anAccount
set IDs to message id of every message of every mailbox of anAccount
end repeat
end tell
-- CONVERT NESTED LISTS TO SIMPLE LISTS
set IDs to ((NSArray's arrayWithArray:(IDs))'s valueForKeyPath:("@unionOfArrays.self")) as list
set MessagesIDs to ((NSArray's arrayWithArray:(MessagesIDs))'s valueForKeyPath:("@unionOfArrays.self")) as list
-- REMOVE ID DUPLICATES
set aSet to NSOrderedSet's orderedSetWithArray:IDs
set IDs to (aSet's array()) as list
set aCount to count IDs
set aSet to NSOrderedSet's orderedSetWithArray:MessagesIDs
set MessagesIDs to (aSet's array()) as list
-- BUILD STRUCTURE TO USE WITH EXCEL
set theMessageInfo to my combineLists({MessagesIDs, IDs})
-- EXPORT TO EXCEL
tell application "Microsoft Excel"
set aSheet to active sheet of (make new workbook)
set startRow to 3
set value of range ("A" & startRow & ":B" & (startRow + aCount - 1)) of aSheet to theMessageInfo
end tell
on combineLists(listOfLists)
script foo --> speed-up processing
property theList : listOfLists
end script
set nl to {}
repeat with i from 1 to count foo's theList's item 1
set nl's end to {}
repeat with x from 1 to count foo's theList
set end of nl's item -1 to foo's theList's item x's item i
end repeat
end repeat
nl
end combineLists