Automator/Applescript Help with FileMaker Pro and Mail

H

hkinzler

Registered
First time poster and such, not to mention newbie to automator and applescripts. I never before thought I would be able to use them, but after encountering FileMaker Pro(v8.5), I thought it would be a lot easier to have an automated script do the work for me.

The following is what I hope is possible:
After finding around 9600 contacts, I want to send a general e-mail to everyone, taking just their e-mail address, importing it into Mail, and then sending it, with a general message. (and I guess repeat 9600 times?) I've been searching awhile, and haven't really found a solution to my detailed query. I'd really appreciate anyone's help, and if they don't mind, please dumb down everything as much as possible, for I have never used either Automator or Applescripts. Also, if it were possible to customize each e-mail with their first name, and not include every single multiple contact in each address field, that would be great! Much Appreciated!
 
'After finding around 9600 contacts' - from where? Your 'Address Book'?, an 'Excel' spreadsheet?, a <tab> delineated text file?, ...

'... possible to customize each e-mail with their first name'(?) - yes.

'... and not include every single multiple contact in each address field' - yes. You would loop though a list of e-Mail addresses (obtained externally).

So, please reply with ...
01. The version MacOS X your Mac has.
02. The source of the e-Mail addresses.
03. The source of the recipients' first name.
 
Thanks for the fast response/help barhar! Here are the answers to your questions:
1) Mac OS X 10.4.11
2) The e-mail address are in FileMaker Pro (v8.5); its in a database along with other info (phone, address, etc.) so the e-mail is a specific entry field.
3) The first name is the same as the e-mail, obviously just a different entry field.

So the "contacts" are found using FileMaker Pro, and I want to take just two of the entry fields for each individual contact (e-mail and first name), and I guess somehow have it put into Mail, and have it customized for each one, etc., then send all 9600 to their respective contact. I hope I cleared up any confusion that you had from my first post.

Remember, I am practically useless when it comes to Automator and or AppleScripts, or whatever the solution maybe. So when you mentioned looping, I wouldn't even know where to begin. Thanks again barhar, you've already been a lot of help!
 
'... the "contacts" are found using FileMaker Pro ...' - I read your posts' title, then the post, and continued on not associating the two.

Let me see what I can contribute ...
 
Enter the following into 'Script Editor', edit where appropriate, and save as a 'Script' ('.script') or application ('.app' - which is then called an AppleScript applet).

-- Code starts here --

set fName to "HD02_232.03_007:Users:barhar:Desktop:Untitled.fp7" -- Full path to 'FileMaker Pro' database file.

property firstNameCell : "First_Name" -- Name of 'First Name' assigned cell.
property eMailAddressCell : "eMail_Address" -- Name of 'e-mail Address' assigned cell.

property eM_Salutation : "Hello"
property eM_Subject : "Your subject here"
property eM_Contents : "Your e-Mail message content here"

tell application "FileMaker Pro Advanced 8.5"
activate

open file fName -- Open database file.
set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

-- quit -- Optional.
end tell

tell application "Mail"
activate

repeat with i from 1 to (count eMailAddresses)

set nMessage to make new outgoing message with properties {visible:false, subject:eM_Subject, content:(eM_Salutation & " " & (item i of firstNames) & "," & return & return & eM_Contents)}
tell nMessage
make new to recipient at end of to recipients with properties {address:(item i of eMailAddresses)}
send
end tell
end repeat

-- quit -- Optional.
end tell

-- Code ends here --

Also, to become familiar with AppleScript code - consider subscribing to Apples' AppleScript discussion group and / or its 'AppleScript-users' list serve. Other Apple developer list serves.
 
I can't thank you enough for actually writing the whole script! After I changed some of those paths, or " ", and compiled and ran it, it gave me two errors. The first one, FileMaker Pro error "apple events or macinosh systems error -35"
Now, I think it's because I'm not correctly typing the file path to define "fName"
This might be because the database file is on a local server. The way I open the file is from "Remote" with the address being "fmnet:/192.168.0.103/Applicants"
Is that right?

