Moving email data from Mail to Excel

In How to print a list of selected emails in Apple Mail. I showed how to print a list of emails: the selected emails are written to a csv file which can then be opened by Excel or Numbers.

Drawbacks of CSV
Getting started with Excel and AppleScript
Improving the way columns are handled in Excel
Creating a header with AppleScript in Excel
Getting data from emails into Excel
Final Script

Drawbacks of CSV

CSV, or Comma-Separated Values, is a simple text format used for storing and exchanging tabular data, where each line represents a row of data, and values within each row are separated by commas. The text in csv files needs to be in quotes and quotes themselves need to be escaped. csv doesn't like end-of-line characters in the text at all. So if I wanted to have the message body in Excel, it would be much easier to move the text directly into Excel.

Name,Age,Comment

Mary,30,"Preferred saying, ""To be or not to be"""

Bob,28,"Has a tattoo that says, ""Carpe Diem"""

In this example, newline characters (\n) within the fields are replaced with the placeholder string (r'\n') before processing the CSV data. After reading the data, you can replace the placeholders back with the actual newline characters.

Getting started with Excel and AppleScript

I have 20 years experience of using VBA in Access, Excel and Powerpoint. I've never used Excel with AppleScript. So I'm going to start very simply. The columns in Excel have characters like A, B etc. The rows have numbers. This makes "A1" or "C3" for the individual cells. A cell or multiple of cells is called a range. If I want to talk to several cells at once, I could use "A1:B2". Assuming I have a sheet I can do a "Hello, Excel" like this:

tell application "Microsoft Excel"
activate
set myValue to "Hello, Excel!"
set value of range "A1" of active sheet to myValue
end tell

Improving the way columns are handled in Excel

Talking to the ranges with A, B and so on is annoying. When I develop a script, I always change the order of the rows. So I want to talk to a number instead. And then increase the number instead of moving from A to B. In pseudocode: set column to 1 set row to 1 write something to the current cell increase the column by one write something else to the current cell As a first test I make a brute force script: on getletter(theNumber) if theNumber = 1 then return "A" else if theNumber = 2 then return "B" else if theNumber = 3 then return "C" else if theNumber = 4 then return "D" else if theNumber = 5 then return "E" else if theNumber = 6 then return "F" else if theNumber = 7 then return "G" end if end getletter Now let me make the brute force function for the letter nicer:

-- Convert a number to its corresponding letter (1 to 'a', 2 to 'b', and so on)
on numberToLetter(theNumber)
set alphabet to "abcdefghijklmnopqrstuvwxyz"

if theNumber is greater than or equal to 1 and theNumber is less than or equal to length of alphabet then
set letter to character theNumber of alphabet
return letter
else
return "Not a valid number"
end if
end numberToLetter

-- Test the function for numbers 1 to 26
repeat with i from 1 to 26
set letterValue to numberToLetter(i)
display dialog "Number: " & i & " => Letter: " & letterValue
end repeat

AppleScript treats the letters a-z as array and picks the index of the letter that comes as parameter.

Creating a header with AppleScript in Excel

Now my script is progressing:

I have a nice header!

Getting data from emails into Excel

Now comes the last part of the script. I already know how to get the selection of emails in Mail:

In Excel I need to loop over the information and put the information into a cell. Both the row and the column are variables. This gives me the last part as:

tell application "Mail"
  set theRow to 2
set SelectedMails to selection
repeat with aMessage in SelectedMails

set theSender to (sender of aMessage)
set theSubject to (subject of aMessage)
set theContent to (content of aMessage)

tell application "Microsoft Excel"
  set theCounter to 1
set value of range(my numberToLetter(theCounter) & theRow as string) of theSheet to theSender
set theCounter to theCounter + 1
set value of range(my numberToLetter(theCounter) & theRow as string) of theSheet to theSubject
set theCounter to theCounter + 1
set value of range(my numberToLetter(theCounter) & theRow as string) of theSheet to theContent
  end tell

set theRow to theRow + 1
  end repeat
end tell

And my final script is:

tell application "Microsoft Excel"

set newDoc to make new document
tell active sheet of newDoc
set theWorkbook to make new workbook
set theSheet to active sheet of theWorkbook
set theCounter to 1
set value of range (my numberToLetter(theCounter) & "1" as string) of theSheet to "From"
set theCounter to theCounter + 1
set value of range (my numberToLetter(theCounter) & "1" as string) of theSheet to "Subject"
set theCounter to theCounter + 1
set value of range (my numberToLetter(theCounter) & "1" as string) of theSheet to "Message Body"

end tell

tell application "Mail"
set theRow to 2
set SelectedMails to selection
repeat with aMessage in SelectedMails

set theSender to (sender of aMessage)
set theSubject to (subject of aMessage)
set theContent to (content of aMessage)

tell application "Microsoft Excel"
set theCounter to 1
set value of range (my numberToLetter(theCounter) & theRow as string) of theSheet to theSender
set theCounter to theCounter + 1
set value of range (my numberToLetter(theCounter) & theRow as string) of theSheet to theSubject
set theCounter to theCounter + 1
set value of range (my numberToLetter(theCounter) & theRow as string) of theSheet to theContent
end tell

set theRow to theRow + 1
end repeat
end tell
end tell

on numberToLetter(theNumber)
set alphabet to "abcdefghijklmnopqrstuvwxyz"

if theNumber is greater than or equal to 1 and theNumber is less than or equal to length of alphabet then
set letter to character theNumber of alphabet
return letter
else
return "Not a valid number"
end if
end numberToLetter

Moth Software