Export Messages From Mail to Excel With AppleScript | South Coast Web Design Ltd
South Coast Web Design Ltd
Export Messages From Mail to Excel With AppleScript Feb 11

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…

28 Responses to “Export Messages From Mail to Excel With AppleScript”

JohnAugust 6th, 2013 at 7:08 pm

Great information! I just have one question if you can answer, what if you want to extract specific data from the body of the email and extract to it’s own columns in Excel?

For example:

Name: John Doe

Contact info: 555-555-5555

Email: john.doe@exampleemailxx.net

Another variable 1: More data 1

Another Variable 2: More data 2

Misc info: xxxx xxxx xxxxx xxxx xxxx xxxxx xxxx

If you have a solution for that, that would be greatly appreciated! I’ve been searching online but I cannot find a way to integrate any code into your applescript. (Keep in mind I’m not a programmer, and although I see so many saying applescript is easy, I only need this for a one time solution.) Also, I have a few thousand e-mails in this same configuration to extract to Excel, If you have a solution I’m sure it would benefit many people viewing this page! Thanks again!

Cheers!

Dan LSeptember 21st, 2013 at 3:08 am

That works great for the inbox. Do you know how to do it for other mailboxes? In particular for locally saved mail folders under “on my mac”.

Dan LSeptember 21st, 2013 at 4:41 am

I figured out the answer to my previous question. Here are some examples

set theMessages to messages of mailbox “somefolder/subfolder”
set theMessages to messages of mailbox “somefolder”
set theMessages to messages of mailbox “INBOX” of account “example.com”

Milind LeleSeptember 29th, 2013 at 6:16 pm

Nice script. Wrote and executed it, ran well until it hit the 85th message (my inbox has several thousand) and then stopped or got into a loop. I’m attaching the script. Any idea why/

tell application “Microsoft Excel”

set LinkRemoval to make new workbook
set theSheet to active sheet of LinkRemoval
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 “iCloud”
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)
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

southcoastwebOctober 4th, 2013 at 8:49 am

Hi,
Was there anything different about the 85th message in that list, like maybe it was sent to multiple people or had an attachment.
Im not going to profess to being a developer of applescript, in fact this was the first script I ever wrote with it.

Have a look at the 85th Message and see if there is something with the message that may cause the issue and then come back and i will see if theres something we can adjust
in the script to make it work.

Kenny

Milind LeleOctober 4th, 2013 at 8:29 pm

Kenny:

Looked at the 85th message. In fact repeated the script several days later, still stops at (presumably different) 85th message. Could it be that applescript is overloading the excel file?

Milind

Milind LeleOctober 4th, 2013 at 8:46 pm

Kenny:

I had made one change to your script; I dropped Message and Subject when I ran it and got in trouble. Your full script runs fine, suggesting buffering issues.

MarcOctober 20th, 2013 at 3:22 pm

Hi there,
great script. This is the first time I ever used Applescript.
May I ask a (probably stupid) question: The content of the message is limited to 255 characters. Often the messages are longer. How can I ensure that the entire message is put into column D?
Best
Marc

AlexanderDecember 11th, 2013 at 4:54 pm

Hi, great script! i just had a little issue:

tell application “Mail”
set theRow to 2
set theAccount to “Sales”
get account theAccount
set theMessages to messages of inbox

didn’t work for me and neither:

set theMessages to messages of mailbox “INBOX” of account “example.com”

i’ve used this instead:

tell application “Mail”
set theRow to 2
set theAccount to “UTDATA”
get account theAccount
set theMessages to messages of mailbox “INBOX” of account theAccount

I’m using 10.9, thanks again for the script

janetDecember 20th, 2013 at 5:32 pm

Thanks for the script, it works perfectly…but I wonder how to get the info from a sub-folder- for example a folder stored “on my mac” called “leads”.
I’ve messed around with a few ideas but can’t get that path right!
Thanks again

AndrewJanuary 17th, 2014 at 12:56 am

You just saved me several hours of work. Much appreciated – especially the step by step explanation of how it worked.

palmerosFebruary 11th, 2014 at 5:33 pm

@ marc: it’s because the script tries to put the content as an excel formula and formulas max chars are 255.
Solution: change this row: “set formula of range theRange of theSheet to theMessage”
to: “set value of range theRange of theSheet to theMessage”

A positive sideeffect to this is that excel doesn’t need to parse “the formula” so you get a massive speed increase for you who have many mails.

Also you need to change the row: “set theMessages to messages of inbox”
to “set theMessages to messages of mailbox “INBOX” of account theAccount”
if you only want the script to parse one mail account.
/p

LanceMarch 24th, 2014 at 11:36 am

This script is great. I am trying to modify it by adding the “to field” as well, however, I have run into a problem when setting “recipient” as a column. Has anyone else used this script to export the to field as well?

PeterJuly 10th, 2014 at 8:08 pm

