Export Messages From Mail to Excel With AppleScript

exportmessagesphoto2

My 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 she not just export from yahoo to Microsoft 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)

exportmessagesphoto1

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.

exportmessagesphoto2

DSM Design LTD - Brand Logo

Contact Us

Contact DSM Design for all of your development questions and queries. You can get in touch via our online form or call us on 01202 048079.

Contact Us

Latest News

View all Articles