Saturday, July 4, 2020

#1 2020-05-17 07:08:25 am

wayland
Member
From:: Nha Trang
Registered: 2020-05-13
Posts: 30

How to use AppleScript to extract the email addresses of all senders

How would I use AppleScript to extract all the email addresses of all the senders in the currently selected mailbox in macOS mail?

I only want the list of email addresses, not the names.

Offline

 

#2 2020-05-17 07:47:57 am

Marc Anthony
Member
From:: Dallas, TX
Registered: 2006-04-27
Posts: 949

Re: How to use AppleScript to extract the email addresses of all senders

Hi and welcome to the forum. Mail has two extract commands in its dictionary for obtaining sender info. A caveat is that Mail must be active and not minimized for this to work.

Applescript:

set senderURLlist to {}
tell application "Mail" to repeat with senderComplex in (get (get selection)'s item 1's mailbox's messages's sender)
   set senderURLlist's end to extract address from senderComplex
end repeat
senderURLlist

Offline

 

#3 2020-05-17 08:00:15 am

wayland
Member
From:: Nha Trang
Registered: 2020-05-13
Posts: 30

Re: How to use AppleScript to extract the email addresses of all senders

Thank you for that Marc Anthony. I see the resulting output is in comma separated values enclosed in curly brackets like this:

{"email1@address.com","email2@address.com"}

Is there any way I can get the output as line separated values, so that I can paste an entire column into a spreadsheet without the quotation marks?

I guess I can first paste into numbers, then transpose, then find and replace all quotation marks.

Last edited by wayland (2020-05-17 08:03:08 am)

Offline

 

#4 2020-05-17 08:39:38 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4533

Re: How to use AppleScript to extract the email addresses of all senders

No need for an extraneous application.

Applescript:

-- your original code supposed to return a list of mail addresses
set listOfAddresses to {"email1@address.com", "email2@address.com"}
set theAddresses to my recolle(listOfAddresses, linefeed)
set the clipboard to theAddresses as «class utf8» --

#=====

on recolle(l, d)
   local oTIDs, t
   set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
   set t to l as text
   set AppleScript's text item delimiters to oTIDs
   return t
end recolle

#=====

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) dimanche 17 mai 2020 16:39:19

Offline

 

#5 2020-05-17 12:53:23 pm

wayland
Member
From:: Nha Trang
Registered: 2020-05-13
Posts: 30

Re: How to use AppleScript to extract the email addresses of all senders

Thank you Ivan,

I combined Marc's script and your script and it copied the list of email addresses to the clipboard! Thank you!

The script looks like this:

set senderURLlist to {}
tell application "Mail" to repeat with senderComplex in (get (get selection)'s item 1's mailbox's messages's sender)
    set senderURLlist's end to extract address from senderComplex
end repeat
senderURLlist

set listOfAddresses to senderURLlist

set theAddresses to my recolle(listOfAddresses, linefeed)
set the clipboard to theAddresses as «class utf8» --

#=====

on recolle(l, d)
    local oTIDs, t
    set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
    set t to l as text
    set AppleScript's text item delimiters to oTIDs
    return t
end recolle

#=====

Armed with this list, I do further processing in Numbers with Automator, but I wonder if there is a more elegant way of completing this task because with Automator, it's recording my mouse clicks and keystrokes, but sometimes, clicks and keystrokes don't register.

What I'm actually trying to do is to give students credit for doing their homework that they have emailed me and I keep track of their grades in Numbers. If their email address was in the mailbox, then I give them a checkmark for that assignment in a Numbers Spreadsheet.

What I would normally do is:

First create a spreadsheet with a column that has the emails from the previous script. (Spreadsheet1)

Then I would open another spreadsheet(Spreadsheet2) with my entire student list. It would have a column of all students email addresses and a rightmost column called "Assignment 1" with a Checkbox(Boolean Value)

Then I would use Automator to record myself:

copy the first email address from Spreadsheet1
Command-Tilde to switch to Spreadsheet2
Command-F to find email address
Esc to get rid of search box so focus is on the the email cell
Tab to get out of edit mode on the cell
Command-Right Arrow to move focus to the rightmost cell - the column with the checkbox
SpaceBar to check the box(Boolean Value set to True)
Command-Tilde to switch back to Spreadsheet1
Down Arrow to move down to the next email address cell.
Stop Recording
Add Loop to repeat for all the emails.

It's kind of crude and sometimes, clicks don't register, and keystrokes don't register when running the workflow. I wonder if this can be accomplished more elegantly with pure AppleScript?

If the sender email address exists in the Mail mailbox, then the Numbers spreadsheet with the email address in one of it's columns get's a checkmark in the rightmost column. Repeat for all email addresses on the list.

Offline

 

#6 2020-05-17 02:11:31 pm

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4533

Re: How to use AppleScript to extract the email addresses of all senders