Is there a way to change this script so that it gets the recipients’ email address from sent mail?

KristianOctober 27th, 2014 at 6:52 pm

Peter,

I also needed to get the same thing done with SENT mail. I managed to do it simply by following the advice of palmeros above:


Also you need to change the row: “set theMessages to messages of inbox”
to “set theMessages to messages of mailbox “INBOX” of account theAccount”

once you do that, you can swap “INBOX” to “Sent” and it works.

THANK YOU FOR SHARING this is exactly what I needed!

RichardJune 9th, 2015 at 3:56 am

The script is great, however I added a timeout function since AppleScript times out after 2 mins. So I changed the variable to 30 mins to handle large inbox folder sizes. Enjoy !

tell application “Microsoft Excel”
set LinkRemoval to make new workbook
set theSheet to active sheet of LinkRemoval
set formula of range “A1” of theSheet to “Subject”
end tell

with timeout of (30 * 60) seconds
tell application “Mail”
set theRow to 22
set theAccount to “Yahoo!”
get account theAccount
set theMessages to messages of mailbox “INBOX” of account theAccount
repeat with aMessage in theMessages
my SetSubject(subject of aMessage, theRow, theSheet)
set theRow to theRow + 1
end repeat
end tell
end timeout

on SetSubject(theSubject, theRow, theSheet)
tell application “Microsoft Excel”
set theRange to “A” & theRow
set formula of range theRange of theSheet to theSubject
end tell
end SetSubject

AjayAugust 24th, 2015 at 4:40 pm

Indeed a great experience and learning with this script. It worked perfect.
Need some help if you can answer, what if you want to extract specific data from the body of the email and extract to it’s own columns in Excel?

For example:
Caller Name: Mr ABC from Viman Nagar
Call Date & Time: Mon, 24 Aug 2015 18:42:28
Branch Info: Audh
City: New York
Caller Phone: +1234567890
Caller Email: pqr@mail.com

-Ajay

AjayAugust 24th, 2015 at 7:06 pm

Indeed a great experience and learning with this script. It worked perfect.
Need some help if you can answer, what if you want to extract specific data from the body of the email and extract to it’s own columns in Excel?

For example:
Caller Name:
Mr ABC from Viman Nagar
Call Date & Time:
Mon, 24 Aug 2015 18:42:28
Branch Info:
Audh
City:
New York
Caller Phone:
+1234567890
Caller Email:
pqr@mail.com

-Ajay

JamieJanuary 12th, 2016 at 5:50 pm

Thank you! This was extremely helpful! All I had to do was change “Sales” to “Exchange” and it did exactly what I needed.

Thank you!

JamieJanuary 12th, 2016 at 8:48 pm

Has anyone succeeded in pulling the recipients in as well?
The below script throws the following error: error “Mail got an error: Can’t get every recipient.” number -1728 from every recipient

Maybe if there’s a way to write “if unable to get recipient, return “””?

tell application “Microsoft Excel”
set LinkRemoval to make new workbook
set theSheet to active sheet of LinkRemoval
set formula of range “E1” of theSheet to “Recipient”
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 “Exchange”
get account theAccount
set theMessages to messages of inbox
repeat with aMessage in theMessages
my SetRecipient(my convertList(address of its recipients) of aMessage, theRow, theSheet)
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

on SetRecipient(theRecipient, theRow, theSheet)
tell application “Microsoft Excel”
set theRange to “E” & theRow
set formula of range theRange of theSheet to theRecipient
end tell
end SetRecipient

on convertList(theRecipient)
set tid to text item delimiters
set text item delimiters to “, ” — separator (comma and space)
set theRecipient to theRecipient as text — convert AppleScript’s list to string –> (address separated by comma)
set text item delimiters to tid
return theRecipient
end convertList

JaneJune 29th, 2016 at 10:22 am

How would i collect email address’ from all my email message area only? I have about 400 emails to get emails out of the body area.
Any help would be great appreciated. 🙂

MikeNovember 2nd, 2016 at 11:15 pm

Just wanted to say thank you verrrrrry much this just saved my keester. This reaffirms that the internet is the greatest thing ever.

Years later and this post is still helping people!

gkcFebruary 21st, 2017 at 8:43 am

for anyone looking to also extract the recipient address – try this…

add in a column C in the “tell application Excel” section and bump the other columns up a notch:
set formula of range “C1” of theSheet to “To”

then, in the “tell application Mail” section, add in:

my SetTo((address of recipients) of aMessage as string, theRow, theSheet)

And finally, create the new setTo function:

on SetTo(theRecipient, theRow, theSheet)
tell application “Microsoft Excel”
set theRange to “C” & theRow
set formula of range theRange of theSheet to theRecipient
end tell
end SetTo

all should be well. Multiple recipients will be concatenated though. I leave that as a problem for the reader. Likely text manipulation in Excel itself is the way forward for that.

Leave a Response


Top