Automator/Applescript Help with FileMaker Pro and Mail

[hkinzler]

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!

[barhar]

'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.

[hkinzler]

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!

[barhar]

'... 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 ...

[barhar]

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.

[hkinzler]

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, contenteM_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!)

[barhar]

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 --

[hkinzler]

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!