Export Messages From Mail to Excel With AppleScript

apple script iconMy Eldest Daughter was sat at home today when I got home from work copy-pasting emails from her yahoo mailbox into an excel sheet. She needed the email in this format for some part of her work and she was on email 157 of 656 and also on her 6th hour. The spreadsheet needed to comprise of Date Sent, From, Subject and message. So I walked in and said that

why did nt she just export from yahoo to excel ?

Well easier said than done…
After 10 mins research I discovered there was no easy way it seems of doing this..
I came across an article on Apple Support Forums that lead me the right way..
Thanks to ‘Barney-15E’ if he still online as the article is dated back in 2010.

Firstly I setup her Yahoo account on my mac mail as an iMap account.

Downloaded all the messages she needed.

Opened up AppleScript Editor (once I sussed out what it was)

Apple Script

Now this was new to me as I had never even looked at Applescript because as quoted on the net :

You can fit the people that love applescript in a small car or even a bike..

Anyway thats about as much as I can tell you about applescript..

I now have the email stored in a mailbox inside my INBOX.

This is the script I then used to get the results into Microsoft Excel:

tell application "Microsoft Excel"
	set LinkRemoval to make new workbook
	set theSheet to active sheet of LinkRemoval
	set formula of range "D1" of theSheet to "Message"
	set formula of range "C1" of theSheet to "Subject"
	set formula of range "B1" of theSheet to "From"
	set formula of range "A1" of theSheet to "Date"
end tell

tell application "Mail"
	set theRow to 2
	set theAccount to "Sales"
	get account theAccount
	set theMessages to messages of inbox
	repeat with aMessage in theMessages
		my SetDate(date received of aMessage, theRow, theSheet)
		my SetFrom(sender 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 SetFrom(theSender, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "B" & 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 "C" & 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

Run the script and after a minute (depending on amount of messages) your excel sheet is complete.

I will try to explain this now…

The first 9 lines open up Microsoft Excel and create a new worksheet..
they make theSheet the active sheet that is open.
Then make the headings for A1-D1 on this worksheet.

Next we open up the mail program.
We set the row in the worksheet to row 2.
We set theAccount to the mailbox we want to get the email from (in this case Accounts as listed in your mail app)
We now get the account of theAccount and set theMessages to the messages in the INBOX.
(So we are getting all messages from the account Sales in my INBOX)
Now we setup a repeat until complete loop.
We set the Date to A
We set the From to B
We set the Subject to C
We set the Message to D
To set each variable we call a function which puts that variable into the excel spreadsheet.
We add 1 to the loop( Basically move the row down to the next line in the worksheet)
Once completed we stop.

I hope you enjoy this…