Email Solutions for FileMaker Pro - Part Two
by Clint Roberts (croberts@metrotechnologies.com)
RATING: Intermediate
PLATFORM: Macintosh
VERSION: FileMaker 5 Only
BONUS FILE: EMAIL_BROWSER. FP5
In the previous issue (ISO 47.3 "Email Solutions for FileMaker Pro-Part One <http://www.filemakermagazine.com/m/a.read/issue.47/article.3> with bonus file EMAIL.FP3), I presented a simple Send Email technique ?le designed to send data stored in FileMaker Pro ?elds into either QuickMail Pro or Outlook Express (including a separate cross-platform technique using the Internet control panel to send data via your default Web Browser).
This article will discuss FileMaker's capability to archive email from two email clients - QuickMail Pro and Outlook Express. Additionally, I have added a Preferences layout which allows you to set your Default Send and Archive Email Client, and your Default Signature.
Taking full advantage of this solution requires you to download either the QuickMail Pro demo from <http://www.cesoft.com/demo.html>, or the Outlook Express client from <http://www.microsoft.com/ie/> or <http://www.microsoft.com/mac/download/default.asp>. Of course, if you currently have a copy of either of these email clients, you would not need to download them.
The goal of this integration is to show two different methods of archiving email using AppleScript. In the Outlook Express example, I grab the ?rst email of the folder "In Box". In the QuickMail Pro example, I grab the current active email.
The good news is that it works! The not-so-good news is that this functionality currently extends only to Mac OS users because AppleScript technology is used. Currently Windows users can only take advantage of the Send via Web Browser technique.
Before Sending or Archiving email, you must set your preferences in the Preferences layout: First Name, Last Name, Email Address, Default Send Email Client and Default Archive Email Client. Otherwise, you will receive an error message directing you to do so. To send email, switch to the Email Browser view, ?ll in the First Name, Last Name, Email Address, Subject, and Body ?elds. Then, click the Send button to Send the email.
General Usage Guildelines
Whereas this is a technique ?le and not a fully-functional application, some of the basic features of a full email browser are not present. Such features include:
- a separate interface for sent and received email
- complete sent and received date parsing and storage
- Windows compatibility
- ?ltering outgoing email messages to remove characters that would cause errors (such as: a "?" in the body of the email while using the URL Command feature
- error trapping in the AppleScripts to avoid alerts when a requested email ?eld is blank
- when email is sent from the database, the ?eld "Email Status" will be still be tagged as "Sent" even if an error is encountered while transferring the data to the email client.
Author's Note: Here are some general usage guidelines for using the Email_Browser.FP5 technique ?le:
1. Do not rename "Email_Browser.FP5" without updating the database name in the embedded and accompanying AppleScripts.
2. The ?rst time you launch Email_Browser.FP5 and run each Send AppleScript, you may be required to locate the appropriate email client if the application is not already running.
3. I have chosen to not embed the Archive Mail AppleScripts whereas a user would typically be browsing an email in their email client when they wish to archive it. Instead, I have left these AppleScripts in their compiled state to be placed in the appropriate email client's AppleScript menu.
Additionally, for Outlook Express, this folder is most likely in following location:
Macintosh HD:Internet:Internet Applications:Outlook Express:Outlook Express Folder: Script Menu Items
However, please note that my default location was somehow assigned in the following location:
Macintosh HD:Documents:Microsoft User Data:Script Menu Items
For QuickMail Pro, this folder is in the following location:
Macintosh HD:System Folder:CE Software:QuickMail Internet:Standard Scripts
Placing the AppleScript here will allow all users of this copy of QuickMail Pro to access this archiving feature. If you would prefer to give access to a particular user of QuickMail Pro, use the following location:
Macintosh HD:System Folder:CE Software:QuickMail Internet:Users:Clint Roberts:Scripts
Where "Clint Roberts" is the name of the sole user that you wish to use this feature.
4. If you are using QuickMail Pro, ensure that the QuickMail Pro application is open and you are logged into your email account before continuing with this sample ?le.
The AppleScripts of Email Browser: Sending Email
I won't spend any time discussing the AppleScripts involved in Sending Email since it was discussed in depth in Part One. However, for your reference I have listed the AppleScript code here.
The only change is the addition of an AppleScript step to set the database ?eld "Email Status" to the value "Sent" to indicate that the email has been sent.
Outlook Express
tell application "FileMaker Pro"
activate
set dbRecord to current record of database 1
set mySubject to get cell "Subject" of the dbRecord
set myBody to get cell "c_Body|Signature" of dbRecord
set myRecipient to get cell "Recipient" of dbRecord
set myCC to get cell "CC" of dbRecord
set myBCC to get cell "BCC" of dbRecord
set cell "Email Status" of dbRecord to "Sent"
-- store the value "Sent" in the database field "Email Status"
end tell
tell application "Outlook Express"
activate
make new draft window with properties {to recipients:myRecipient, CC recipients:myCC, BCC recipients:myBCC, subject:mySubject, content:myBody}
end tell
QuickMail Pro
tell application "FileMaker Pro"
activate
set dbRecord to current record of database 1
set mySubject to get cell "Subject" of the dbRecord
set myBody to get cell "c_Body|Signature" of dbRecord
set myRecipient to get cell "Recipient" of dbRecord
set myCC to get cell "CC" of dbRecord
set myBCC to get cell "BCC" of dbRecord
set cell "Email Status" of dbRecord to "Sent"
-- store the value "Sent" in the database field "Email Status"
end tell
tell application "QuickMail Pro"
activate
new Message
set Message mySubject as text
set Message Body myBody as text
set Message Recipients {class:Address, Recipients:myRecipient}
end tell
Send Via the URL Command
There are two FileMaker Pro ScriptMaker steps involved, as follows:
Set Field gEmailMessage
Calculation "mailto:" & Email Address & "?Subject=" & Subject & "?Body=" & Body
Open URL URL:
No dialog "gEmailMessage"
The AppleScripts of Email Browser: Archiving Email
Below are the scripts used for Outlook Express & QuickMail Pro. Highlight these scripts and copy and paste them into a new Script Editor document. Alternatively, you can use the precompiled scripts in the folder for the Technique file.
Outlook Express
tell application "Outlook Express"
activate
-- make Outlook Express the foremost application
set myDate to the time received of the last message of folder "InBox" as string
set myFrom to the display name of the sender of the last message of folder "InBox" as string
set myAddress to the address of the sender of the last message of folder "InBox" as string
set mySubject to the subject of the last message of folder "InBox" as string
set myBody to the content of the last message of folder "InBox" as string
-- grab the date, sender, email address, subject, and email body of the first email in the "Inbox".
-- store this data in the global variables myDate, myFrom, myAddress, mySubject, and myBody respectively
end tell
tell application "FileMaker Pro"
activate
-- make FileMaker Pro the foremost application
set myDate to myDate as text
-- convert the data held by myDate to text; this avoids a "data cannot be coerced" error.
go to database "Email_Browser.FP5"
-- bring the database window "Email_Browser.FP5" to the foreground.
set newRecord to (create new record at database "Email_Browser.FP5")
-- create a new database record in "Email_Browser.FP5".
go to newRecord
-- make sure that the newly created record is the active record.
set cell "Date" of current record to myDate
set cell "Last Name" of current record to myLast
set cell "First Name" of current record to myFirst
set cell "Email Address" of current record to myAddress
set cell "Subject" of current record to mySubject
set cell "Body" of current record to myBody
-- set the date, last name, first name, email address, subject, and body cells of the current record to the previously populated global variables.
set cell "Email Status" of current record to "Received"
-- store the value "Received" in the database field "Email Status"
end tell
QuickMail Pro
tell application "QuickMail Pro"
activate
-- make QuickMail Pro the foremost application
set myText to Get Message
-- grab the data from the active email message
set mySubject to Topic of myText
set myBody to Body of myText
set mySender to first item of Senders list of myText
set myFirstName to First name of mySender
set myLastName to Last name of mySender
set myFrom to Mail Account of mySender
-- grab the subject and body of the email, grab the first name, last name, and email address of the sender's list.
-- store this data in the global variables mySubject, myLast, myBody, myFirstName, myLastName, and myFrom, respectively.
end tell
tell application "FileMaker Pro"
activate
-- make FileMaker Pro the foremost application
go to database "Email_Browser.FP5"
-- bring the database window "Email_Browser.FP5" to the foreground.
set newRecord to (create new record at database "Email_Browser.FP5")
-- create a new database record in "Email_Browser.FP5".
go to newRecord
-- make sure that the newly created record is the active record.
set cell "First Name" of current record to myFirstName
set cell "Last Name" of current record to myLastName
set cell "Email Address" of current record to myFrom
set cell "Subject" of current record to mySubject
set cell "Body" of current record to myBody
-- set the first name, last name, email address, subject, and body cells of the current record to the previously populated global variables.
set cell "Date" of current record to (current date) as text
-- due to an apparent bug in QuickMail Pro's implementation of AppleScript, I have chosen to store the date the email was archived into the database's "Date" field.
set cell "Email Status" of current record to "Received"
-- store the value "Received" in the database field "Email Status"
end tell
Conclusion
In a future revision I will enable full functionality and make this technique ?le a FileMaker Pro-based application. Please feel free to send suggestions to croberts@metrotechnologies.com. In the meantime, check out Belimah Tech's Email Archiver 1.7 at http://www.belimah.com/. This solution will allow you to archive emails from Microsoft's Outlook Express and Qualcomm's Eudora Pro.
Happy FileMaking!
Clint Roberts is a Senior Developer for Metro Technologies <http://www.metrotechnologies.com>, a leading provider specializing in custom FileMaker Pro solutions. Clint has substantial FileMaker and IT/Networking experience and has a Master of Arts in Education, successfully completed various Master's-level education and management courses.