Wednesday, June 26, 2019

#1 2018-12-18 09:20:39 pm

Galaxar
Member
Registered: 2018-12-18
Posts: 3

Applescript finds same message multiple times

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?

Applescript:


-- 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

Offline

 

#2 2018-12-19 03:47:48 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4884

Re: Applescript finds same message multiple times

Hi. Welcome to MacScripter.

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].


NG

Offline

 

#3 2018-12-20 12:08:55 am

Galaxar
Member
Registered: 2018-12-18
Posts: 3

Re: Applescript finds same message multiple times

Hi Nigel,

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.

Thank you very much!

Mike

Offline

 

#4 2018-12-26 01:58:21 pm

t.spoon
Member
From:: BFE, Massachusetts
Registered: 2013-01-13
Posts: 397

Re: Applescript finds same message multiple times

re: #2

Nested tells removed

Applescript:



-- 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


Hackintosh built February, 2012 |  Mac OS Sierra
GIGABYTE GA-Z68X-UD3H-B3 | Core i5 2500k | 16 GB DDR3 | GIGABYTE Geforce 1050 TI 4GB
250 GB Samsung 850 EVO | 4 TB RAID
Dell Ultrasharp U3011 | Dell Ultrasharp 2007FPb

Offline

 

#5 2019-01-02 02:11:57 pm

Galaxar
Member
Registered: 2018-12-18
Posts: 3

Re: Applescript finds same message multiple times

Thanks, t.spoon!

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)