Export from Mail to Numbers -almost there - Issues with Recipients & cc Recipients

Scripting Forums AppleScript | Mac OS X
#1

Hi
So, updated from my previous thread - I’m trying to export a selection of emails from Mail to a spreadsheet to create a log.

Thanks to a lot of help from here and the Apple community, I now have it working so long as I don’t add the recipients and the cc recipients; however, I need this detail for creating the log. I’m not sure why it’s not working. Is it because the recipient & cc fields would have multiple addresses? Struggling to fix it. Thanks.

set dataLst to {{"SenderNameAddr", "torecipientsNameAddr", "ccrecipientsNameAddr", "Subject", "Date", "Time", "Content"}}

tell application "Mail"
	repeat with aMsg in items of (get selection)
		tell aMsg
			set senderNameAddr to sender
			set torecipientsNameAddr to to recipients
			set ccrecipientsNameAddr to cc recipients
			set msgSubj to subject
			set msgDate to date received
			set msgTime to time string of msgDate
			set msgDate to my dateFormat(date string of msgDate)
			set msgContent to content
			set msgLst to {senderNameAddr, torecipientsNameAddr, ccrecipientsNameAddr, msgSubj, msgDate, msgTime, msgContent}
			copy msgLst to dataLst's end
		end tell
	end repeat
end tell

tell application "Numbers"
	set newDoc to make new document
	tell table 1 of active sheet of newDoc
		delete column "A" -- remove default Header Column
		set column count to length of item 1 of dataLst
		set row count to (length of dataLst)
		repeat with i from 1 to length of dataLst
			repeat with j from 1 to length of item 1 of dataLst
				set value of cell j of row i to item j of item i of dataLst
			end repeat
		end repeat
	end tell
end tell
#2

There are two issues:

  1. As the plural form suggests to recipients and cc recipients return a list because there can be multiple recipients.
  2. recipients itself is an element, it contains a name and an address property.

A solution is to get the addresses of the recipients and add a handler to flatten the list comma separated

set dataLst to {{"SenderNameAddr", "torecipientsNameAddr", "ccrecipientsNameAddr", "Subject", "Date", "Time", "Content"}}

tell application "Mail"
	repeat with aMsg in items of (get selection)
		tell aMsg
			set senderNameAddr to sender
			set torecipientsNameAddr to my flattenList(address of to recipients)
			set ccrecipientsNameAddr to my flattenList(address of cc recipients)
			set msgSubj to subject
			set msgDate to date received
			set msgTime to time string of msgDate
			set msgDate to my dateFormat(date string of msgDate)
			set msgContent to content
			set msgLst to {senderNameAddr, torecipientsNameAddr, ccrecipientsNameAddr, msgSubj, msgDate, msgTime, msgContent}
			copy msgLst to dataLst's end
		end tell
	end repeat
end tell

tell application "Numbers"
	set newDoc to make new document
	tell table 1 of active sheet of newDoc
		delete column "A" -- remove default Header Column
		set column count to length of item 1 of dataLst
		set row count to (length of dataLst)
		repeat with i from 1 to length of dataLst
			repeat with j from 1 to length of item 1 of dataLst
				set value of cell j of row i to item j of item i of dataLst
			end repeat
		end repeat
	end tell
end tell

on flattenList(theList)
	set {saveTID, text item delimiters} to {text item delimiters, {", "}}
	set theString to theList as text
	set text item delimiters to saveTID
	return theString
end flattenList
#3

Thank you! It didn’t work at first, but as soon as I added the date format back in, it did!

Next question: Is there a way to truncate the content field to a certain number of characters?
Can I set it to a text string so it gets rid of returns?

I can’t tell you how amazing this is and how much work has been saved by this - thank you again!

#4

Yes, you can truncate the content, for example this takes the first 20 characters if the length of the string is greater than 20

set msgContent to content
if (count msgContent) > 20 then set msgContent to text 1 thru 20 of msgContent
#5

Amazing!
You are a true hero & I bow to your genius!

Thank you!