How to access emails in Apple Mail programmatically

The simplest and quickest way to do this in other languages would just be to drop each of your columns from excel into a list (array) and then have the whole thing in a loop and it will create the email fill the information and then send the email then loop back to the beginning and start again with values from each of the arrays at [1] and so on from there through your list. Not the most professional or efficient way but for your purposes that would work. If you can implement that then go ahead. If not I'll script it when I get back to my desktop.

----edit----

I edited script first written on this post: https://stackoverflow.com/questions/15386794/applescript-for-sending-emails-from-a-list-in-excel

to achieve this script. I code in java mostly so this might not be perfect so maybe somebody can come along and tweak it. OP should go through and enter his own values for the things written in all caps. Also the excel sheet needs to be in the form that the OP from the links excel sheet is in

set {firstName, eAddress} to getData()

repeat with i from 1 to count firstName
tell application "Mail"
    activate
    set mymail to make new outgoing message at the beginning of outgoing messages with properties {subject:"INSERT SUBJECT HERE"}
    tell mymail
        make new to recipient at beginning of to recipients with properties {address:item i of eAddress}
--The next line will start each email with Hi firstName and then carriage return to the text you fill in below

        set content to "Hi " & item i of firstName & "

INSERT BODY OF EMAIL HERE"
    end tell
    --show message window (otherwise it's hidden)
    set visible of mymail to true
    --bring Mail to front
    activate
    send mymail
end tell
end repeat


on getData()
set colA to {}
set colB to {}
tell application "Microsoft Excel"

    activate
    tell active sheet
        set lastRow to first row index of (get end (last cell of column 1) direction toward the top)

        repeat with i from 3 to lastRow
            set end of colA to (value of range ("A" & i))
            set end of colB to (value of range ("B" & i))
        end repeat
    end tell
end tell

return {colA, colB}
end getData    

I take it the core of your question is how to securely delete an email which you received via an IMAP email account, and read using your local Mac OS X ''Mail'' application. You ask other interesting questions along the way, to which I'll give only brief answers.

First, it's important to understand the IMAP account, and how it affects deleting messages. The essence of the IMAP approach to email is that mail resides primarily on the mail server. The email client (the Mac OS Mail application in your case) temporarily downloads a copy of the message to your local Mac for you to read. If you were then to feed your Mac to a gigantic crushing machine, the local Mac and its copy of the message would be destroyed, but the original copy of the message remains intact on the IMAP server. When you tell Mail to delete the message, Mail tells the IMAP server to delete its copy of the message, and Mail deletes its own copy of the message. Thus, you might want to concern yourself with how securely the IMAP server deletes its copy.

Second, concerning strictly the way Mail deletes its messages, we can tell that the local copy of the message exists as a file on the local Mac. Telling Mail to delete the message makes it disappear from the Mail window, but might not delete the local file containing the local copy. The Mail command "Erase deleted items..." does reliably delete the local file with the local copy.

You ask, "If I delete an email and empty trash in the client then is the deletion done securely, using srm or in a non-secure way?" I don't have access to the Mail source code, so I don't know how securely it deletes the file. We have to assume that it deletes files using the most fast, easy, and insecure method available from Mac OS.

So, the most secure way I know of to delete the email message from the client is:

• In Mail, delete the message
• In Mail, erase deleted items. Control-click on the mailbox which had contained the message. A pop-up menu appears. Select "Erase deleted items...". A confirmation dialog appears. You are about to delete files related to not just the message you just deleted, but other deleted messages also. Click "OK".
• Using Disk Utility, erase free space with either 7 passes or 35 passes. This too will erase more than just the local copy message you deleted.

If you care this much about security, you should also think hard about how your IMAP server deletes messages, and how vulnerable you are to an attacker seeing a copy of the message on your IMAP server's disk. You should also think about whether you should be sending such messages unencrypted, where an attacker monitoring the data flowing through the public Internet could read it.

You ask, "Is there a way to pinpoint the exact file location of an email in the client?" Yes, but it's not fast and not programmatic. You already figured out that the messages are stored in

~/Library/Mail/V2/IMAP-@mykolab.com@imap.mykolab.com/INBOX.mbox/...

My impression from looking at my own file system and thinking logically is that the local copy of your email message will be one of the files matching:

~/Library/Mail/V2/IMAP-@mykolab.com@imap.mykolab.com/INBOX.mbox/*/Data/*/*/Messages/*.emlx

(This pattern is not the only one Mail appears to use; messages stored "on my Mac", and attachments, will have different patterns describing the file path names.

Each email message appears to be stored as a plain text file with a name like number.emlx, e.g. 55219.emlx. One way to find the appropriate file is find a distinctive phrase in the message, and to use grep from the command line to find a file containing this phrase. Thus, [linebreaks added for readability]

% grep -l 'Repair Cafe January 18' ~/Library/Mail/V2/IMAP-@mykolab.com@imap.mykolab.com
    /INBOX.mbox/*/Data/*/*/Messages/*.emlx
/Users/myuser/Library/Mail/V2/IMAP-@mykolab.com@imap.mykolab.com
    /INBOX.mbox/393BA6E1-145B-E29D-536F-306645CA9917/Data/3/5/Messages/53938.emlx

However, don't assume that you can safely delete these files out from under the Mail application. It might get confused by the unexpected absence of the file. It might simply recreate the file from the copy on the IMAP server.