Export Messages From Mail to Excel With AppleScript

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…

Searching for a solution

I came across an article on Apple Support Forums that lead me the right way… Thanks to ‘Barney-15E’ if he’s still online as the article is dated back in 2010.

Firstly I setup her Yahoo account on my Mac Mail as an IMAP account and then downloaded all the messages that she needed.

AppleScript is apparently a thing

Once I sussed out what this AppleScript thing was, I sought it out and cracked open a new 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 that’s about as much as I can tell you about AppleScript..

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

Importing into Microsoft Excel

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

1tell application "Microsoft Excel"
2	set LinkRemoval to make new workbook
3	set theSheet to active sheet of LinkRemoval
4	set formula of range "D1" of theSheet to "Message"
5	set formula of range "C1" of theSheet to "Subject"
6	set formula of range "B1" of theSheet to "From"
7	set formula of range "A1" of theSheet to "Date"
8end tell
9
10tell application "Mail"
11	set theRow to 2
12	set theAccount to "Sales"
13	get account theAccount
14	set theMessages to messages of inbox
15	repeat with aMessage in theMessages
16		my SetDate(date received of aMessage, theRow, theSheet)
17		my SetFrom(sender of aMessage, theRow, theSheet)
18		my SetSubject(subject of aMessage, theRow, theSheet)
19		my SetMessage(content of aMessage, theRow, theSheet)
20		set theRow to theRow + 1
21	end repeat
22end tell
23
24on SetDate(theDate, theRow, theSheet)
25	tell application "Microsoft Excel"
26		set theRange to "A" & theRow
27		set formula of range theRange of theSheet to theDate
28	end tell
29end SetDate
30
31on SetFrom(theSender, theRow, theSheet)
32	tell application "Microsoft Excel"
33		set theRange to "B" & theRow
34		set formula of range theRange of theSheet to theSender
35	end tell
36end SetFrom
37
38on SetSubject(theSubject, theRow, theSheet)
39	tell application "Microsoft Excel"
40		set theRange to "C" & theRow
41		set formula of range theRange of theSheet to theSubject
42	end tell
43end SetSubject
44
45on SetMessage(theMessage, theRow, theSheet)
46	tell application "Microsoft Excel"
47		set theRange to "D" & theRow
48		set formula of range theRange of theSheet to theMessage
49	end tell
50end SetMessage

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

How it works

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…