Hopefully, after I get the file path right, everything should be great, except for a second error that occured after a made a test file path, and ran it. It used to stop after opening FileMaker Pro, but this time it actually opened Mail. Then an error popped up: "Can't make item 1 of {} into type string" I don't even really understand why this is coming up, but my best guess would be maybe it can't for some reason in the code. I bolded it below I think.

set nMessage to make new outgoing message with properties {visible:false, subject:eM_Subject, content:(eM_Salutation & " " & (item i of firstNames) & "," & return & return & eM_Contents)}
tell nMessage
make new to recipient at end of to recipients with properties {address:(item i of eMailAddresses)}


Other than that, Hopefully if nothing else comes up, I should be golden. You've been a lot of help, I've learned so much already, I might even be able to figure it out soon. (But I'll still take any advice!)
 
Based on the information you provided. replace ...

set fName to "HD02_232.03_007:Users:barhar:Desktop:Untitled.fp7" -- Full path to 'FileMaker Pro' database file.

... with ...

set fName to "smb://workgroup;username:password@192.168.0.103/Applicants" -- Or similar; where 'username' and 'password' are supplied by you.

... or similar. I have no experience or idea about 'fmnet:/...'.

'"Can't make item 1 of {} into type string"' - yes. Attempting to obtain an expected item from an empty list should result with an AppleScript error; unless - if 'try' ... 'end try' are used.

There is zero error checking in the provided code.

Final code (with some checking of the size of the lists) is provided below:

-- Code starts here --

set fName to "smb://workgroup;username:password@192.168.0.103/Applicants" -- Or similar, where 'username' and 'password' are supplied by you.

property firstNameCell : "First_Name" -- Name of 'First Name' assigned cell.
property eMailAddressCell : "eMail_Address" -- Name of 'e-mail Address' assigned cell.

property eM_Salutation : "Hello"
property eM_Subject : "Your subject here"
property eM_Contents : "Your e-Mail message content here"

tell application "FileMaker Pro Advanced 8.5"
activate

open file fName -- Open database file.
set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

-- quit -- Optional.
end tell

set eMailAddressesCount to count eMailAddresses -- Obtain number of items of 'eMailAddresses' list.
if ((eMailAddressesCount > 0) and ((count firstNames) = eMailAddressesCount)) then -- Lists must be equal in items, and greater than 0.
tell application "Mail"
activate

repeat with i from 1 to (count eMailAddresses)
set nMessage to make new outgoing message with properties {visible:false, subject:eM_Subject, content:(eM_Salutation & " " & (item i of firstNames) & "," & return & return & eM_Contents)}
tell nMessage
make new to recipient at end of to recipients with properties {address:(item i of eMailAddresses)}
--send
say "Sent"
end tell
end repeat

-- quit -- Optional.
end tell
else -- This line, and the next four (4) lines, are optional.
set errorMessage to "An error occurred"
activate
say errorMessage
display dialog errorMessage buttons {"OK"} default button 1 giving up after 5
end if

-- Code ends here --
 
Last edited:
Again barhar, your solutions are working! However, I am finding out more and more complications...so, if this thread is still active, I have a couple more things to take care of, but I feel if anybody else, you or even me find a solution to have this script run smoothly, it could be a great useful tool for others that happen to find themselves in the situation that I'm currently in.

First, I was having the most trouble with FileMaker Pro (v8.5) opening the database file, which is stored remotely on a server. AFter trial and error, along with other forum help searching, I found a solution that worked for me, with the line:

getURL "fmp7://[username?]:[password?]@192.168.0.103/[databasefile]"

Now, after being able to have FileMaker open the file "Applicants" remotely without prompting me for the username and password, I have run into a new program. My applescript knowledge is limited, (but I'm learning more and more now!) but the error I get now is:

FileMaker Pro got an error: Object not found.

With that dialog box, a section of code is highlighted:

{(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

I hope that this error is due to FileMaker not being able to find said emails and firstnames, and not something completely different.

In the mean time, I will search my heart out to find a solution to this "Object not found" error with the cells. (I think its something with the cells?)

Thank again!
 
On another note, I just realized one more kink in this wheel of a problem I've been trying to solve. I forgot to mention that this database holds around approximately 19,000 contacts. I then perform a simple search query to filter out contacts that have been in the database before 5/1/2006, and have e-mail contacts. When I get results, around 6400 contacts are found. (this is different than the previous 9000 I mentioned, I refined the search to be more detailed)

Now, it would be great if somehow the script that is in the process of being tweaked (the one barhar has graciously been writing and editing) could somehow filter this, but I feel that this might be somewhat of a hard task. I guess if worst comes to worst, all 19,000 contacts could be e-mailed; but the sheer fact that the script would take their first name and e-mail, and be able to automatically do this would be worth it.

Good Luck and Thanks to anyone helping in the past and future!
 
Well, I was able to manage scripting a filter for the problem I stated above. The code:

show (every record whose cell "GEN.LASTACCESS" is less than "5/1/2006" and cell "Gen.EMAIL" is "*")


This now cuts the 19,000 records to a mere 6,400. All thats left is fixing the cell values, getting them into mail, and start sending those e-mails! I hope someone out there can offer any solution. Forums used to be something I paid no mind to, but now I might become an avid poster!

Note-Because of the security of the database I'm working with, I don't have some privledges to modify cell values/data. I hope that this won't be a problem (probably will be, what isn't¿) If it is, I'm sure I can get the rights, and everything then will work smoothly. Modifying those values, the "set":

set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

This is what the scripts hung up on now. Thanks and Good Luck for past and future help!
 
With reference to ...

set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

..., at the beginning of my second reply is ...

property firstNameCell : "First_Name" -- Name of 'First Name' assigned cell.
property eMailAddressCell : "eMail_Address" -- Name of 'e-mail Address' assigned cell.

... You must replace 'First_name' and 'eMail_Address' (which are names I used when creating a test database file, to check the code written and posted) with the actual respective cell names of your FileMaker database file.
 
One of the first things I realized barhar was the difference in the code, and how I would have to change it to fit my file/misc. locations. The first name and e-mail fields are defined as "GEN.FNAME" and "GEN.EMAIL", respectively. They have been in the property line since you first coded it, however, now I don't know why its not finding those cells, or field values. Below is what I have:

property firstNameCell : "GEN.FNAME" -- Name of 'First Name' assigned cell.
property eMailAddressCell : "GEN.EMAIL" -- Name of 'e-mail Address' assigned cell.

property eM_Salutation : "Dear"
property eM_Subject : "Updating Contact Database"
property eM_Contents : "Message...I took it away for text purposes..."

tell application "FileMaker Pro"
activate
getURL "fmp7://[username]:[password]@192.168.0.103/Applicants"
tell table "Applicants"
show (every record whose cell "GEN.LASTACCESS" is less than "5/1/2006" and cell "Gen.EMAIL" is "*")
set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

-- quit -- Optional.
end tell

set eMailAddressesCount to count eMailAddresses -- Obtain number of items of 'eMailAddresses' list.
if ((eMailAddressesCount > 0) and ((count firstNames) = eMailAddressesCount)) then -- Lists must be equal in items, and greater than 0.
tell application "Mail"
activate

repeat with i from 1 to (count eMailAddresses)
set nMessage to make new outgoing message with properties {visible:false, subject:eM_Subject, content:(eM_Salutation & " " & (item i of firstNames) & "," & return & return & eM_Contents)}
tell nMessage
make new to recipient at end of to recipients with properties {address:(item i of eMailAddresses)}
--send
say "Sent"
end tell
end repeat

-- quit -- Optional.
end tell
else -- This line, and the next four (4) lines, are optional.
set errorMessage to "An error occurred"
activate
say errorMessage
display dialog errorMessage buttons {"OK"} default button 1 giving up after 5
end if
end tell


On another note, would you mind briefly describing certain commands? I'm a bit confused what these commands mean, and hope you could clear up any confusion that I have.

1) The "set {firstnames,emailaddresses}" command line, what exactly does that do? is it renaming or modifying the cell values? My access privledges might not allow this...

2) Is the counting command neccessary? I understnad what it does (@ least I think I do...lol) I did get the whole script to run only once (it only needs to work once, natch) but it didn't do anything; other than open up and log into the database file, filter the records, then pull up mail, and then the mac says "Sent" <---nice touch!

3)Lastly, what exactly do the last 5 lines do exactly? Is it to combat any errors that arise?

