9 Replies Latest reply: Feb 7, 2014 5:13 AM by Steve Howse
jlantern Level 1 Level 1 (0 points)

Canyou help me, I extracted "from" addresses with the following script:

 

--script begins

property theBoxes : {"mailbox1", "mailbox2", "mailbox3", "mailbox4", "mailbox5", "mailbox6", "mailbox7"}

 

on run

 

          set theMessages to {}

 

          tell application "Microsoft Excel"

set theSpamFile to make new workbook

set theSheet to active sheet of theSpamFile

set formula of range "B1" of theSheet to "Subject"

set formula of range "A1" of theSheet to "From"

          end tell

 

          tell application "Mail"

set theRow to 2

get mailboxes

 

--set theMessages to messages of junk mailbox

repeat with aBox in theBoxes

set theMessages to theMessages & messages of mailbox aBox

end repeat

 

repeat with aMessage in theMessages

my SetFrom(sender of aMessage, theRow, theSheet)

my SetSubject(subject of aMessage, theRow, theSheet)

set theRow to theRow + 1

end repeat

end tell

 

end run

 

on SetFrom(theSender, theRow, theSheet)

          tell application "Microsoft Excel"

set theRange to "A" & theRow

set formula of (range theRange) of theSheet to theSender

          end tell

end SetFrom

 

on SetSubject(theSubject, theRow, theSheet)

          tell application "Microsoft Excel"

set theRange to "B" & theRow

set formula of range theRange of theSheet to theSubject

          end tell

end SetSubject

--script ends

 

Which worked very well.

 

I now want to extract all of the to, cc and BCC emails and changed the above script to:

 

--script begins

property theBoxes : {"RWG Archive", "NRW Archive", "Contacts#Newspaper List", "JOBS R", "JOBS N"}

 

on run

 

          set theMessages to {}

 

          tell application "Microsoft Excel"

set theSpamFile to make new workbook

set theSheet to active sheet of theSpamFile

set formula of range "B1" of theSheet to "Subject"

set formula of range "A1" of theSheet to "Recipients"

          end tell

 

          tell application "Mail"

set theRow to 2

get mailboxes

--set theMessages to messages of junk mailbox

repeat with aBox in theBoxes

set theMessages to theMessages & messages of mailbox aBox

end repeat

 

repeat with aMessage in theMessages

my SetRecipients(recipients of aMessage, theRow, theSheet)

my SetSubject(subject of aMessage, theRow, theSheet)

set theRow to theRow + 1

end repeat

end tell

 

end run

 

on SetRecipients(theRecipients, theRow, theSheet)

          tell application "Microsoft Excel"

set theRange to "A" & theRow

set formula of (range theRange) of theSheet to theRecipients

          end tell

end SetRecipients

 

on SetSubject(theSubject, theRow, theSheet)

          tell application "Microsoft Excel"

set theRange to "B" & theRow

set formula of range theRange of theSheet to theSubject

          end tell

end SetSubject

--script ends

 

It does not work, I get the following error:

 

error "Can’t make «class trcp» 1 of «class mssg» id 135522 of «class mbxp» \"RWG Archive\" of application \"Mail\" into the expected type." number -1700 from «class trcp» 1 of «class mssg» id 135522 of «class mbxp» "RWG Archive"

 

I am not good at this and would appreciate help to get TO, CC and BCC address.

 

Kind Regard

 

 

o


 

 

 

o


