Copy mail content and attachments from e-mails in Mac Mail

I was looking for a way to easily get information from emails sent to me via a web form into an Excel file on my Mac. I've used applescript to do that and I want to show some basic examples on this page that may be useful to others as well. You can also find a script to copy Excel files from emails and put them in a local folder on your Mac. Note: I'm not an expert, but I like to use AppleScript because sometimes it's only possible with applescript or it's much easier than having to do the same thing with VBA code. Many thanks to the visitors on MacScripter.net for sharing their knowledge.

If you not know how to run AppleScript follow the steps below :

How do I run AppleScript on my Mac

Click on the Spotlight icon in the top right corner of your Mac.
Enter "Script Editor" to find and open this program.
Click the New Document button, and we are ready to start.

Tip: Right click on the Script Editor icon in the dock and choose Options>Keep in Dock so it is easy the next time to open it when you need it.

Click on one of the examples scripts below and you will notice that the whole script is selected
press Command c to copy it to the clipboard
click in the Script Editor
Use Command v to paste the script inside the editor
Compile the script by pressing the hammer icon next to the run button
Change the mailbox and account name
Press the Run button

Copy content from e-mails that are in specific mailbox into Excel workbook

You can run the script below in the Script Editor to get the info and mail content from the e-mails from the account and mailbox that you can choose when you run the script. You can run the script without changing anything to create a new Excel file with the info and content from the e-mails in it. You can save the file if you want and do with the data what you want. I use VBA to clean up my data and split it into columns for example but you can also use worksheet functions to do the job. All depending how the data looks like. This example will loop through the first 5 e-mails in the mailbox, Read the information in the script to change this.

Note: In the second example on this page, you can fill in the account name and mailbox name so you if you always use the same account and mailbox you do not have to choose both in the script each time.

--Ron de Bruin, https://macexcel.com
--Created on 26-April-2024
--Copy info and content from e-mails in 'Apple Mail' mailbox into Excel workbook
--In this example you can choose the account and mailbox 
--******************************************************************************

tell application "Mail"
	set AccountList to get the name of every account
	set MyAccount to (choose from list (AccountList) with title "Select account" with prompt "Please choose an account" without multiple selections allowed)
	
	if MyAccount is false then
		display dialog "Run the script again and select an account if you want to use this script." with title "You have not selected an account" with icon stop buttons {"Quit Script"} default button "Quit Script"
		return
	else
		set MailboxList to get the name of every mailbox of account (MyAccount as string)
		set MyMailBox to (choose from list (MailboxList) with title "Select mailbox" with prompt "Please choose an mailbox" without multiple selections allowed)
	end if
	
	if MyMailBox is false then
		display dialog "Run the script again and select an mailbox if you want to use this script." with title "You have not selected an mailbox" with icon stop buttons {"Quit Script"} default button "Quit Script"
		return
	end if
end tell

tell application "Microsoft Excel"
	set DestWorkbook to make new workbook
	set DestSheet to active sheet of DestWorkbook
	set value of range "A1" of DestSheet to "Date"
	set value of range "B1" of DestSheet to "From"
	set value of range "C1" of DestSheet to "Subject"
	set value of range "D1" of DestSheet to "Message"
end tell

tell application "Mail"
	--You can also reference your own mailbox in the account
	set MyMailBox to mailbox (MyMailBox as string) of account (MyAccount as string)
	
	set EveryMail to every message of MyMailBox
	set MyRow to 1
	
	repeat with MailInloop in EveryMail
		--Example to only loop through the first 5 mails in this test script
		--Use this to comment it and loop through them all   --if (MyRow - 1) = 5 then exit repeat
		if (MyRow - 1) = 5 then exit repeat
		
		set MyRow to MyRow + 1
		set SetMailDate to date received of MailInloop
		set SetMailSender to sender of MailInloop
		set SetMailSubject to subject of MailInloop
		set SetMailContent to content of MailInloop
		
		tell application "Microsoft Excel"
			set value of range ("A" & MyRow) of DestSheet to SetMailDate
			set value of range ("B" & MyRow) of DestSheet to SetMailSender
			set value of range ("C" & MyRow) of DestSheet to SetMailSubject
			set value of range ("D" & MyRow) of DestSheet to SetMailContent
		end tell
	end repeat
end tell

--After the loop is ready do some things with the Excel data in the new workbook
tell application "Microsoft Excel"
	set bold of font object of range "A1:D1" to true
	set wrap text of range ("D:D") to false
	autofit column "A:C"
	activate