Other than that, I'm feverishly searching for solutions, it seems the only one able to script well (not to mention, help) is barhar. Many thanks again, I hope we can get this to work!
 
Reply to '1)':

set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

... is no different than ...

set firstNames to (every cell of document 1 whose name is firstNameCell)
set eMailAddresses to (every cell of document 1 whose name is eMailAddressCell)

... The line of code ...

set firstNames to (every cell of document 1 whose name is firstNameCell)

... creates a list, titled 'firstNames', which contains all of the 'firstNameCell's of the database. Where 'firstNameCell' is 'GEN.LASTACCESS', in your database.

The line of code ...

set eMailAddresses to (every cell of document 1 whose name is eMailAddressCell)

... creates a list, titled 'eMailAddresses', which contains all of the 'eMailAddressCell's of the database. Where 'eMailAddressCell' is 'Gen.EMAI', in your database.

Reply to '2)':

Yes. The comment '-- Lists must be equal in items, and greater than 0.' as the end of the line of code is self explanatory.

If you look at the line above the 'say "Sent"' ... it is '--send'. You will have to remove the two '--'es (minus signs). The duo minuses 'comments out' the line. I did not realize I did not remove the '--'s when pasting the code into the reply. [I had no intention of sending countless e-Mail messages while testing the code, thus the '--'es ]

