Get all Mails from a specific recipient address

Hi there

I would make an apple-script, which get all Mail-Messages from a specific recpient-address and put them into an excel-sheet. This is what i’ve done so far:




tell application "Microsoft Excel"
	set theSpamFile to make new workbook
	set theSheet to active sheet of theSpamFile
	set formula of range "A1" of theSheet to "From"
	set formula of range "B1" of theSheet to "Mailbox"
	set formula of range "C1" of theSheet to "Subject"
	set formula of range "D1" of theSheet to "Content"
	set formula of range "E1" of theSheet to "Received"
end tell

--with timeout of  3600 seconds

-- MAIL PART
tell application "Mail"
	
	set theRow to 2
	get mailboxes

	set theMailboxes to every mailbox of account "Support" --whose name contains "Ford"

	repeat with aMailbox in theMailboxes
		
		set theMessages to messages of aMailbox
		
		return count theMessages
		
		set thePath to name of aMailbox
		set aMailbox to container of aMailbox
		set thePath to name of aMailbox & "/" & thePath
		
		repeat with aMessage in theMessages
			
			set theRecipient to address of to recipient of aMessage
			
			set {year:y, month:m, day:d, hours:h, minutes:min} to aMessage's date received
			
			if y > 2013 and theRecipient contains "support@master.ch"
				
				set mailBoxName to name of mailbox of aMessage as rich text
				
				set m to m as number
				
				if m < 10 then
					set m to "0" & m as rich text
				else
					set m to m as rich text
				end if
				
				if d < 10 then
					set d to "0" & d as rich text
				else
					set d to d as rich text
				end if
				
				set theDate to y & "-" & d & "-" & m as rich text
				
				--display dialog theDate
				
				my SetFrom(sender of aMessage, theRow, theSheet)
				my SetMailbox(thePath, theRow, theSheet)
				my SetSubject(subject of aMessage, theRow, theSheet)
				my SetContent(content of aMessage, theRow, theSheet)
				my SetReceived(theDate, theRow, theSheet)
				
				set theRow to theRow + 1
				
				
				
			end if
		end repeat
		
		
		
	end repeat
end tell

--end timeout

on SetFrom(theSender, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "A" & theRow
		set formula of (range theRange) of theSheet to theSender
	end tell
end SetFrom

on SetMailbox(theMailbox, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "B" & theRow
		set value of range theRange of theSheet to theMailbox
	end tell
end SetMailbox

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 SetContent(theContent, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "D" & theRow
		set formula of range theRange of theSheet to theContent
	end tell
end SetContent

on SetReceived(dateReceived, theRow, theSheet)
	tell application "Microsoft Excel"
		set theRange to "E" & theRow
		set formula of range theRange of theSheet to dateReceived
	end tell
end SetReceived


When I run this script, it freezes after a few seconds. So my question is, is there a faster way to do this? Or am I completely wrong whit my script to do what want to?

Thank you all in advance
Cheers

Marth

I don’t think your script freezes.
It just stops:

return count theMessages

As you’re at the top level of the script, control is returned to AppleScript, which effectively terminates the script.

Some other things:
You are setting formula’s to constants. You want to set value, not formula.

You are settings ranges of one cell. Not formally wrong, but gets confusing when script get larger. Use cell when it’s a cell.

No need to trouble yourself with building a date stamp. Excel understands date objects, you can just set a cell’s value to a date. To format it use cell properties number format or number format local.

You don’t need multiple subroutines to write a row of data.
You can set values of a range:

tell application "Microsoft Excel"
	set value of range "A1:D1" to {"value1", "date_object", "number", "whatever"}
end tell

There is residue of lots of modifications. Start with a clean slate, and do one logical step at a time.
This could be the 1st block:

tell application "Microsoft Excel"
	set theSpamFile to make new workbook
	set theSheet to active sheet of theSpamFile
	set value of range "A1:E1" of theSheet to {"From", "Mailbox", "Subject", "Content", "Received"}
end tell