end tell

The only thing that you have to change after you paste the second script below in the Script Editor is the account name and the name of the mailbox in the script line below. In the first example on this page you can choose both when you run the script. This example will loop through the first 5 e-mails in the mailbox, Read the information in the script to change this.

set MyMailBox to mailbox "Inbox" of account "Exchange"

The account names are easy to find when you use Mail>Accounts in the menu bar with Mail active. Or you can run this script line one time to get the names

tell application "Mail" to get the name of every account

And you can try the name of the mailbox that you see in Mail first, but the correct name of the mailbox can be sometimes difficult to find and if you got problems the easiest way to find the exact name is to run this script line one time in the Script Editor :

tell application "Mail" to get the name of every mailbox of account "Exchange"

For example I must use the Dutch word "Postvak IN" for "Inbox" that I see in Mail on my English Macbook, also mailbox names are case-sensitive so it is maybe possible that you must use "INBOX"

When you run the script below it will open a new Excel file and copy all info and mail content inside that file, you can save the file if you want and do with the data what you want. I use VBA to clean up my data and split it into columns for example but you can also use worksheet functions to do the job. All depending how the data looks like.

--Ron de Bruin, https://macexcel.com
--Created on 19-April-2024
--Copy info and content from e-mails in 'Apple Mail' mailbox into Excel workbook
--******************************************************************************

tell application "Microsoft Excel"
	set DestWorkbook to make new workbook
	set DestSheet to active sheet of DestWorkbook
	set value of range "A1" of DestSheet to "Date"
	set value of range "B1" of DestSheet to "From"
	set value of range "C1" of DestSheet to "Subject"
	set value of range "D1" of DestSheet to "Message"
end tell

tell application "Mail"
	--You can also reference your own mailbox in the account
	set MyMailBox to mailbox "Inbox" of account "YourAccountName"
	
	set EveryMail to every message of MyMailBox
	set MyRow to 1
	
	repeat with MailInloop in EveryMail
		--Example to only loop through the first 5 mails in this test script
		--Use this to comment it and loop through them all   --if (MyRow - 1) = 5 then exit repeat
		if (MyRow - 1) = 5 then exit repeat
		
		set MyRow to MyRow + 1
		set SetMailDate to date received of MailInloop
		set SetMailSender to sender of MailInloop
		set SetMailSubject to subject of MailInloop
		set SetMailContent to content of MailInloop
		
		tell application "Microsoft Excel"
			set value of range ("A" & MyRow) of DestSheet to SetMailDate
			set value of range ("B" & MyRow) of DestSheet to SetMailSender
			set value of range ("C" & MyRow) of DestSheet to SetMailSubject
			set value of range ("D" & MyRow) of DestSheet to SetMailContent
		end tell
	end repeat
end tell

--After the loop is ready do some things with the Excel data in the new workbook
tell application "Microsoft Excel"
	set bold of font object of range "A1:D1" to true
	set wrap text of range ("D:D") to false
	autofit column "A:C"
	activate
end tell

In the mails that I got from my webform there where U+2028 line separators, In Excel it is not so easy to replace the U+2028 line separator so I looked for a solution inside the script to replace them with "_" so I can split the text on that into columns, you can see the solution that I add to the script above in the script below. The other cleanup and splitting the data into columns I do in Excel with VBA.

--Ron de Bruin, https://macexcel.com
--Created on 19-April-2024
---Copy info and content from e-mails in 'Apple Mail' mailbox into Excel workbook
--*******************************************************************************

use framework "Foundation"
use scripting additions

tell application "Microsoft Excel"
	set DestWorkbook to make new workbook
	set DestSheet to active sheet of DestWorkbook
	set value of range "A1" of DestSheet to "Date"
	set value of range "B1" of DestSheet to "From"
	set value of range "C1" of DestSheet to "Subject"
	set value of range "D1" of DestSheet to "Message"
end tell

