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

Written By

Kenny Turner

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.

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 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
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
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
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
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
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…

More Tutorials

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.

Copyright 2007 - 2024 southcoastweb is a brand of DSM Design.