trying to create bulk letter & email module

[mahesh]

i am setting up a contact, reservations, and donations database for a local theater. i am stuck on the letter/email part of this. mass letters/emails will need to be sent based on various search criteria (group, name of last show, etc.). doing the search to create a found set is not the problem. with that found set, i do not know the best way to go about the letter database. i know how to simply create the letter with the found set, but i need that letter to have other custom fields inline with the body of the letter so the letter seems personalized. i also need the letter date field to automatically enter itself into the main contact database in a field that tracks all communications to that contact. i am assuming the letter database will need to be its own file. but do i need to create a new file for each mass letter i send out? and each mass email? if not, how do i change the body of the letter/email within the same file? i know i can do different layouts, but then how do i know which layout someone actually got later on down the road? i have looked into ready-made solutions such as dbMailerPro, directMAIL, and fmMailMerge. all of these have cool features, but none give me everything i want. the closest was fmMailMerge. i like that it copies the date of the letter into the contact database into a field named "last contact", although i want it to track ALL letter dates in that field. i really like most of its features, except there is no letter feature - it is only for email and fax.

another more simple question... if i set up a field in the reservations database to track the "show name", so that eventually and 1 contact may have several show names attached to their name, how do i get all of the "show name" field from all of the records associated with that contact into a letter or email for personalization?

any ideas? many many thanks in advance.

[danjacoby]

I have developed a couple of modular solutions that contain mail merges -- including one for a ticketing agency here in NYC, and one for actors to do targeted mailings. E-mail me at [email protected] for details.

[Fenton]

I've recently done a bulk mail project. Without going into too many gory details, here's an overview.

I used a "bulk log" file. You can import the IDs of the found set of Contacts into this file, as well as the ID of the letter (which you set into global in the mail file), also a creation date (or import an unstored StatusCurrentDate field; can't remember which is faster). It's a small file with many records. The import goes fairly quick, even with a thousand.

A relationship to this file, based on Contact ID, sorted descending by Date, would show the last entry, from Contacts.

How to do the letters is another (long) story. I did not do what I call "layout-based" letters, because they wanted user-entered custom "merge" fields, within the body of the letter. This complicates things. Some other letters did not have this, but had formatting.

In either case, they wanted to be able to easily edit the letters, in Browse mode. So my letter bodies were in a field. It would take a long time to go into all the techniques and problems, but it is doable, if you must.

The main point in all this is that you must find out from your client what it is they want, and are willing to pay for, before you build the details of the bulk letter procedure. Key questions are: Do they need any user-entered merge fields within the body? Do they want user-editable formatting within the body? These 2 are mutually exclusive, without advanced techniques.

If "Yes" to the first, but "No" to the 2nd, then use Substitute calculation fields. If "No" to the first, but "Yes" to the 2nd, then use related fields on the letter layout.

If "No" to both, i.e., formatting, but not user-editable in Browse mode, then a "layout-based" technique may be the best. You'd need to know which layout to go to, but this can be done. Either a calculation field or a separate little file could relate layout numbers to names.

If "Yes" to both, you'd better ask for more help; you're going to need it. Add $$$$ :-)