Export To Filemaker Database

• Besides support for FileMaker Pro 4.x, 5.x & 6.x, the script will work with
  Filemaker Pro 3.0 as well.
  
  
• You can select single or multiple messages as well as mailboxes (which in turn
  might contain mailboxes itself). All contained messages will be exported to the
  Filemaker database. If you've selected one or more mailboxes the script will ask
  you to confirm that you really want to export all contained messages (this is
  for security reasons but can be disabled within the script).
  
  
• Upon the first start the script will prompt you for the location of the database
  (which will be remembered & kept track of if you move or rename the file).
  The next time you use the script the database will be opened automatically.
  
  
• If a message was exported successfully it will be tagged by adding the string
  "<exported>" (without quotation marks) to the notes section of the message
  (instead of assigning the label #30 to it). This tag allows the script to recognize
  that the message has been already exported once, so it won't get exported again
  when you run the script on the same message(s) or mailbox(es) again! Removing
  this tag from the notes field will cause the message to get exported again.
  
  
• After the script has finished its work a dialog will inform you about the number
  of messages that were exported successfully to the database. If anything goes
  wrong it will also report the number of messages that failed to export.
  
  
• Added a few fields to the Filemaker database layout and applied some cosmetic
  changes. Additionally, a list view layout was added together with a few buttons,
  which will sort the database entries e.g. by date, email or name of sender.

• exports now mailbox name and label no./name of a message
• the fields of the database layout were updated accordingly
  (note: the script v1.4 requires this new database layout!)
• added a list view and different sorting options to the database layout

• code for FMP record creation resembles again that of v1.0 (which is faster)
• error checking was improved
• the warning that pops up if complete mailboxes are going to be exported is now optional
  (prevent the warning dialog by setting the variable 'mbox_warning' to 'false')
• the final dialog now also provides info on the number of messages that failed to export

(*Original header by BareBones Software:

	This script attempts to export a mail message from Mailsmith to a FileMaker Pro database. 
	If the export succeeds the label index of the message is set to 30. 
	If it fails for some reason it is set to 31
	
	This script requires FileMaker Pro 4.0 or later.
	It does not work with File Maker Pro 3.0

	Requires the Date Calculations OSAX
	
	The script is Copyright 1998 Bare Bones Software, Inc.
	It may be freely used in conjunction with Mailsmith 1.1 or later
	but may not be included in any commercial product without the prior
	written consent of Bare Bones Software, Inc.
*)


-- These properties will store the local path to your filemaker database and its name.
-- You will be prompted to specify its location at the first start (from which the name will be extracted).
-- The script will keep track of your database path & name as you move or rename it.
property database_path : 0
property database_name : 0

global layout_name, Die, msgs_exported, msgs_export_failed, count_only_once, submbox_ct, FMP4_or_greater, Finder_label_list


-- This specifies the layout to be used. Its name must be edited if it was changed in the database:
set layout_name to "import view"

-- Set this variable to "false" if you don't want to see a confirmation dialog when exporting complete mailboxes
set mbox_warning to true

set Die to false
set msgs_exported to 0
set msgs_export_failed to 0
set count_only_once to {}
set mbox_ct to 0
set submbox_ct to 0
set Finder_label_list to {7, 6, 5, 4, 3, 2, 1}


-- This part handles receiving a message from a filter and is
-- used when this script is executed as part of a filter action.
on FilterMessage(the_message)
	my parse_message(the_message)
end FilterMessage


-- This part handles exporting messages or mailboxes selected in a mail list window
-- or in the mail browser window.  If no messages are selected it gets flipant…
tell application "Mailsmith 1.5"
	set the_list to selection as list
	if the_list is not {} then
		my launch_database()
		if not Die then
			try
				set m to item 1 of the_list
				if class of m is mailbox then
					if mbox_warning then -- Throw up a dialog when complete mailboxes are selected
						-- Before getting started: if a mailbox is selected the user will be informed that complete
						-- mailboxes (including all submailboxes!) will be exported, which of course may take some time!
						repeat with obj in the_list
							if id of obj is not in count_only_once then
								set mbox_ct to mbox_ct + 1 -- counts total number of 1st-level mailboxes (regarding your selection)
								my count_submboxes(obj) -- counts total number of selected sub-level mailboxes
							end if
						end repeat
						activate
						-- The warning dialog:
						set DialogResult to display dialog "Your selection contains:" & return & return & ¬
							"mailboxes:             " & mbox_ct & return & ¬
							"submailboxes:       " & submbox_ct & return & return & ¬
							"Really export ALL contained messages?" with icon note buttons {"Cancel", "OK"} default button 2
						if button returned of DialogResult is "Cancel" then set Die to true
					end if
					if not Die then
						repeat with obj in the_list
							my process_mailbox(obj)
						end repeat
					end if
				else -- Class is not mailbox -> "message" or something else ("string", etc.)
					my process_selection(the_list)
				end if
			on error -- No class specified
				my process_selection(the_list)
			end try
			if not Die then -- inform the user how many msgs have been exported successfully
				my export_statistics()
			end if
		end if
	else -- Nothing was selected
		my nothing_selected_alert()
	end if
end tell


-- This section will prepare for message parsing and will hand each message to the parse_message routine:
on parse_msgs_prologue(m_list)
	set old_delimiters to AppleScript's text item delimiters
	repeat with i from 1 to count items of m_list
		my parse_message(item i of m_list)
	end repeat
	set AppleScript's text item delimiters to old_delimiters
end parse_msgs_prologue


-- This is the part of the script which actually gets the interesting parts of the message:
on parse_message(the_message)
	tell application "Mailsmith 1.5"
		try
			if notes of the_message does not contain "<exported>" then
				set AppleScript's text item delimiters to return
				
				set mailbox_name to name of container of the_message
				
				set full_headers to header text of the_message as text
				
				if full_headers is "" then
					set full_headers to "(outgoing messages have no headers)"
					if sent of the_message is true then set msg_type to "sent"
					if sent of the_message is false then set msg_type to "queued"
				else
					set msg_type to "incoming"
				end if
				
				set msg_priority to "normal"
				if priority of the_message is urgent then set msg_priority to "urgent"
				if priority of the_message is high then set msg_priority to "high"
				if priority of the_message is low then set msg_priority to "low"
				if priority of the_message is bulk then set msg_priority to "bulk"
				if priority of the_message is none then set msg_priority to "(none)"
				
				set the_subject to subject of the_message
				if the_subject is "" then set the_subject to "(no subject)"
				
				set the_contents to contents of the_message as text
				if the_contents is "" then set the_contents to "(no body content)"
				
				set the_enclosures to (name of every enclosure of the_message) as text
				if the_enclosures is "" then set the_enclosures to "(no enclosures)"
				
				set the_notes to notes of the_message
				if the_notes is "" then set the_notes to "(no notes)"
				
				set the_originator to originator of the_message
				set orig_name to display name of the_originator
				set orig_email to address string of the_originator
				set the_originator to orig_email & " (" & orig_name & ")"
				
				set the_to_recipients to my address_constructor(to_recipients of the_message) as text
				set the_cc_recipients to my address_constructor(cc_recipients of the_message) as text
				set the_bcc_recipients to my address_constructor(bcc_recipients of the_message) as text
				
				set AppleScript's text item delimiters to ""
				
				set time_sent to my parse_time(time sent of the_message)
				set date_sent to my parse_date(time sent of the_message)
				
				if msg_type is "incoming" then
					set time_received to my parse_time(time received of the_message)
					set date_received to my parse_date(time received of the_message)
					
				else
					set time_received to ""
					set date_received to ""
				end if
				
				set label_no to label index of the_message
				-- due to some strange behaviour Mailsmith will map the Finder labels 1-7 to label indices 7-1
				-- so if necessary we have to switch these numbers first:
				if label_no is in Finder_label_list then
					set label_no to item label_no of Finder_label_list
				end if
				if label_no is not 0 then -- get label name
					set label_name to name of label label_no
				else
					set label_name to "none"
				end if
				
				if FMP4_or_greater then -- Filemaker Pro 4 or greater is being used
					set the_data to ¬
						{full_headers, the_contents, the_subject, the_enclosures, the_notes, ¬
							orig_name, orig_email, the_originator, ¬
							the_to_recipients, the_cc_recipients, the_bcc_recipients, ¬
							date_received, time_received, date_sent, time_sent, msg_type, msg_priority, ¬
							mailbox_name, label_no, label_name}
				else -- Filemaker Pro < 4 is being used
					set the_data to ¬
						{full_headers, the_to_recipients, the_contents, the_subject, date_received, ¬
							orig_name, the_originator, the_cc_recipients, date_sent, time_received, ¬
							time_sent, orig_email, msg_type, the_bcc_recipients, the_enclosures, ¬
							the_notes, msg_priority, mailbox_name, label_no, label_name}
				end if
				
				my create_record(the_data)
				
				-- Counts the total number of exported messages:
				set msgs_exported to (msgs_exported + 1)
				
				-- This will tag the successfully exported message:
				if the_notes = "(no notes)" then
					set notes of the_message to "<exported>" & return & return
				else
					set notes of the_message to "<exported>" & return & return & the_notes
				end if
			end if
		on error
			my handle_error(the_message)
		end try
	end tell
end parse_message


-- This section handles getting the time (as a string) out of dates 
-- and requires the "Date Calculations" osax:
on parse_time(foo)
	set time_items to date items foo
	set the_hour to hours part of time_items
	set the_minute to minutes part of time_items
	set the_second to seconds part of time_items
	if the_minute < 10 then set the_minute to 0 & the_minute
	if the_second < 10 then set the_second to 0 & the_second
	
	set the_time to the_hour & ":" & the_minute & ":" & the_second as text
end parse_time


-- This section handles getting the date (as a string) out of dates 
-- and requires the "Date Calculations" osax:
on parse_date(foo)
	set date_items to date items foo
	set the_month to month part of date_items
	set the_day to day part of date_items
	set the_year to year part of date_items as text
	if the_month < 10 then set the_month to 0 & the_month
	if the_day < 10 then set the_day to 0 & the_day
	
	set the_date to the_day & "." & the_month & "." & the_year as text
end parse_date


-- This section handles building full addresses from the parts that Mailsmith provides:
on address_constructor(foo)
	tell application "Mailsmith 1.5"
		set recipient_list to {}
		if foo is not {} then
			set foo to foo as list
			repeat with i from 1 to count items of foo
				set the_name to display name of item i of foo
				set the_address to address string of item i of foo
				set the_person to the_address & " (" & the_name & ")" as text
				set recipient_list to recipient_list & the_person
			end repeat
		end if
		recipient_list
	end tell
end address_constructor


-- This section handles blasting the data into FileMaker Pro
on create_record(the_data)
	tell application "FileMaker Pro"
		set current record to create new record with data the_data at layout layout_name of database database_name
	end tell
end create_record


-- This handler will process every kind of selection that
-- 	- does not consist out of mailboxes -> class is not mailbox
--	 - is not "empty" -> {}
on process_selection(m_list)
	tell application "Mailsmith 1.5"
		set retry to false
		set m to item 1 of m_list
		try
			if class of m is message then
				my parse_msgs_prologue(m_list)
			else
				set retry to true
			end if
		on error
			set retry to true
		end try
		if retry then
			set retry to false
			try
				set m_list to message of window 1 as list
				my parse_msgs_prologue(m_list)
			on error
				set retry to true
			end try
		end if
		if retry then -- -> No selection
			set Die to true
			my nothing_selected_alert()
		end if
	end tell
end process_selection


-- This handler will step through the mailboxes (if any selected):
on process_mailbox(mbox)
	tell application "Mailsmith 1.5"
		set m_list to (every message of mbox) as list
		if m_list is not {} then my process_selection(m_list)
		set ct to count mailboxes of mbox
		repeat with i from 1 to ct
			set obj to mailbox i of mbox
			my process_mailbox(obj)
		end repeat
	end tell
end process_mailbox


-- This section will start the Filemaker app (if not already running) and 
-- will open the database (if its not already open):
on launch_database()
	-- first get the FMP version being used (which is important when arranging the data for export):
	tell application "FileMaker Pro" to set FMP_version to version as text
	set FMP_version to first character of FMP_version as number
	if FMP_version ≥ 4 then -- FMP 4 or greater is being used
		set FMP4_or_greater to true
	else
		set FMP4_or_greater to false
	end if
	tell application "Finder"
		try
			if (exists file database_path) then
				tell application "FileMaker Pro"
					if not (exists database database_name) then
						open database_path
						set database_name to name of database 1
					end if
				end tell
			else
				my choose_database()
			end if
		on error ErrText number ErrNo
			if ErrNo is not -128 then -- if user didn't press 'cancel':
				if ErrNo is 32768029 then -- "file not found"
					my choose_database()
				else -- present an error message
					display dialog "The following error has occurred:" & return & return & ErrText & return & ¬
						"(Error Code: " & ErrNo & ")" with icon caution buttons {"OK"} default button 1
				end if
			else
				set Die to true
			end if
		end try
	end tell
end launch_database


on choose_database() -- If the database wasn't found (script was recompiled or due to some error) the script will ask for the database location:
	set database_path to choose file with prompt "Choose the Filemaker database:"
	
	--This section will extract the database name from "database_path":
	set old_delims to AppleScript's text item delimiters
	set AppleScript's text item delimiters to ":"
	set database_name to string (last text item of (database_path as string))
	set AppleScript's text item delimiters to old_delims
	
	tell application "FileMaker Pro" to open database_path
end choose_database


-- This handler will step through all selected mailboxes and count the total number of sub-level mailboxes
-- contained within the selection. This is only to provide information for the initial dialog that pops up if
-- your selection contains mailboxes.
on count_submboxes(mbox)
	tell application "Mailsmith 1.5"
		set mbox_id to id of mbox
		if mbox_id is not in count_only_once then -- Avoids double counting
			copy mbox_id to end of count_only_once
			set ct to count mailboxes of mbox
			set submbox_ct to submbox_ct + ct
			repeat with i from 1 to ct
				set obj to mailbox i of mbox
				my count_submboxes(obj)
			end repeat
		end if
	end tell
end count_submboxes


on export_statistics()
	if (msgs_exported = 0) and (msgs_export_failed = 0) then
		set already_exported_info to "" & return & return & "All selected messages have been already exported!"
	else
		set already_exported_info to ""
	end if
	-- if enabled the following line will produce odd behaviour if the user pressed cancel while the script executes: the export_statistics() routine won't show up!
	-- tell application "Mailsmith 1.1" to activate
	
	-- the final dialog stating how many msgs have been successfully exported:
	display dialog "Number of Messages that" & return & return & ¬
		"- were successfully exported:    " & (msgs_exported as string) & return & ¬
		"- failed to export:                        " & (msgs_export_failed as string) & ¬
		already_exported_info with icon note buttons "OK" default button 1
end export_statistics


-- This handler gets activated if there was no selection in Mailsmith:
on nothing_selected_alert()
	tell application "Mailsmith 1.5"
		activate
		display dialog "No messages were selected!" with icon caution buttons "Oops" default button 1
	end tell
end nothing_selected_alert


-- If a message fails to be parsed properly or can't be inserted into the database:
on handle_error(the_message)
	tell application "Mailsmith 1.5"
		set msgs_export_failed to msgs_export_failed + 1 -- counts the messages that failed to get exported
		set label index of the_message to 31
	end tell
end handle_error