Export Selection of Messages From Mail to Excel With AppleScript - Updated

Scripting Forums AppleScript | Mac OS X
, ,
#1

Hi
I don’t usually create scripts, so I’m struggling to create what I need and that it would work. I need a script that exports the header and body text-only content (no attachments) from Mail into an Excel spreadsheet. All the emails are located in one email account, but the selected emails would include conversation threads.
I can select the messages myself in Mail - there are over 3000 emails, and automating this task seems far more efficient than creating this list manually.

I’ve tried this, but it’s not working - it’s getting stuck on the SplitEmail and the “<” delimiter - I don’t need the email split but unsure how to remove it or adjust the SplitEmail so it will work.

set dataLst to {{“SenderAddress”, “SenderName”, “Mailbox folder”, “RecipientAddresses”, “Date”, “Time”, “Subject”, “Content”}}

tell application “Mail”
repeat with aMsg in items of (get selection)
tell aMsg
set senderNameAddr to my splitEmail(sender)
set senderAddr to item 2 of senderNameAddr
set senderName to item 1 of senderNameAddr
# Why the heck must ‘of aMsg’ be added to the end of this line?!??
set nameMailbox to name of mailbox of aMsg
set toRecipients to to recipients
set theRecipientAddrs to address of item 1 of toRecipients
repeat with i from 2 to (count of toRecipients)
set theRecipientAddrs to theRecipientAddrs & " " & address of (get to recipient i) as rich text
end repeat
set ccRecipients to cc recipients
repeat with i from 1 to (count of ccRecipients)
set theRecipientAddrs to theRecipientAddrs & " " & address of (get cc recipient i) as rich text
end repeat
set bccRecipients to bcc recipients
repeat with i from 1 to (count of bccRecipients)
set theRecipientAddrs to theRecipientAddrs & " " & address of (get bcc recipient i) as rich text
end repeat
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 {senderAddr, senderName, nameMailbox, theRecipientAddrs, msgDate, msgTime, msgSubj, 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

to dateFormat(aDateString) → yyyy-mm-dd
set {year:y, month:m, day:d} to date aDateString
tell (y * 10000 + m * 100 + d) as string to text 1 thru 4 & “-” & text 5 thru 6 & “-” & text 7 thru 8
end dateFormat

to splitEmail(nameAddress)
set text item delimiters to “<”
tell nameAddress
set theName to text item 1
set theAddress to text 1 thru -2 of text item 2
end tell
return {theName, theAddress}
end splitEmail

Any help you can provide would be incredibly appreciated.

Thanks!

#2

First, to enter code and have it formatted properly, put a line with nothing but three backticks above and another below the code, like so:

```
your code here
```

Some general thoughts on the script…

I think that the ‘of aMsg’ is required because which mailbox is too ambiguous otherwise. You could also use ‘of it’.

You might consider using ‘string’ instead of ‘rich text’ when coercing the various addresses. The results would be treated as plain text then.

I’m a little fuzzy on this but you might also use ‘set’ instead of ‘copy’ when assigning msgLst, ie set end of dataLst to msgLst. There can be implications to using ‘copy’ this way.

As regards the splitEmail handler… you might want to have both resulting strings exclude the last character. Otherwise, each ‘name’ will have a trailing space.

Do your email addresses all follow this format? In my own tests they do, so the handler works fine for me.

Sales Team <nosales@example.com>

It’s conceivable that some non-internet addresses (eg exchange) might not. Without the ‘<’, any such address will trigger an error. If there is a different separator then you could adjust the delimiters accordingly. Otherwise, you may have to adjust the script.

Finally, this is a bit random but you can also adjust the column widths with the script. To see the actually message body, you would need to.

	set width of last column to 480
	set width of column "date" to 64
	set width of column "time" to 40

FWIW, there is another recent post that discusses opening a pre-formatted numbers spreadsheet to dump some data in. I think it was in response to handling csv data but it would likely server in this context as well.

#3

What are you doing to do with the message body in Numbers? I highly doubt that you can easily get data with end-of-line characters as CSV into any spreadsheet app.

I have some AppleScripts on my website for exporting emails: AppleScripts

I don’t like to mention my app Mail Archiver here on the forum because the forum is about AppleScript. But if you want to do reports or any sort of data manipulation on message bodies then it’s much easier to do when you have a database like Mail Archiver has. You could - for instance - do SQL queries in Valentina Studio or export to Excel. As I said this all depends on what your goal is.

#4

Hi
Sorry, I’ll copy any further code like you said. The error you mentioned is what’s happening. Not all emails have the same the same “<” delimiter. When I tried to add a
…
or ""
…
to the same line, I got an error
I don’t need the SplitEmail the whole thing can be together but I couldn’t figure out how to replace it in the code I have

#5

Hi. The code I found that worked was numbers. When I switched it to Excel, it didn’t work. I don’t necessarily need the message body; it could be truncated. Critical information is in the header: date, time, to, cc, from, and subject.
The emails span three years involving specific @salestem.com domains;
there are over three thousand, and I need to log them. I’m no scripter or even a data specialist, so I’m somewhat lost as I attempt to make it easier to meet my deadline. I’d use something else if I knew it would give me the list I need as the end product. Thanks again.

#6

Have a look at How to print a list of selected emails in AppleMail . This should give you a start.

#7

Thanks

I reviewed it and tried it alone and couldn’t make it work, but I incorporated some of it into what I’m working on for better or worse. It makes the Excel spreadsheet but gets stuck on the date right away.

tell application "Microsoft Excel"
	
	set newDoc to make new document
	tell active sheet of newDoc
		set LinkRemoval to make new workbook
		set theSheet to active sheet of LinkRemoval
		set formula of range "G1" of theSheet to "Message"
		set formula of range "F1" of theSheet to "Subject"
		set formula of range "E1" of theSheet to "cc Recipients"
		set formula of range "D1" of theSheet to "Sender"
		set formula of range "C1" of theSheet to "Recipients"
		set formula of range "B1" of theSheet to "Time"
		set formula of range "A1" of theSheet to "Date"
	end tell
end tell

tell application "Mail"
	set theRow to 2
	set theAccount to ".Mac"
	get account theAccount
	set SelectedMails to selection
	repeat with aMessage in items of (get selection)
		my SetDate(date of aMessage, theRow, theSheet)
		my SetTime(time of aMessage, theRow, theSheet)
		my SetSender(sender of aMessage, theRow, theSheet)
		my SetRecipients(recipient of aMessage, theRow, theSheet)
		my SetccRecipients(cc recipient of aMessage, theRow, theSheet)
		my SetSubject(subject of aMessage, theRow, theSheet)
		my SetMessage(content of aMessage, theRow, theSheet)
		set theRow to theRow + 1
	end repeat
end tell
on SetDate(theDate, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "A" & theRow
		set formula of range theRange of theSheet to theDate
	end tell
end SetDate
on SetTime(theTime, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "B" & theRow
		set formula of range theRange of theSheet to theTime
		set time to time string of date
	end tell
end SetTime

on SetTo(theSender, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "C" & theRow
		set formula of range theRange of theSheet to theRecipient
	end tell
end SetTo

on SetFrom(theRecipient, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "D" & 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 "E" & theRow
		set formula of range theRange of theSheet to theSubject
	end tell
end SetSubject
on SetMessage(theMessage, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "D" & theRow
		set formula of range theRange of theSheet to theMessage
	end tell
end SetMessage

to theDate(aDateString) --> yyyy-mm-dd
	set {year:y, month:m, day:d} to date aDateString
	tell (y * 10000 + m * 100 + d) as string to text 1 thru 4 & "-" & text 5 thru 6 & "-" & text 7 thru 8
	
end theDate
#8

You need to use date received and not date. Date received also gives the time. There was some mixup between from and to. If you develop a script do it line by line. Otherwise, you get confused where the error comes from.

I only did the first 2 fields for the script:

tell application "Microsoft Excel"
	
	set newDoc to make new document
	tell active sheet of newDoc
		set LinkRemoval to make new workbook
		set theSheet to active sheet of LinkRemoval
		set formula of range "B1" of theSheet to "Sender"
		set formula of range "A1" of theSheet to "Date"
	end tell
end tell


tell application "Mail"
	set theRow to 2
	set SelectedMails to selection
	repeat with aMessage in SelectedMails
		my SetDate(date received of aMessage, theRow, theSheet)
		my SetSender(sender of aMessage as string, theRow, theSheet)
		set theRow to theRow + 1
	end repeat
end tell
on SetDate(theDate, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "A" & theRow
		set formula of range theRange of theSheet to theDate
	end tell
end SetDate

on SetSender(theSender, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "B" & theRow
		set formula of range theRange of theSheet to theSender
	end tell
end SetSender

to theDate(aDateString) --> yyyy-mm-dd
	set {year:y, month:m, day:d} to date aDateString
	tell (y * 10000 + m * 100 + d) as string to text 1 thru 4 & "-" & text 5 thru 6 & "-" & text 7 thru 8
end theDate
#9

Some more work done:

tell application "Microsoft Excel"
	
	set newDoc to make new document
	tell active sheet of newDoc
		set LinkRemoval to make new workbook
		set theSheet to active sheet of LinkRemoval
		set formula of range "B1" of theSheet to "Sender"
		set formula of range "A1" of theSheet to "Date"
	end tell
end tell

tell application "Mail"
	set theRow to 2
	set SelectedMails to selection
	repeat with aMessage in SelectedMails
		my SetField(date received of aMessage, theRow, 1, theSheet)
		my SetField(sender of aMessage as string, theRow, 2, theSheet)
		--my SetField(recipient of aMessage as string, theRow, 3, theSheet)
		--my SetField(subject of aMessage as string, theRow, 3, theSheet)
		--my SetField(content of aMessage as string, theRow, 3, theSheet)
		set theRow to theRow + 1
	end repeat
end tell

on SetField(theField, theRow, theColumn, theSheet)
	tell application "Microsoft Excel"
		set theRange to my getletter(theColumn) & theRow
		set formula of range theRange of theSheet to theField
	end tell
end SetField

to theDate(aDateString) --> yyyy-mm-dd
	set {year:y, month:m, day:d} to date aDateString
	tell (y * 10000 + m * 100 + d) as string to text 1 thru 4 & "-" & text 5 thru 6 & "-" & text 7 thru 8
end theDate

on getletter(theNumber)
	if theNumber = 1 then
		return "A"
	else if theNumber = 2 then
		return "B"
	else if theNumber = 3 then
		return "C"
	else if theNumber = 4 then
		return "D"
	else if theNumber = 5 then
		return "E"
	else if theNumber = 6 then
		return "F"
	else if theNumber = 7 then
		return "G"
	end if
end getletter
1 Like