I wrote an Applescript to scan through an Apple Mail mailbox and pull out info on each message and put it into an Excel spreadsheet. The problem is that the script finds more messages than are displayed in Apple Mail. For example, in a folder/mailbox called “Chat,” Apple Mail shows 63 messages at the top of the window. The resulting Excel spreadsheet lists over 1000 messages! Many messages with the same ID and message ID are listed multiple times. It’s not a set number of repeats and it happens on many (but not all) mailboxes/folders. Any idea why Applescript finds these duplicates?
-- Make sure to have an Excel worksheet open first
-- Also, select any message in any mailbox prior to running this script
set theRow to 3 -- starting row in Excel
tell application "Mail"
set x to selection
repeat with aMessage in x
set myMailbox to mailbox of aMessage
set theRow to my GetMyMessages(myMailbox, theRow)
end repeat
end tell
on GetMyMessages(mbox, startrow)
tell application "Mail"
try
set myMessageList to (every message in mbox)
repeat with myMessage in myMessageList
set msgID to id of myMessage
set messageID to message id of myMessage
-- many other fields are pulled out but removed for brevity here
tell application "Microsoft Excel"
set myExcelWorkbook to active workbook
set theSheet to active sheet of myExcelWorkbook
set formula of range ("A" & startrow & ":B" & startrow) of theSheet to [msgID, messageID]
end tell
set startrow to startrow + 1
-- add the following log just to watch progress
if (startrow / 10) = (startrow / 10 as integer) then
log ("Processing: " & startrow)
end if
end repeat
on error
log ("Got an error: " & number)
error
end try
end tell
return startrow
end GetMyMessages
I don’t use either Mail or Excel myself, but I’d guess the main problem is you’re looping through a list of selected messages and listing every message in the mailbox of each. If two or more of the selected messages share a mail box, its entire contents will be listed more than once.
It’s generally not considered a good idea to nest ‘tell’ statements to different applications, so you could possibly move the last ‘end tell’ in the handler to just after the “many other fields” comment, although of course it depends on what’s in the code the comment’s replaced.
I don’t know what Excel expects, but the ‘set formula’ line looks wrong. Lists in AppleScript are denoted with braces — {msgID, messageID} — not square brackets — [msgID, messageID].
Thanks very much for your response. Regarding your comments (in reverse order because your first led me to the solution):
#3: Excel is weird (thanks, Microsoft!), and it does expect the data between brackets and not braces.
#2: I appreciate your comments on not nesting tells, but I’m not sure how to do this in this particular scenario. If I add the end tell as you suggest, I will break the repeat loop.
#1: I can see that if I selected more than one message, I would loop through the mailbox for each message selected, but I was always selecting one message. In some mailboxes, the script would not produce any duplicates. But your comment made me look closer at Apple Mail. It has the option to display individual messages or to organize messages by conversation (i.e. thread). For the mailboxes that produced the duplicates, conversation view was turned on. So, when I was selecting one message in those mailboxes, I really was selecting all messages in the conversation thread! If I turn off the Organize by Conversation view, the duplicates are not created.
-- Make sure to have an Excel worksheet open first
-- Also, select any message in any mailbox prior to running this script
set theRow to 3 -- starting row in Excel
tell application "Mail"
set x to selection
repeat with aMessage in x
set myMailbox to mailbox of aMessage
set theRow to my GetMyMessages(myMailbox, theRow)
end repeat
end tell
on GetMyMessages(mbox, startrow)
try
tell application "Mail" to set myMessageList to (every message in mbox)
repeat with myMessage in myMessageList
tell application "Mail"
set msgID to id of myMessage
set messageID to message id of myMessage
-- many other fields are pulled out but removed for brevity here
end tell
tell application "Microsoft Excel"
set myExcelWorkbook to active workbook
set theSheet to active sheet of myExcelWorkbook
set formula of range ("A" & startrow & ":B" & startrow) of theSheet to [msgID, messageID]
end tell
set startrow to startrow + 1
-- add the following log just to watch progress
if (startrow / 10) = (startrow / 10 as integer) then
log ("Processing: " & startrow)
end if
end repeat
on error
log ("Got an error: " & number)
error
end try
return startrow
end GetMyMessages
The script above exports properties of Mail messages to Excel. But very slow…
I wrote here improved script to perform this task:
property theRow : 3 -- starting row in Excel
tell application "Mail"
activate
set theSelection to selection
repeat while theSelection is {}
set theSelection to selection
display notification "Select some message in Mail.app, please"
delay 6
end repeat
my GetMyMessages(mailbox of item 1 of theSelection)
end tell
on GetMyMessages(mbox)
tell application "Mail"
set msgIDs to id of every message of mbox
set messageIDs to message id of every message in mbox
end tell
tell application "Microsoft Excel"
activate
make new document
tell active sheet of active workbook
set horizontal alignment of column "$A:$A" to horizontal align center
set column width of column "$B:$B" to 130.0
repeat with i from 1 to count msgIDs
set r to theRow + i - 1
set {msgID, messageID} to {item i of msgIDs, item i of messageIDs}
set formula of range ("A" & r & ":B" & r) to [msgID, messageID]
end repeat
end tell
end tell
end GetMyMessages
Here is a simple script which I use to clean my mailboxes.
For some messages, there were seven copies of the same item.
Mostly during the late six months I received some messages three times.
I discovered that when I noticed that the flag unread remained when I closed a message window after reading it.
(*
Apply a colored flag to every item listed in a Mail's message viewer.
Select one single entry in the message viewer.
Execute this script.
*)
tell application "Mail"
set _sel to selection
set nbMsgs to (count _sel)
-- Sometimes nbMsgs is greater than one.
if nbMsgs > 1 then
try
tell me to say "replicate" -- using "Alex"
repeat with i from nbMsgs to 2 by -1
-- get each extraneous copy
set _msg to item i of _sel
log (get source of _msg)
-- delete it
delete _msg
end repeat
end try
end if
-- clears the flag of the remaining item
set flag index of item 1 of _sel to -1
end tell
log nbMsgs -- displays the original count of "replicates"
Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) dimanche 6 décembre 2020 15:30:47