The first step would be to store the list of mail addresses in a text file then open it with Numbers.
Here I face a serious problem, most of the time the values embedded in the text file are spread among several contiguous cels.
I never understood what is ruling this odd behavior and I don't know if the problem strikes upon the late version of Numbers (which I can't run on my machine).

Applescript:

-- your original code supposed to return a list of mail addresses
set listOfAddresses to {"email1@address.com", "email2@address.com"}
set theAddresses to my recolle(listOfAddresses, linefeed)
set bareName to "myAddresses"
set targetFile to ((path to desktop as string) & bareName & ".txt") as «class furl»
my writeto(targetFile, theAddresses, «class utf8», false)

tell application "Numbers"
   activate
   if exists window bareName then close window bareName
   open targetFile
end tell

#=====

on recolle(l, d)
   local oTIDs, t
   set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, d}
   set t to l as text
   set AppleScript's text item delimiters to oTIDs
   return t
end recolle

#=====
(*
Handler borrowed to Regulus6633 - http://macscripter.net/viewtopic.php?id=36861
*)

on writeto(targetFile, theData, dataType, apendData)
   -- targetFile is the path to the file you want to write
   -- theData is the data you want in the file.
   -- dataType is the data type of theData and it can be text, list, record etc.
   -- apendData is true to append theData to the end of the current contents of the file or false to overwrite it
   try
       set targetFile to targetFile as «class furl»
       set openFile to open for access targetFile with write permission
       if not apendData then set eof openFile to 0
       write theData to openFile starting at eof as dataType
       close access openFile
       return true
   on error
       try
           close access file targetFile
       end try
       return false
   end try
end writeto

#=====

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) dimanche 17 mai 2020 22:11:16

Offline

 

#7 2020-05-31 11:53:37 pm

wayland
Member
From:: Nha Trang
Registered: 2020-05-13
Posts: 30

Re: How to use AppleScript to extract the email addresses of all senders

Thank you Yvan.

I was able to solve the problem without storing the information into a text file. The script will retrieve all the sender's email addresses from the currently selected mailbox, then search for a Numbers document for these email addresses. If the email address is found, then set the rightmost cell of the same row to true (checkmark)

Here's the latest version of my script, I know it can be improved to look cleaner and be more efficient, but it works for now:

Applescript:


#Extract Email Addresses from a Mailbox
set roughList to {}
tell application "Mail" to repeat with senderComplex in (get (get selection)'s item 1's mailbox's messages's sender)
   set roughList's end to extract address from senderComplex
end repeat

#Remove Duplicates
set emailList to {}
set duplicateEmailList to {}

repeat with i from 1 to count of items of roughList
   if item i of roughList is not in emailList then
       set emailList to emailList & item i of roughList
   else
       set end of duplicateEmailList to item i of roughList
   end if
end repeat

#Extract tables 1 to 4 from Numbers document
tell application "Numbers"
   set table1 to the value of the cells of table 1 of sheet 1 of document 1
   set table2 to the value of the cells of table 1 of sheet 2 of document 1
   set table3 to the value of the cells of table 1 of sheet 3 of document 1
   set table4 to the value of the cells of table 1 of sheet 4 of document 1
end tell

#Search tables 1 to 4 for email address in emailList and set checkbox to true
on indexOf(theItem, theList) -- credits Emmanuel Levy
   set text item delimiters to return
   set theList to return & theList & return
   set text item delimiters to {""}
   try
       -1 + (count (paragraphs of (text 1 thru (offset of (return & theItem & return) in theList) of theList)))
   on error
       0
   end try
end indexOf

set emailIndex1 to 0
set emailIndex2 to 0
set emailIndex3 to 0
set emailIndex4 to 0

set combinedTables to table1 & table2 & table3 & table4
set emailAddressesNotChecked to {}

repeat with email in emailList
   if combinedTables does not contain email then set end of emailAddressNotChecked to email
   if table1 contains email then set emailIndex1 to indexOf(email, table1)
   if emailIndex1 is greater than 0 then tell application "Numbers"
       tell document 1 to tell sheet 1 to tell table 1 to set rowAddress to cell emailIndex1's row's address
       tell document 1 to tell sheet 1 to tell table 1 to set the lastCell to the last cell of row rowAddress
       tell document 1 to tell sheet 1 to tell table 1 to set the value of the lastCell to true
   end tell
   set emailIndex1 to 0
   if table2 contains email then set emailIndex2 to indexOf(email, table2)
   if emailIndex2 is greater than 0 then tell application "Numbers"
       tell document 1 to tell sheet 2 to tell table 1 to set rowAddress to cell emailIndex2's row's address
       tell document 1 to tell sheet 2 to tell table 1 to set the lastCell to the last cell of row rowAddress
       tell document 1 to tell sheet 2 to tell table 1 to set the value of the lastCell to true
   end tell
   set emailIndex2 to 0
   if table3 contains email then set emailIndex3 to indexOf(email, table3)
   if emailIndex3 is greater than 0 then tell application "Numbers"
       tell document 1 to tell sheet 3 to tell table 1 to set rowAddress to cell emailIndex3's row's address
       tell document 1 to tell sheet 3 to tell table 1 to set the lastCell to the last cell of row rowAddress
       tell document 1 to tell sheet 3 to tell table 1 to set the value of the lastCell to true
   end tell
   set emailIndex3 to 0
   if table4 contains email then set emailIndex4 to indexOf(email, table4)
   if emailIndex4 is greater than 0 then tell application "Numbers"
       tell document 1 to tell sheet 4 to tell table 1 to set rowAddress to cell emailIndex4's row's address
       tell document 1 to tell sheet 4 to tell table 1 to set the lastCell to the last cell of row rowAddress
       tell document 1 to tell sheet 4 to tell table 1 to set the value of the lastCell to true
   end tell
   set emailIndex4 to 0
   
