Export Messages From Mail to Excel With AppleScript
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 well into her 6th hour. The spreadsheet needed to comprise Date Sent, From, Subject and Message. So I walked in and said that
Founder of southcoastweb, Passionate about PHP, MySQL, Laravel and Linux Servers.
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 workbook3 set theSheet to active sheet of LinkRemoval4 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 tell910tell application "Mail"11 set theRow to 212 set theAccount to "Sales"13 get account theAccount14 set theMessages to messages of inbox15 repeat with aMessage in theMessages16 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 + 121 end repeat22end tell2324on SetDate(theDate, theRow, theSheet)25 tell application "Microsoft Excel"26 set theRange to "A" & theRow27 set formula of range theRange of theSheet to theDate28 end tell29end SetDate3031on SetFrom(theSender, theRow, theSheet)32 tell application "Microsoft Excel"33 set theRange to "B" & theRow34 set formula of range theRange of theSheet to theSender35 end tell36end SetFrom3738on SetSubject(theSubject, theRow, theSheet)39 tell application "Microsoft Excel"40 set theRange to "C" & theRow41 set formula of range theRange of theSheet to theSubject42 end tell43end SetSubject4445on SetMessage(theMessage, theRow, theSheet)46 tell application "Microsoft Excel"47 set theRange to "D" & theRow48 set formula of range theRange of theSheet to theMessage49 end tell50end 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…
Here are some more tutorials that we think might be helpful for you. Feel free to contact us if there's anything you might need
Automating your service and repository patterns in Laravel with command generators
"Why spend 20 seconds doing something when you can spent 4 hours automating it," goes the proverb. See any professional proverbists around any more? No, because they've all been automated. Also it's fun. Whatever your programming pattern in Laravel, chances are that the php artisan make:x command is going to leave you high and dry on occasion. That's why it can be useful to have your own commands available to cover those gaps. We're going to go with the example of the "Service" pattern below.
Passing through slots in Vue
If you're keeping your Vue component sizes small, there's a good chance you'll need to implement a wrapper component at some point. If you're only utilising the default slot for these, it can be as simple as putting a <slot /> tag inside your wrapper component. However, there's a bit more effort involved if you need to pass through named slots to your base component
How to Create a Simple Button Component in Figma
In this tutorial, we’ll create a simple button using Figma’s built-in component system.
cPanel Setup - File Explorer
Continuing with our cPanel Setup series, this tutorial will give you an overview about setting up ConfigServer Explorer (CSE) onto your WHM powered VPS. This is not an essential plugin, but definitely one that is very handy, and again it is completely free, so why not?
cPanel Setup - Firewall
In this tutorial, which is part of our cPanel Setup series, we will give you a brief overview about setting up ConfigServer Firewall (CSF) onto your WHM powered VPS. This will protect your server from unwanted visitors and attempts at brute forcing onto the server. A firewall to protect your server is a must, and this is probably one of the most commonly used ones, and even better, it is completely free to use.