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)
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.