tell application "Mail"
	--You can also reference your own mailbox in the account
	set MyMailBox to mailbox "Inbox" of account "YourAccountName"
	
	set EveryMail to every message of MyMailBox
	set MyRow to 1
	
	repeat with MailInloop in EveryMail
		--Example to only loop through the first 5 mails in this test script
		--Use this to comment it and loop through them all   --if (MyRow - 1) = 5 then exit repeat
		if (MyRow - 1) = 5 then exit repeat
		
		set MyRow to MyRow + 1
		set SetMailDate to date received of MailInloop
		set SetMailSender to sender of MailInloop
		set SetMailSubject to subject of MailInloop
		set SetMailContent to content of MailInloop
		
		--I got problems with replacing the U+2028 line separator in Excel so I add this to the script to replace them with _
		--The other cleanup and splitting the data into columns I do in Excel with VBA
		--This AppleScriptObjC code replaces any line separator character U+000A ~ U+000D , U+0085 , U+2028 , and U+2029 
		--with a "_" character. Posted by StefanK in the Macscripter.net forum to replace original with 'linefeed'
		set nsText to (current application's NSString's stringWithString:SetMailContent)
		set newLinesCharacterSet to current application's NSCharacterSet's newlineCharacterSet()
		set separatedLines to (nsText's componentsSeparatedByCharactersInSet:newLinesCharacterSet)
		set SetMailContent to (separatedLines's componentsJoinedByString:"_") as rich text
		
		tell application "Microsoft Excel"
			set value of range ("A" & MyRow) of DestSheet to SetMailDate
			set value of range ("B" & MyRow) of DestSheet to SetMailSender
			set value of range ("C" & MyRow) of DestSheet to SetMailSubject
			set value of range ("D" & MyRow) of DestSheet to SetMailContent
		end tell
	end repeat
end tell

--After the loop is ready do some things with the Excel data in the new workbook
tell application "Microsoft Excel"
	set bold of font object of range "A1:D1" to true
	set wrap text of range ("D:D") to false
	autofit column "A:C"
	activate
end tell

Copy attachments from e-mails that are in specific mailbox into local folder

This script makes a folder on your Desktop named "RDB-Mail-Attachments" and copy the Excel attachments that are in the e-mails to this local folder, if the folder already exists it will first delete all the files in it before it will paste the new files in it. It is possible that you must give permission when you run the script because it create a folder on your Desktop. I choose in this example to get Excel files, but you can change that in the script if you want. I often use a mail rule that move e-mails with files in it to a certain mailbox, the script is handy to get the files out of the e-mails so I can get information out of them with VBA for example. This example will loop through the first 20 e-mails, see the script for how to change this. It also add a number before each file so you not have a problem with duplicate file names.

--Ron de Bruin, https://macexcel.com
--Created on 19-April-2024
--Copy attachments from e-mails in 'Apple Mail' mailbox into local folder
--************************************************************************

--Set Attachement Destination folder 'RDB-Mail-Attachments' on your Desktop
--No need to change the Finder part of this script
tell application "Finder"
	set DesktopPath to (path to desktop folder as text)
	set DestFolderPath to DesktopPath & "RDB-Mail-Attachments:" as text
	if not (exists folder DestFolderPath) then
		make new folder in DesktopPath with properties {name:"RDB-Mail-Attachments"}
	else
		--If the folder exists first delete all files in it
		delete (files of folder DestFolderPath)
	end if
	--Open and activate the folder
	open DestFolderPath
	activate DestFolderPath
end tell

tell application "Mail"
	--You can also reference you own mailbox in the account
	set MyMailBox to mailbox "Inbox" of account "YourAccountName"
	
	--Loop through all mails in MyMailBox
	set EveryMail to every message of MyMailBox
	set LoopRow to 0
	set Attachmentnumber to 0
	
	repeat with MailInloop in EveryMail
		--Count the mails in the mailbox to exit the loop if you want 
		--for example to only loop through the first 20 mails in this test script
		--Use this to comment it  --if LoopRow = 20 then exit repeat
		set LoopRow to LoopRow + 1
		if LoopRow = 20 then exit repeat
		
		--Copy Excel attachments to local folder DestFolderPath
		--I add a number before the file name so it also copy files with the same name
		--if not you will only get one file with that file name
		set AttachmentCount to count of (mail attachments of MailInloop)
		if AttachmentCount is not 0 then
			
			repeat with TheAttachment in (mail attachments of MailInloop)
				set Attachmentnumber to Attachmentnumber + 1
				set AttachmentName to name of TheAttachment
				set savePath to (DestFolderPath & Attachmentnumber & " " & AttachmentName) as «class furl»
				
				--Only copy Excel files from the Mail in this example
				if AttachmentName contains ".xls" then
					save TheAttachment in savePath
				end if
			end repeat
			
		end if
	end repeat
end tell

Mac Excel Automation