mail script sending different recipients different attachments

[Charles Henebry]

I have a database where each record has an email address and a calculation field formatted as a container. I want to create a script which will send out emails containing the file referenced in the container field to each individual recipient. (I'm a teacher; the records = my students, and the container field references their essays, which I have graded and want to return to them.)

The system works fine so far as the email part goes, except that Filemaker's email script step only appears to be able to accept a single file for attachments—that is, you're forced to choose which file to send while editing the script. That strikes me as a useless feature, but maybe I'm missing something. Or perhaps this deficiency has been fixed in a newer version of FM—I'm using FM7? Or perhaps there's a workaround for doing this in FM7?

That's why I'm posting here; you guys really know what you're doing; I'm just a humble teacher trying to send my 50 students their corrected essays with a minimum of fuss. Any way to automate this part of the email process?

[bdonelson]

I am not sure if this will work in FM7, but in FM8.5 the script step allows you to specify the filename using a global variable in the options.

For example, $$filename in place of the actual filename.

[aldipalo]

I'm not sure you can do that without a plugIn. I know I use SMTPit and it will allow you to assign a field with the path to the script and attach the file. I'll bet MAILit and 360Works will also do that as well. It would be interesting to see if someone has a way to do it.

Edited November 15, 2008 by Guest

[aldipalo]

Yes, but, doesn't that, $$filename, then make the variable a global variable and assign the same path/file to all emails? And would you assign a container to the variable or a text field with the referenced path?

I guess that would work on a single email, but, what about mass emails?

[bdonelson]

Setup within a loop you can set the variable for each recipient.

Here is a topic that covers the method for setting the value of the variable. Variable Filenames

[Charles Henebry]

I see what you're doing here, but I can't figure out how to set a variable as a script step. Is Set Variable a new feature on FM 8 that's missing from FM 7?

[comment]

I haven't tested this, but you might be able to do it in version 7 by exporting the container field contents to a hardcoded path, then sending the mail with the same hardcoded path to the attachment (looping through the records, every iteration will overwrite the previously exported file).

[Charles Henebry]

By hardcoded path, are you suggesting a script step that would create a duplicate of the current student's essay and rename the file to the same name, then send an email using that filepath? That's clever!

But how do I make changes to files from within FM? I'm running FM 7 on Mac OS 10.5. Are there script steps to modify external files?

[comment]

Use the Export Field Contents[] script step.

[Fenton]

Yes, it's a new option in FileMaker 8, so you'd have to do as comment says. You would also need to actually send each of those emails as you went (check [x] Perform without dialog). Because you're overwriting the file each is pointing to each time. It would be easier and faster in FileMaker 8, as you could use a calculated path to the file, set it into a Variable (local, not global), using FileMaker syntax; you wouldn't need to export it.

[bdonelson]

Apparently, Set Variable is new for FM 8. Sorry, this method does not appear to work for FM 7.

[Charles Henebry]

OK. I've got it working, but only partially. I'm currently running tests with just two students' records, and I've given them email addresses for accounts that I control so I can see the results.

The system works great if I run a Find which isolates just a single student. But, when run the script so that it emails both students, it winds up sending both Student B and Student C the attachment which belongs to Student C. The strange part is that all the other parts of the email sent to Student B are correctly personalized: not just the address but the name and the message body. Only the attachment is incorrect.

This even happens if I delete the temp file before running the script. Somehow, when the script looks at Student B, it winds up exporting the file which belongs to Student C. This despite the fact that when I'm in Browse mode I can see the correct file belonging to Student B in the container field — and I can click on it.

Even stranger, when I added just now a third test student (call him Student A, since he comes earlier in the alphabetical list order), all three emails come with the attachment that belongs to Student B. So once again the emails all get the same attachment, but this time a different one. In both cases, all emails get (strangely) the second student's attachment.

The Container field is really a calculation field with the following formula:

"file:"& "folder/"& filename

I'm not sure if this is the issue or not.

Edited November 16, 2008 by Guest

[comment]

Try pausing the script for a few seconds before going to the next record in the loop, to give the mail client enough time to send the file before it's overwritten.

[Vaughan]

I would not run FMP 7.0 in Mac OS X 10.5.

FMP 8.5v2 (note the v2) was the first version certified for Leopard, as 8.5v1 and earlier had numerous problems especially with non-US setups.

[Charles Henebry]

Aha! I get it now. The script writes the file and sends a prompt for the email client to create a new message with that file attached. Then, while the email client is in the midst of following this instruction, FM is busy overwriting the file and sending Mail a prompt for a second message, etc.

This understanding of the scenario makes me think that this workaround (clever though it is) isn't a great solution for me. The cost of accidentally emailing a graded essay to the wrong student is very high. And, while I can ask FM to pause for some arbitrary length of time, I'm anxious that with 50 outgoing messages at some point along the line Mail will slow to a crawl.

If 95% accuracy were good enough, I could set the pause to some reasonably long period (say 10 seconds), and let the whole job unfold over the course of three minutes. But accidents happen, Mail isn't always fast or stable, so the savings in effort may not be worth the risk. Even an unreasonably long pause (60 seconds) might not be enough if Mail got itself in real trouble. And, at that point, the bulk mailing would take nearly an hour to complete.

[comment]

I see. Well, then I think your options are:

1. Upgrade to version 8 or higher;

2. Get a plugin;

3. Use Applescript (if this runs on Mac exclusively).

[Fenton]

Which pretty much reinforces the argument to upgrade FileMaker to a version which has the built-in ability to handle file paths; 7 doesn't. Or set up and send the emails and attachments with AppleScript's help; which would have no problem just grabbing the actual files as they are.

As you can see, data can be set into AppleScript variables from FileMaker fields. This is a basic email with Apple Mail, with possibly multiple attachments, with testing to make sure the attachment file is actually there. The example file has the below embedded in a FileMaker Perform AppleScript step. Of course, your field names are going to be different, so you're going to have to adjust those (at the least).

It could be simplified somewhat if you have only 1 attachment; but it will work as is for only 1 also.

-- Basic create/send email with attachments via Apple Mail



tell application "FileMaker Pro Advanced"

	tell current record

		set theSubject to cell "Subject"

		set theMessage to cell "Message" & return & return

		set theRecipient to cell "Recipient"

		set att_txt to cell "Attachments"

		-- return-separated Mac:file paths

		set atts_txt to paragraphs of att_txt

		-- text to AppleScript list

		set theSender to cell "Sender"

	end tell

end tell



-- test for each attachment file; add to list as alias

set theAttachments to {}

try

	repeat with i in atts_txt

		set theAttachments to theAttachments & alias i

	end repeat

on error

	beep

	display alert "Error with Attachments" message "Could not find attachment file:" & return & (i as text) buttons {"Cancel"} default button 1

	return

end try



tell application "Mail"

	launch

	-- launch does not bring mail to the front, nor show the "message viewer"

	-- "activate" can be used instead, or added here to bring it to the front

	set newMessage to make new outgoing message with properties {subject:theSubject, content:theMessage}

	tell newMessage

		-- set visible to true

		-- if you want to see the message

		if theSender is not "" then

			set sender to theSender

			-- optional; otherwise uses default email account

		end if

		make new to recipient at end of to recipients with properties {address:theRecipient}

		repeat with i in theAttachments

			make new attachment with properties {file name:i} at after the last paragraph

		end repeat

		

		save newMessage

		--send newMessage

		-- remove the commenting on the line above to send immediately

	end tell

end tell

Mail_wAttach_simple.zip

[Charles Henebry]

Very impressive, Fenton!

It crashes Mail for some reason when I include more than one attachment in the list of attachments, but I think I can make it work for my purposes with just a single attachment on each email.

[Fenton]

Sorry. Maybe it's a Panther thing. I'm using Tiger, and it works fine. Don't know about Leopard. Yes, I guess you better stick to 1 attachment. Here's a modified script, for only 1 attachment. It has one other feature. Its AppleScript dialog has a "giving up after 10," which means it will close the dialog after 10 seconds (wish FileMaker had this), then exit the script. So it will not stop the Loop if you have a FileMaker record with no attachment; neither will it email that person.

You can copy/paste this into it the FileMaker Perform AppleScript step (save the old one, just in case). Also, comment out the "tell FileMaker" line (and its end tell line); they are not needed/wanted within FileMaker. I usually keep them however, so I can copy/paste a script back out into Script Editor to edit (then comment them out again when I paste it back).

-- Basic create/send email with 1 attachment via Apple Mail



-- tell application "FileMaker Pro Advanced"

	tell current record

		set theSubject to cell "Subject"

		set theMessage to cell "Message" & return & return

		set theRecipient to cell "Recipient"

		set att_txt to cell "Attachments"

		set theSender to cell "Sender"

	end tell

-- end tell



-- test for attachment file

try

	set att_file to alias att_txt

on error

	beep

	display alert "Error with Attachment" message "Could not find attachment file:" & return & att_txt buttons {"Cancel"} default button 1 giving up after 10

	return

end try



tell application "Mail"

	launch

	-- launch does not bring mail to the front, nor show the "message viewer"

	-- "activate" can be used instead, or added here to bring it to the front

	set newMessage to make new outgoing message with properties {subject:theSubject, content:theMessage}

	tell newMessage

		-- set visible to true

		-- if you want to see the message

		if theSender is not "" then

			set sender to theSender

			-- optional; otherwise uses default email account

		end if

		make new to recipient at end of to recipients with properties {address:theRecipient}

		make new attachment with properties {file name:att_file} at after the last paragraph

		

		save newMessage

		--send newMessage

		-- remove the commenting on the line above to send immediately

	end tell

end tell

Edited November 16, 2008 by Guest