end repeat

Model: MacBook
AppleScript: 2.7
Browser: Safari 605.1.15
Operating System: macOS 10.14

Last edited by wayland (2020-06-03 09:15:21 am)

Offline

 

#8 2020-05-31 11:55:24 pm

wayland
Member
From:: Nha Trang
Registered: 2020-05-13
Posts: 30

Re: How to use AppleScript to extract the email addresses of all senders

How do you guys get your code into a box like that?

Offline

 

#9 2020-06-01 02:40:31 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4533

Re: How to use AppleScript to extract the email addresses of all senders

Click the dedicated button named "Applescript" to insert automatically the correct tags.

You used [code] and [/code] which are used inn other sites.

Edit you message by replacing the word "code" by the word "applescript" in your tags and your message will be displayed correctly.


Below is a cleaned version. (I hope that I broke nothing)

Applescript:

#Extract Email Addresses from a Mailbox
set roughList to {}
tell application "Mail" to repeat with senderComplex in (get (get selection)'s item 1's mailbox's messages's sender)
   set roughList's end to extract address from senderComplex
end repeat

#Remove Duplicates
set emailList to {}
set duplicateEmailList to {}

repeat with i from 1 to count items of roughList
   if item i of roughList is not in emailList then
       set emailList to emailList & item i of roughList
   else
       set end of duplicateEmailList to item i of roughList
   end if
end repeat

#Extract tables 1 to 4 from Numbers document
tell application "Numbers" to tell document 1
   set table1 to the value of the cells of table 1 of sheet 1
   set table2 to the value of the cells of table 1 of sheet 2
   set table3 to the value of the cells of table 1 of sheet 3
   set table4 to the value of the cells of table 1 of sheet 4
end tell


#Search tables 1 to 4 for email address in emailList and set checkbox to true
set emailIndex1 to 0
set emailIndex2 to 0
set emailIndex3 to 0
set emailIndex4 to 0

set combinedTables to table1 & table2 & table3 & table4
set emailAddressesNotChecked to {}

repeat with email in emailList
   if combinedTables does not contain email then set end of emailAddressNotChecked to email
   if table1 contains email then set emailIndex1 to indexOf(email, table1)
   if emailIndex1 > 0 then tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
       set rowAddress to cell emailIndex1's row's address
       set lastCell to last cell of row rowAddress
       set value of lastCell to true
   end tell
   set emailIndex1 to 0
   if table2 contains email then set emailIndex2 to indexOf(email, table2)
   if emailIndex2 > 0 then tell application "Numbers" to tell document 1 to tell sheet 2 to tell table 1
       set rowAddress to cell emailIndex2's row's address
       set lastCell to last cell of row rowAddress
       set value of lastCell to true
   end tell
   set emailIndex2 to 0
   if table3 contains email then set emailIndex3 to indexOf(email, table3)
   if emailIndex3 > 0 then tell application "Numbers" to tell document 1 to tell sheet 3 to tell table 1
       set rowAddress to cell emailIndex3's row's address
       set lastCell to last cell of row rowAddress
       set value of the lastCell to true
   end tell
   set emailIndex3 to 0
   if table4 contains email then set emailIndex4 to indexOf(email, table4)
   if emailIndex4 > 0 then tell application "Numbers" to tell document 1 to tell sheet 4 to tell table 1
       set rowAddress to cell emailIndex4's row's address
       set lastCell to last cell of row rowAddress
       set value of lastCell to true
   end tell
   set emailIndex4 to 0
end repeat

-- better to put the handler(s) out of the main code

on indexOf(theItem, theList) -- credits Emmanuel Levy
   set text item delimiters to return
   set theList to return & theList & return
   set text item delimiters to {""}
   try
       -1 + (count (paragraphs of (text 1 thru (offset of (return & theItem & return) in theList) of theList)))
   on error
       0
   end try
end indexOf

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) lundi 1 juin 2020 10:40:24

Last edited by Yvan Koenig (2020-06-01 02:58:38 am)

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)