Remove the '--'es, and the script will actually send each person an e-Mail message.

Reply to '3)':

One has to look as the code ...

if ((eMailAddressesCount > 0) and ((count firstNames) = eMailAddressesCount)) then -- Lists must be equal in items, and greater than 0.
.
.
.
else -- This line, and the next four (4) lines, are optional.
set errorMessage to "An error occurred"
activate
say errorMessage
display dialog errorMessage buttons {"OK"} default button 1 giving up after 5
end if

..., Thus, if there are not an equal number of 'First_Name' and 'eMail_Address' items in the respective 'firstName' and 'eMailAddresses' lists - the code beneath 'else' is executed. You would hear 'An error occurred' and will see a dialog box, for up to five (5) seconds, displaying 'An error occurred'.

--

In conclusion:

Locate ...

--send

..., rename it as ...

send

..., and execute the code.
 
Very through explanation barhar, however, I still am having trouble with the script. I am getting (which has been what I've always been getting) an error that says that FileMaker Pro could not find object. Then this is highlighted: every cell of document 1 whose name is firstNameCell

Now, I have a question to ask. Is "document 1" the default file? or should I leave it like that? I'm a bit confused as to where to find it; seeing as I have only a database file located on a remote server.

On a side note, I'm pretty sure these are the correct cell names, I got them by accessing the template/form editor in FileMaker Pro and logically determined that "GEN.FNAME" is first name and "GEN.EMAIL" is e-mail. "GEN.LASTACCESS" is only data pertaining to the date. That 'filter' that I made, does reference "GEN.LASTACCESS" and "GEN.EMAIL", allowing me to just have only records with contacts who have e-mail addresses and were last accessed anytime before 5/1/06. I hope this helps with any confusion that anyone might be having, I'm sure I am still a little confused, but I can feel that the solution is there!

Thanks again barhar!
 
hkinzler said:
FileMaker Pro got an error: Object not found.
Click to expand...

I got this error when adding a Perform FileMaker Script action to an Automator script:
FileMaker Pro got an error: Object not found. (-1728)

The list of scripts in the database would not load. This is what solved it for me:
Go to Applications > AppleScript
Open AppleScript Utility
Next to GUI Scripting, check the box labeled Enable GUI Scripting - make sure it's checked. Close AppleScript utility.
Quit FileMaker and Automator.
Re-open FileMaker and the database(s) you're dealing with.
Re-open Automator and it worked as expected.
 
You must log in or register to reply here.
Back
Top