I have 80 emails and I need to get the contents of each and add to a CSV file. By contents I mean sender, subject and email body. Instead of manually going through each email, is there a way Keyboard Maestro can automate this? Almost like a mail merge type process but in reverse?
It depends -- how do you access your email? With a "proper" email client? If so, which one? Via <shudder> a web browser? Could be tricker...
Apple Mail app. But if not possible to do in there, would consider using other apps.
Excellent.
What is the content of the email body like? Are you happy with just the text, or do you need formatting (if any)? Importantly, given you want to end up in a CSV -- might it contain commas? 
Just need text. Not bothered about formatting. Commas can just be replaced with spaces.
Also need to save attachments to folder (attachments from all 80 emails saved to same folder).
The following AppleScript works, but depends on a couple of things:
- Your destination for the CSV must handle linefeeds within fields correctly. "Open"ing the CSV file in Excel will work properly, but "Import"ing the CSV into an Excel sheet won't
- Attachments must have different names or they'll overwrite whatever was previously saved with that name
If either of those will be a problem just say and I'll see what can be done. For point 1 you'll need to say what any linefeeds within the email's contents should be replaced with. For point 2 you'll ideally have some way to relate the attachment to the original email -- would prepending the sender to the attachment name work?
The script is non-destructive, so don't worry about it deleting your emails!
Updated to properly escape double-quotes in text
tell application "Mail"
set theSelection to (get selection)
if theSelection is {} or class of item 1 of theSelection is not message then
display dialog "Make sure 1 or more messages are selected"
return 1
end if
set theText to ""
set theFolder to POSIX path of (choose folder with prompt "Select export destination")
repeat with eachMsg in the theSelection
set theSender to sender of eachMsg
set theSubject to subject of eachMsg
set theContent to content of eachMsg
set theSender to escapeQuotes(theSender) of me
set theSubject to escapeQuotes(theSubject) of me
set theContent to escapeQuotes(theContent) of me
set theText to theText & "\"" & theSender & "\",\"" & theSubject & "\",\"" & theContent & "\"" & linefeed
repeat with eachAttachment in (get mail attachments of eachMsg)
save eachAttachment in (theFolder & name of eachAttachment)
end repeat
end repeat
set theRef to (open for access (theFolder & "export.csv") with write permission)
write theText to theRef
close access theRef
end tell
on escapeQuotes(theText)
set AppleScript's text item delimiters to "\""
set tmp to every text item of theText
set AppleScript's text item delimiters to "\"\""
return tmp as text
end escapeQuotes
1 Like
Incredible. Your macros and code just works every time.
Can the email subject be prepended to attachment name?
Selecting emails will be a great general purpose macro. Wondering if there's a way to make a variation of this macro that, instead of selecting emails, it captures all emails that certain words in the subject. In my current situation, the subject of all 80 emails starts with 'Re: Russian Common Gull'.
ChatGPT suggested the below but something not working.
repeat with eachMailbox in theMailboxes
set filteredMessages to (every message of eachMailbox whose subject contains "Russian Common Gull")
set theMessages to theMessages & filteredMessages
end repeat
Sure -- just add that into the line that saves to attachment. So change:
save eachAttachment in (theFolder & name of eachAttachment)
...to:
save eachAttachment in (theFolder & subject of eachMsg & " - " & name of eachAttachment)
(We're getting the subject from the email again, rather than using the "escaped" version.)
TBH, I'd keep the macro working on a selection and, instead, make a Smart Mailbox to gather the emails and select from that. That way you could process emails, flag them as done, and not worry about double-processing if you don't delete them.
If you do want to do it this way you'll need work through a list of mailboxes, and to get that you work through a list of accounts. So to create your list:
repeat with eachAccount in (get every account)
repeat with eachMailbox in (get every mailbox of eachAccount)
set msgList to msgList & (every message of eachMailbox whose sender contains "Russian Common Gull")
-- do the stuff that works
-- through each message in msgList
end repeat
end repeat
end tell
So putting that together with the other change you'll want something like (untested):
Script
tell application "Mail"
set msgList to {}
set theText to ""
set theFolder to POSIX path of (choose folder with prompt "Select export destination")
repeat with eachAccount in (get every account)
repeat with eachMailbox in (get every mailbox of eachAccount)
set msgList to msgList & (every message of eachMailbox whose sender contains "Russian Common Gull")
repeat with eachMsg in the theSelection
set theSender to sender of eachMsg
set theSubject to subject of eachMsg
set theContent to content of eachMsg
set theSender to escapeQuotes(theSender) of me
set theSubject to escapeQuotes(theSubject) of me
set theContent to escapeQuotes(theContent) of me
set theText to theText & "\"" & theSender & "\",\"" & theSubject & "\",\"" & theContent & "\"" & linefeed
repeat with eachAttachment in (get mail attachments of eachMsg)
save eachAttachment in (theFolder & subject of eachMsg & " - " & name of eachAttachment)
end repeat
end repeat
end repeat
end repeat
set theRef to (open for access (theFolder & "export.csv") with write permission)
write theText to theRef
close access theRef
end tell
on escapeQuotes(theText)
set AppleScript's text item delimiters to "\""
set tmp to every text item of theText
set AppleScript's text item delimiters to "\"\""
return tmp as text
end escapeQuotes
Many thanks. Agree to stick with selecting emails to avoid double-processing.
Prepending the subject to the file name doesn't seem to be working. I can only get it to work by using the below but this adds Re/ to attachment file name.
save eachAttachment in (theFolder & theSubject & " - " & name of eachAttachment)
Sorry that's me being stupid and copying from the wrong version. Because we are working through the email list with
repeat with eachMsg in the theSelection
...
...we need to reference each message as eachMsg -- I used eachMessage in the edit. It should have been
save eachAttachment in (theFolder & subject of eachMsg & " - " & name of eachAttachment)
That's because the email's subject begins with "Re:" and ":" is an illegal character in a macOS file name -- it's substituted with "/".
Would you be OK changing the ":" to "-" in the file name? It may be difficult to remove the "reply" indicator completely, especially if you've foreign correspondents who use a different indicator.
Amazing, thanks for your expertise.
Wondering if the message link can also be added to csv file? ChatGPT says the code to extract link is something like below, although not sure how this can be incorporated into above code?
set messageLink to "message://" & theMessageID
The incantation to create the link is
set messageLink to "message://%3c" & message id of eachMsg & "%3e"
(The id must be enclosed with < and >, which URL encode to %3c and %3e respectively.) Add that after getting the message sender, subject, etc.
It won't have any quotes so we don't need to escape it, but might as well quote-enclose it for consistency with the other fields. So just expand the set theText line to include the new variable and surrounding quotes:
set theText to theText & "\"" & theSender & "\",\"" & theSubject & "\",\"" & theContent & "\"" & messageLink & "\"" & linefeed
1 Like