Mac OS X (10.7.4), Help!!!!
  • 1. Re: Extracting all email addresses contained in emails.
    Jacques Rioux Level 4 Level 4 (1,960 points)

    Hi,

     

    recipients is a element, to get what you want we must obtain values ​​from the property address of this element

    The result will be a list, this list must be converted to string, otherwise the value of the cell will be the first item in the list of addresses

     

    --------------------------------

    --extract all of the to, cc and BCC emails

     

    set theBoxes to {"Videotron", "Yahoo"} --{"RWG Archive", "NRW Archive", "Contacts#Newspaper List", "JOBS R", "JOBS N"}

    set theRow to 2

    tell application "Microsoft Excel"

          set theSpamFile to make new workbook

          set theSheet to active sheet of theSpamFile

          set formula of range "B1" of theSheet to "Subject"

          set formula of range "A1" of theSheet to "Recipients"

    end tell

    tell application "Mail"

          repeat with aBox in theBoxes

                repeat with aMessage in (get messages of mailbox aBox)

                      tell aMessage

                            my setRecipientsAndSubject(address of its recipients, theRow, theSheet, subject)

                      end tell

                      set theRow to theRow + 1

                end repeat

          end repeat

    end tell

     

    on setRecipientsAndSubject(theRecipients, theRow, theSheet, theSubject)

          set TID to text item delimiters

          set text item delimiters to ", " -- separator (comma and space)

          set theRecipients to theRecipients as text -- convert AppleScript's list  to string --> (address separated by comma)

          set text item delimiters to TID

     

          tell application "Microsoft Excel"

                set value of range ("A" & theRow & ":B" & theRow) of theSheet to {theRecipients, theSubject}

          end tell

    end setRecipientsAndSubject

  • 2. Re: Extracting all email addresses contained in emails.
    jlantern Level 1 Level 1 (0 points)

    Hi Jacques, I very much appreciate the time you have taken to assist me.

     

    I copied to script above.

     

    Each time I run it the same error occurs.

     

    It begins, opens up excel, extracts email addresses until line 4087 [excel]. The spript result is as follows:

    "error "Microsoft Excel got an error: Connection is invalid." number -609". Each time.

     

    Can you offer any advise?

     

    Regards

  • 3. Re: Extracting all email addresses contained in emails.
    Jacques Rioux Level 4 Level 4 (1,960 points)

    Hi,

     

     

    The error number -609 means that the application will not responds.

    I don't know why, because I tested in an infinite loop, I have no error, I stop the script after the row 8000.

     

    Another solution is to fill the sheet only one time.

    Try this :

    ---------------------------------------------------------------

    set theBoxes to {"RWG Archive", "NRW Archive", "Contacts#Newspaper List", "JOBS R", "JOBS N"}

    set theRow to 2

    tell application "Microsoft Excel"

         set theSpamFile to make new workbook

         set theSheet to active sheet --of theSpamFile

         set formula of range "B1" of theSheet to "Subject"

         set formula of range "A1" of theSheet to "Recipients"

    end tell

    set allValues to my getRecipientsAndSubject(theBoxes)

     

    tell application "Microsoft Excel" to set value of range ("A" & theRow & ":B" & (count allValues)) of theSheet to allValues

     

    on getRecipientsAndSubject(L)

         script o

              property tValues : {}

         end script

         tell application "Mail"

              repeat with aBox in L

                   repeat with aMessage in (get messages of mailbox aBox)

                        tell aMessage

                             set end of o's tValues to {my convertList(address of its recipients), subject}

                        end tell

                   end repeat

              end repeat

         end tell

         return o's tValues

    end getRecipientsAndSubject

     

    on convertList(theRecipients)

         set tid to text item delimiters

         set text item delimiters to ", " -- separator (comma and space)

         set theRecipients to theRecipients as text -- convert AppleScript's list  to string --> (address separated by comma)

         set text item delimiters to tid

         return theRecipients

    end convertList

  • 4. Re: Extracting all email addresses contained in emails.
    jlantern Level 1 Level 1 (0 points)

    Hi, You script worked very well and I thank you a lot for assisting!!

  • 5. Re: Extracting all email addresses contained in emails.
    Jongware Level 2 Level 2 (265 points)

    jlantern wrote:

     

    > set theSheet to active sheet of theSpamFile

     

    I thank you a lot for assisting!!

     

    Yeah, many millions of people are going to be so greatful for this.

  • 6. Re: Extracting all email addresses contained in emails.
    danpaluska Level 1 Level 1 (0 points)

    is there a way to do this without excel?

     

    how about just into text edit? or openoffice?

  • 7. Re: Extracting all email addresses contained in emails.
    Jacques Rioux Level 4 Level 4 (1,960 points)

    danpaluska wrote:

     

    is there a way to do this without excel?

     

     

    Yes, these are just lists that contains text.

    Just delete everything about "Excel", convert the allValues lists to text.

    It is already an example (in the script) to convert a list into text.

     

    This discussion is already solved, if you have a new question, start a new thread.

  • 8. Re: Extracting all email addresses contained in emails.
    Ju.Les Level 1 Level 1 (0 points)

    Hi guys, I'm trying to use this script to export all senders email addresses from various mailboxes in Mac mail. Each time I run the script, Applescript tells me: Mail got an error: Can’t get mailbox "Name of the mailbox". What can I do? Is the name of the mailbox the name in the description field? Please help.

  • 9. Re: Extracting all email addresses contained in emails.
    Steve Howse Level 1 Level 1 (0 points)

    Hey ther.

     

    I love the idea of this script - seems exactly what I've been looking for. I'm not sure what to put in the "mailbox" line though. I'm running two IMAP accounts from my mac mail - iCloud and Gmail.

     

    If you know what I should put in these lines it would be most appreciated!

     

    Thanks