|
|
Gammadyne Mailer: Design and Operations
|
 | C:\Rainbow\Marketing\Email-Merge Projects\Chicago, 2012-05.HTM |
| C:\Rainbow\Marketing\Email-Merge Projects\Chicago, 2012-05.TXT |
| C:\Rainbow\Marketing\Email-Merge Projects\Chicago, 2012-05.GMM |
The fourth file could have been named C:\Rainbow\Marketing\Email-Merge Projects\Chicago, 2012-05.DBF, and I discuss the advantages and disadvantages of this approach later. However, in our case study, the recipient list is exported for each project in the file C:\Rainbow\Church\EMailMrg.DBF.
|
File |
Directory |
Description |
|
Chicago, 2012-05.HTM |
C:\Rainbow\Marketing\Email-Merge Projects |
This is the HTML form of the personalized email message, with Gammadyne merge slots. |
|
Chicago, 2012-05.TXT |
C:\Rainbow\Marketing\Email-Merge Projects |
This is the plain-text form of the personalized email message, with Gammadyne merge slots. |
|
EMailMrg.DBF |
C:\Rainbow\Church |
This is the recipient database. |
|
Chicago, 2012-05.GMM |
C:\Rainbow\Marketing\Email-Merge Projects |
This is the Gammadyne Mailer project file. |
For its source data, Gammadyne Mailer uses up to three input files which are prepared by other programs. It creates a Gammadyne Mailer project file with a GMM extension to keep track of the source files and other data it requires in order to email personalized messages to persons in the recipient list. It is possible to store the HTML form of your email message, the plain-text form of your email message, and your recipient list in the project file.
The diagram below shows the inputs and outputs for a typical Gammadyne Mailer project. In this typical case, an HTML editor prepares the HTML form of the email message, a plain-text editor prepares the plain-text form of the email message, and custom database software prepares the recipient list.
Work-Flow For Producing Personalized Email
Messages
Example: Chicago Appearances, May 2012
Project Name: Chicago, 2012-05
Not shown are some quality-control feedback loops, which operate as follows:
| The recipient list is exported to the file EMailMrg.DBF from the main database. At export time, the user is told the number of persons exported under any specified subset definition, and the software presents the exported data in a table format for the user to review. For example, if the recipient list is supposed to be for Florida residents, the user can scan the STATE column in the exported data. If mistakes are detected, the user exports again with a different subset definition. |
| After the user builds the Gammadyne Mailer project for a particular mass email operation, the user previews one or more messages inside Gammadyne Mailer. For example, if the first person on the recipient list is Alan Abbott, Gammadyne Mailer will show you a preview of the text form and HTML form of the message personalized for Alan Abbott. One quality control check is to verify that the "dear line" in the text form and HTML form of the message reads "Dear Alan," (or whatever is expected). If you are putting contact information in the email message, does this contact information appear in both the plain-text form and the HTML form of the message? (Gammadyne Mailer displays message previews in small windows in a full-screen preview dialog. The HTML is shown in source form, and therefore the presence of all the codes can make it difficult to verify that the HTML form of the message has correct content.) A final quality-control step is to send a test message to your own email account. For example, you might send a message customized for Alan Abbott to your personal email account so that you can proofread the final HTML product, with database data merged in, and with fonts, styles, and colors. In our experience, we find many mistakes at this step, such as incorrect times or dates, wrong addresses, and missing information! Mistakes send us back to the plain-text and HTML forms of the email message. Gammadyne Mailer gives push-button access to these files, so it is mercifully easy to edit the files! |
This workflow assumes a standard Windows configuration. We use the Windows applet NotePad to prepare the plain-text version of the email message; any Windows-based, plain-text editor can substitute for NotePad. We use Microsoft FrontPage 2000 to prepare the HTML form of the email message; any HTML editor can substitute for FrontPage 2000, since email messages must consist of simple HTML in order to be displayed properly by as many email programs as possible. We do not use scripts or even Cascading Style Sheets; instead, we rely on templates which we have refined over a period of time.
In our Rainbow Ministries case study, we use the same data source for each project by always exporting the Recipient list to C:\Rainbow\Church\EMailMrg.DBF. This approach has these advantages:
This approach has these disadvantages:
Gammadyne Mailer offers you the option of entering short recipient lists by hand, or you can copy-and-paste from a source such as a plain-text list of email addresses. If you specify recipients in a data file, you'll need to define an appropriate ODBC connection to the directory where Gammadyne Mailer will find your data files. These are the basic steps in defining an ODBC connection:
We use an obsolete (though still functional) database program called Foxpro for DOS to run our legacy application and maintain our People Database. (In the Rainbow Ministries case study, we export data from the People Database to the Recipient List file C:\Rainbow\Church\EMailMrg.DBF.) However, Gammadyne Mailer uses a universal database connectivity service called ODBC to import database data for all Gammadyne Mailer projects in which the Recipient List is in a separate file. The ODBC service is a part of Windows which supports virtually all database formats. In the case of Foxpro for DOS, ODBC supports the native Foxpro for DOS .DBF format. If ODBC does not support your database program's native format, it should be possible for your database program to export a plain-text version of your data which Gammadyne Mailer can import through the plain-text driver offered as an option by ODBC.
The following instructions are taken from version 21.1 of Gammadyne Mailer's help file for the Guides\Database Registration topic listed under a search for the term ODBC:
dBase
1. In Gammadyne Mailer, select "Register database" from the "Database" menu. The ODBC Data Source Administrator will appear.
2. Click on the "System DSN" tab.
3. Click on the "Add" button.
4. In the driver list, click on "Microsoft dBase Driver". Click on the "Finish" button.
5. Type in a friendly name for the database in the "Data Source Name" field. This name will appear in Gammadyne Mailer's drop down list.
6. Uncheck the "Use Current Directory" box.
7. Click on the "Select Directory" button. Locate the directory that contains your database file and click on the "OK" button.
8. Click on the "OK" button to exit the ODBC Data Source Administrator.
If Gammadyne Mailer had native support for .DBF files, it could access a .DBF file in any directory. Instead, it accesses .DBF files (and all other database files) through named ODBC connections, each of which gives Gammadyne Mailer access to database files of one type in one directory. For example, if you store your Recipient Lists as Microsoft Access database files in ten directories, you must have ten ODBC connections for Microsoft Access files, with each connection defined for one of the ten directories.
Inside Gammadyne Mailer, on the Database branch, you'll specify the name of an ODBC connection, which points to one directory, and you'll further specify the name of the database file which holds the Recipient List. Gammadyne Mailer then uses the ODBC connection to get the names of the fields used in the Recipient List file. You pick one of these field names from a list as the Recipient Column. The data in that column or field should contain the email address.
In our example, the recipient file is always C:\Rainbow\Church\EMailMrg.DBF. That means the ODBC connection must point to C:\Rainbow\Church\. We name this ODBC connection Rainbow Database Files. Inside Gammadyne Mailer (on the Database branch), we specify the ODBC connection Rainbow Database Files in the Use database box, we specify EMailMrg in the Table name box, and we specify EMail in the Recipient column box.
When you define an ODBC connection, you can specify the directory explicitly as in steps 6 and 7 above, or you can check the "Use Current Directory" checkbox. If you check this checkbox, the recipient database files must be stored in the directory where Gammadyne Mailer is installed — typically C:\Program Files\Gammadyne Mailer\. Specifying a different directory when defining the ODBC connection permits you to manage your recipient file or files in a way that is appropriate for a directory holding data files.
If you always export your recipient list to C:\Rainbow\Church\EMailMrg.DBF, you specify C:\Rainbow\Church\ as your ODBC directory. If you use a project-specific file for your recipient list, you would logically store your project-specific recipient list with the other project-specific files, and specify your ODBC directory as the directory where you store your Gammadyne Mailer project files. For our sample project, that directory is C:\Rainbow\Marketing\Email-Merge Projects\.
The logical name format for a project-specific recipient file is <project name>.<database file extension> (for example, Chicago, 2012-05.DBF). If you use this name format, all of your project files are stored in the same directory with names of the format <project name>.<file extension>. When you point Windows Explorer to your email-merge projects directory, a sort on name shows these file names for the sample project Chicago, 2012-05 and the .DBF database file extension:
Chicago, 2012-05.DBF
Chicago, 2012-05.GMM
Chicago, 2012-05.HTM
Chicago, 2012-05.TXT
Sweet!!!
Your recipient database contains a row for each person and a column for each type of data you export. If your recipient database has columns named name and email, you could insert a person's name and email address into your email message by starting them as follows:
[[EMAIL]]
Dear [[NAME]],
Note that the examples in the online help for Gammadyne Mailer 21.1 show the field names in lower case, but I have found it necessary to use upper case field names, as shown.
For a record with NAME = "Charles Babbage" and EMAIL = "CBabbage@Mechano.COM", the email message would begin
CBabbage@Mechano.COM
Dear Charles Babbage,
In the HTML version of your email message, you may apply fonts, colors, and styles, as in
[[EMAIL]]
Dear [[NAME]],
to produce a message for Charles beginning:
CBabbage@Mechano.COM
Dear Charles Babbage,
We use the ten-character ZIPC field for any postal code which is fewer than 11 characters long. U.S. ZIP codes must have exactly 5 or 9 digits. We validate the combination of state and the first three characters of ZIPC when the country is the U.S. or Canada. We validate the combinations of home phone area code, work phone area code, and the first three characters of ZIPC when the country is the U.S. or Canada.
We use our own lookup database to validate the prefix field. If the user enters a prefix which is not in the PREFIX database, we present the user with a popup list of prefixes from the PREFIX database. In February 2004, the contents of our PREFIX database are:
Bro.
Brother
Director
Dr.
Dr. and Mr.
Dr. and Mrs.
Drs.
Miss
Mr.
Mr. and Mrs.
Ms.
Rabbi
Rev.
Rev. Dr.
Revs.
Sir
Sister
The maximum length of these English-language prefixes is 12. We allow up to 14 characters for prefixes in the Prefix Database and People Database.
Gammadyne Mailer merges your plain-text and HTML messages with the data in your recipient list. You have three basic alternatives:
Our sample project uses option (3). This option gives the highest degree of flexibility in our case, since we can use a powerful, general-purpose programming language to apply arbitrarily complex logic in the production of error-free and user-friendly email-merge fields. In fact, we exploit this power in order to solve an number of problems which would be difficult or infeasible to solve with G-Merge. For example, we enforce the rule that all prefixes stored in the People Database must be found in the Prefix Database.
Those who email to large customer/member lists are quite familiar with the flood of delivery failure notifications which come to the "From:" email address used in a message sent to the list. In this section, I explain why we don't use Gammadyne Mailer to automatically process delivery failure notifications, and I'll explain how we've automated the processing of delivery failure notifications to the maximum possible extent in the pursuit of high efficiency and total quality control.
Gammadyne Mailer can use any primary key field (a field which holds values unique to the data set) to delete records which correspond to permanent errors, as determined by the rules you specify for processing delivery failure notifications. The way this works is as follows: instead of downloading your email as usual following a large emailing — and filling your incoming mail folder with delivery failure notifications — you can have Gammadyne Mailer check your email for these notifications, process them according to your rules, and delete the messages. Do this each time before you download your email messages as usual. With this approach, your incoming mail folder does not fill with delivery failure notifications.
Unfortunately, in our experience, the variation in the format and content of delivery failure notifications often makes it difficult for a human being to determine (1) whether the problem is permanent and (2) which email address triggered the delivery failure notification! This variation was wide enough to prevent us from devising a set of rules for the automatic processing of delivery failure notifications.
Furthermore, there is a very significant benefit to human-processing of delivery failure notifications. As I discuss below, there is more than one type of problem with returned email addresses which a human can handle in a way which corrects the email address and retains a connection to a person which would otherwise be lost!
Another reason to carefully read delivery failure notifications — rather than to rely on automatic processing — is to detect misspellings in email names. For example, a recent return from "verizon.etn" looked as if it might be a misspelled variation of a valid email address. I listed all email addresses containing the well-known company name "verizon", and in all but one of the addresses the domain was "verizon.net". I then noted that "etn" is a permutation of "net", so I changed "verizon.etn" to "verizon.net" in the offending record. If I had processed this delivery failure notification automatically, the email address would have been blanked out and a contact would needlessly have been lost!
Manual inspection of delivery failure notifications brings another way to correct or replace bad email addresses. Let's say you get a delivery failure notification for the address Info@NailsByNannette.COM.
The domain is clearly a personal domain. Point your browser to this domain. If the domain — NailsByNannette.COM — is still there, look for "Contact Us" information. You may be able to replace an obsolete or mistakenly undefined email address with a current one. In this example, the website may list Nannette@NailsByNannette.COM as a contact address; if the name in your database is "Nannette Norris", you know to change Nannette's email address from Info@NailsByNannette.COM to Nannette@NailsByNannette.COM!
Once again, an email connection to a person is maintained, when automatic processing would have broken the connection by blanking out (in one or more database records) the email address reported in the delivery failure notification.
As you can see, resolving the mystery of "bad address not found" reports can involve special knowledge and ingenuity. However, the examples here demonstrate the detective techniques that anyone can use!
The content of the delivery failure notification below suggests a research project or even a career path. The message was rejected because the subject line contained three exclamation points in a row. The anti-SPAM filter of the intended recipient had a rule which defined messages as SPAM if their subject lines contain "!!!". Legitimate users of mass email have a legitimate need for expert advice on how to create email messages which will pass the filters of leading anti-SPAM programs.
Content violation found in email message.
From: [actual email deleted for privacy reasons, TDM]
To: [actual email deleted for privacy reasons, TDM]
Subject: Time & Place of Rev. Rainbow's 70th Birthday Party!!!
Matching Subject: *!!!*
This is the entire content of the delivery failure notification! The message was FROM: AntiVirus_SPAM_Filter@bhi.corp. The FROM address provides the legitimate emailer with the only clue as to how to get the legitimate message through to the intended recipient, other than using contact information such as telephone number or mailing address. In this case, a computing expert can tell that the filter condition which the current message could not pass is that its subject contains "!!!".
Here's what the expert knows. In standard notation for string patterns, the asterisk "*" represents any character string and the question mark "?" represents a single character. Thus, "?X?" would be matched by "AXA", "XXX", and "_X_" but not by "XX", "AX", or "AAXA". In contrast, "*X*" would be matched by "X", "AX", "XA", "AAX", "XAA", and so on. The subject wildcard pattern "*!!!*" matches any subject which contains "!!!" optionally preceded and/or followed by arbitrary text, or stated in simple, human-oriented terms, any subject which contains "!!!". The message is stopped by the filter rule because its subject line ends in three exclamation points!
Once again, a human examination of the delivery failure notification saves a contact. If we processed this delivery failure notification by simple-minded automatic rules, we would simply delete the email address from the database record of the intended recipient after pulling the "bad" email address from the "To:" line. Instead, we learned a rule for subject lines: use only one or two exclamation points in a row; perhaps the safest rule is "do not repeat exclamation marks in email message subject lines".
After a mass emailing, we create a plain-text file to hold the email addresses (one per line) which we manually identify as having permanent errors after a careful examination of each delivery failure notification. Many of the notifications we receive contain the phrases "permanent error" or "permanent failure" when the problem is a full mailbox. That is one example of the necessity to carefully review each notification.
If we decide the problem is permanent, we copy the email address with the permanent failure to the Windows clipboard, and then we paste the email address into a new line at the end of the bad address file, which we open and edit using Windows NotePad. We store the bad address files in a dedicated directory called BadEMail\. We name the bad address files with the date on which we create the file, as in "20120407.txt" for July 4, 2012.
- We discard any blanks, left angle brackets ("<"), or right angle brackets (">") in the data and then we check the remaining contents of each line to verify that the data forms an email address which meets the published definition of valid email addresses. (The published definition permits email formats which are far more complex than those we commonly see.)
- If the contents do not form a valid email address, we print an error message showing at what point the parse failed. For example, we may report "bob@bobcat@com" contains more than one at-sign.
- If the data is in valid email format, we match it to all of the records in our contact database which have that email address.
- If there are no matches, we report the email address and say that it cannot be found.
- We have a field in the contact database called OldEMail. When there is a match, we copy EMail to OldEMail, set EMail to blanks, and report the fact that we have blanked the email address OldEMail for <name and contact information>. The data entry screen for our contact database displays OldEMail as read-only data just below the edit box for EMail. We report the update of the EMail and OldEMail fields for each updated record.
It is a good idea to print out the bad address report so that you can perform quality control procedures with it. Examine each line in the bad address report which reports that someone's email has been blanked, and proceed as follows:
Jane@AOL.CMO should be Jane@AOL.COM (If Jane is dyslexic, she may have given her email address as "Jane@AOL.CMO", which shows how important it is to catch these problems before they are entered into the computer. People who enter hand-written source data into your People Database should understand valid and common email address formats so that these people can identify invalidly constructed email addresses and correct obvious problems. If the correction is not obvious, you can use other contact information to solicit a correct and current email address.) Jane@AOL,COM should be Jane@AOL.COM (If your database software requires email addresses to be validly constructed, the email entry "Jane@AOL,COM" would be rejected because there is a comma instead of a period between "AOL" and "COM".) Jane@OAL.COM should probably be Jane@AOL.COM. Point your browser to WWW.OAL.COM to see if the domain exists. If the address points to the home page of an ISP, the returned email address is probably not misspelled, and you can use other contact information in your People Database to reach the person and request an updated email address. If the address does not point to the home page of an ISP, suspect a misspelling and enter as the new email address the obvious correction to the returned email address.
We have found an initially baffling number of returned email addresses which the bad email report cannot find in the People Database, which contains the email addresses which to which email was sent!?! How can we get a delivery failure notification for an address which is not in the People Database? Here are some reasons that addresses in delivery failure notifications can fail to be found in your contact database:
The table below shows some real-life examples of detective work to determine aliases in delivery failure notifications. I generated the contents of the first column from the data in the second column through guesses which I verified by sending a test message to the email addresses in the second column.
Real-Life
Examples of Email Aliases
in Delivery Failure Notifications
|
SENT OUT |
GOT BACK |
For example, when I sent a test message to suijuris@retireasy.com, I got a delivery failure notification for suijuris@1stfamily.com. I was able to identify suijuris@retireasy.com as a suspect because it was the only email address in the organization's contact database that contained the string "suijuris".
I linked saintdon44@cs.com to donwelsh@compuserve.com because I knew that cs.com and compuserve.com are domain-level aliases. Among the CompuServe email addresses in the database, only two contained "don". Test messages confirmed that saintdon44@cs.com and donwelsh@compuserve.com are aliases.
I solved the third linkage by looking for email addresses containing "horseflower". In this case, there was only one match, and a test message confirmed the linkage.
In this section, we'll look at the email-merge-related fields in our People Database, the fields in our Recipient List database EMailMrg.DBF, and a number of issues which are involved in exporting data from the People Database to the Recipient List database.
Here is the subset of the structure of our People Database, showing the fields which we use in our email-merge operations:
9 Prefix C 14
10 First_name C 31
11 Surname C 25
12 Nickname C 25
14 Title C 50
15 Organizatn C 55
16 Moved_nfa L 1
17 Address1 C 40
18 Address2 C 40
19 City C 25
20 State C 2
21 Zipc C 10
22 Country C 30
27 Homeacodec C 3
28 Homephonec C 8
29 Workacodec C 3
30 Workphonec C 8
31 Extension C 5
32 Email C 50
33 Old_email C 50
The field lengths reflect many years of practical experience. If you use similar field lengths, you should be able to store provided contact information without loss or abbreviation 100% of the time or almost 100% of the time. The notable exception would be government addresses, such as this monster:
Mr. Able Baker
Data Quality Control Manager
Department of Systems Analysis and Data Quality Control
Bureau of Childcare Facility Certification and Licensing
Health and Human Services Department
Mail Stop C-5427
Edgar Burroughs Building
State Capitol Complex
2817 Government Blvd.
Sacramento, CA 96543-1557
U.S.A.
If you commonly deal with government employees, you may need to add additional address line fields Address3, Address4, and so on. However, it is virtually impossible to print such an address legibly on a standard mailing label, so you may be forced into abbreviation strategies and you may be forced to omit storing some information. In the example above, physical mail would probably reach Mr. Baker if the second and third lines were not used, but you give the maximum honor to Mr. Baker if you include all the information in the first four lines.
At data-entry time, I display the following rules for the user:
| *** PLEASE FOLLOW THESE CONVENTIONS
WHEN ENTERING OR CORRECTING DATA *** 1. Enter titles used as a form of address in the PREFIX field, such as Mr., Ms., Mrs., Dr., and Rev. Say the name is "Rev. Mary Smith": CORRECT: PREFIX: Rev. FIRST NAME: Mary LAST NAME: Smith INCORRECT: PREFIX: FIRST NAME: Rev. Mary LAST NAME: Smith 2. Enter "Rev. Mary Smith & Mr. Don Smith" in two separate records, or in one record WITHOUT THE AMPERSAND: PREFIX: Rev. FIRST NAME: Mary and Don LAST NAME: Smith 3. Enter "Rev. Mary Smith & Mr. Don Davies" in two separate records, or in one record, which means choosing which surname to use when you try to find this record. LOOKUP BY "Smith" PREFIX: Mr. FIRST NAME: Don Davies and Rev. Mary LAST NAME: Smith LOOKUP BY "Davies" PREFIX: Rev. FIRST NAME: Mary Smith and Don LAST NAME: Davies 4. Enter compound last names entirely in the LAST NAME field. CORRECT: PREFIX: Mr. FIRST NAME: Brian LAST NAME: de Palma INCORRECT: PREFIX: Mr. FIRST NAME: Brian de LAST NAME: Palma When you search for this record, give the last name as "de Palma". |
We use the second approach given under the heading Three Ways to Create Your Recipient List above, as you can see from the example at the beginning of this tutorial. We export the recipient list as EMailMrg.DBF, which has the structure shown below. The third column is the field type: N for Numeric and C for Character. The last column shows the field width.
1 Serial N 5 for quality control only
2 Dear_name C 40
3 Recipient C 134
4 Title C 50
5 Workphone C 21
6 Organizatn C 40
7 Homephone C 14
8 Address1 C 40
9 Address2 C 80
10 City C 40 for quality control only
11 State C 2 for quality control only
12 Zip3 C 3 for quality control only
13 Country C 40
Since we do not use automatic processing of delivery failure notifications, we use the Serial field for convenience only. However, should we decide to use automatic processing in the future, the Serial field can serve as the primary key which Gammadyne Mailer would require. The City, State, and ZIP3 fields assist the user in verifying that the recipient list contains the correct subset of records from the People Database. We do not insert any of these four quality control fields in our email messages, but we use all eight of the other fields in the body of the email message.
Here is the beginning of an email message which is similar to the one that we use (I discuss the -FullName- Gammadyne Mailer built-in function below):
[[-FullName-]]
[[TITLE]]
[[ORGANIZATN]] [[WORKPHONE]]
[[HOMEPHONE]]
[[ADDRESS1]]
[[ADDRESS2]]
[[COUNTRY]]Dear [[DEAR_NAME]],
By using such a format, we expose recipients to the data we have on them, with the result that many recipients will email us updates for their contact information!
Here is an overview of how I map People Database fields in Recipient List fields. For complete details, click here to see the FoxPro source code, whose English-like syntax should be for the most part obvious to any programmer.
Gammadyne Mailer can use the SERIAL field to delete records which correspond to permanent errors, as determined by the rules you specify for processing delivery failure notifications. I initially constructed the SERIAL field for this purpose, but found it impossible to specify accurate rules. At this time, the SERIAL field serves as a counter.
Some of the records in our People Database store the contact information for more than one person. For example,
| FIRST_NAME | SURNAME |
| Charles and Cathy | Colson |
| Art Arlen and Barbara | Berlin |
| Joseph, Joan, and Jan | Johnson |
Our export routine assigns "Friends" to DEARNAME if " and " or "&" appears in the FIRST_NAME field. While this is rather impersonal, we begin the message with a header which lists all the contact information we have. For this sample data, the first lines of the email messages would be:
Charles and Cathy Colson
Art Arlen and Barbara Berlin
Joseph, Joan, and Jan Johnson
If we have a nickname for a person, we use that as the DEARNAME. Otherwise we use the FIRST_NAME.
In Rev. Rainbow's transformational seminars, people often take new (if unofficial) names that describe their self-concepts, or their concepts of the selves they wish to be. Examples are Intrepid, Power Woman, and Cloud Poet. We honor their choice of transformational names by including a Nickname field on our sign-up form and in our database. When this field is not blank, we export its contents in the DEAR_NAME field; otherwise, we export FIRST_NAME to the DEAR_NAME field.
In our messages, the "dear line" is
Dear [[DEAR_NAME]],
If a woman named Sally Jones doesn't give a nickname on the signup form, the "dear line" in her email message will be "Dear Sally,". If she gives the nickname "Love Artist", her "dear line" will be "Dear Love Artist,". The concept here is to honor people in every possible way.
The format of data in the RECIPIENT field is the full name followed by the email address inside angle brackets, as in Dr. Sally Jones, Ph.D. <President@SallyForth.COM>. We specify this field as the Recipient column on the Gammadyne Mailer project's Database branch.
It is important to construct the data in your designated Recipient column in this format for this reason. If you store "President@SallyForth.COM" in the designated Recipient column, Sally will receive your email message addressed "To: President@SallyForth.COM". If you store "Dr. Sally Jones, Ph.D. <President@SallyForth.COM>" in the designated Recipient column, Sally will receive your email message addressed "To: Dr. Sally Jones, Ph.D."
You may have noticed the enormous length of the RECIPIENT field in EMailMrg.DBF. This huge length is needed to accommodate not just the email address, but also the full name of the recipient,
I build the full name from the People Database fields PREFIX, FIRST_NAME, and SURNAME. Note that the small expense of entering prefix data pays off with the honor extended. If your name is Bill Johnson, wouldn't you feel more honored to get an email addressed to Mr. Bill Johnson than to get an email addressed to Bill Johnson?
Using the power of the FoxPro programming language, I am able to construct the full name and handle missing data as a human would. If PREFIX = "Mr.", FIRST_NAME is blank, and SURNAME = "Johnson", I construct the full name as "Mr. Johnson" with only one blank between "Mr." and "Johnson" instead of the two blanks one would get with
[[PREFIX + " " + FIRST_NAME + " " + SURNAME]]
If the Recipient column data is in the format given above, you can insert [[-FullName-]] in your message where you want "Dr. Sally Jones, Ph.D." to appear. You can insert [[-Email-]] where you want "President@SallyForth.COM" to appear.
The work telephone number is in the format "(123) 123-1234 x12345" for North American telephone numbers.
\Note in the sample email message heading above that there is no line between [[ADDRESS2]] and [[COUNTRY]] of the form
[[CITY]], [[STATE]] [[ZIPC]]
as you might expect. That is because I exploited the power of the database software to save a line in the email message heading in a way that would be tedious using G-Merge. I combined the ADDRESS1 and ADDRESS2 fields from the source database into the ADDRESS1 field in the EMailMrg.DBF database that Gammadyne Mailer uses as the recipient list. For example, if the source record has ADDRESS1 = "123 Elm St.", ADDRESS2 = "Suite 4", the corresponding record in the recipient list has ADDRESS1 = "123 Elm St., Suite 4". Similarly, I have combined the source fields CITY, STATE, and ZIPC into the recipient list field ADDRESS2. For example, if the source record has CITY = "New York", STATE = "NY", and ZIPC = "01054", in the recipient list, ADDRESS2 = "New York, NY 01054".
We send two kinds of messages: general interest email messages which go to the entire database, and "we're going to be in your neighborhood" messages which are sent to a geographically-defined subset of the database. In the case of U.S. locations, the subset may consist of a list of states or a range of ZIP codes.
Note that the ZIP code does not appear as a separate field in the recipient list. Instead, a field called ZIP3 holds the first three digits of the ZIP code of the source record. For Rainbow Ministries' purposes, we only define subsets in terms of the first three ZIP code digits when we use ZIP code data in a subset definition. The CITY, STATE, and ZIP3 fields help the user verify that the the subset exported has the proper geographical distribution. In order to help the user make this verification, our recipient list is in COUNTRY order, and within countries, in ZIPC order. For example, U.S. residents are listed in ZIP code order, and Canadian residents are listed in Canadian postal code order.
These fields are never inserted in an email message. They are used to help verify the proper construction of the subset definition when that definition is made on the basis of a person's state or ZIP code.
In our custom-coded database application, we have an Export submenu. On the Export submenu, there is an option to export email-merge data. When users choose this option, they are guided by a wizard through the process of defining a subset to export or choosing to export all the email addresses.
In our activities, it is likely that we will use a particular subset over and over again. For that reason, it is useful to capture the subset definitions so that a user can choose from a list of previously-constructed definitions and only create a particular definition once. We do this with a database that captures the subset definitions. Here is the structure of the database in which we store subset definitions:
1 UPDATE Date 8
2 DESC Character 25
3 FILTER Character 150
** Total ** 184
Here is a sample of our subset definitions:
|
DATE LAST USED |
DESCRIPTION |
SUBSET DEFINITION |
|
01/08/2004 |
Test 10 records |
RECNO()<12 |
|
10/20/2003 |
Colorado |
State="CO" |
|
10/20/2003 |
Austin, Texas |
(TRIM(City) == "Austin") AND (State="TX") |
|
09/20/2003 |
S. California Reverends |
BETWEEN(LEFT(ZIPC, 3), "900", "939") AND (","+UPPER(ALLTRIM(Prefix))+","$",DR.,REV.,") |
|
09/20/2003 |
Texas |
State="TX" |
|
09/20/2003 |
Arizona |
State="AZ" |
|
08/07/2003 |
San Francisco Bay Area |
BETWEEN(LEFT(ZIPC, 3), "940", "959") |
|
05/26/2003 |
S. California |
BETWEEN(LEFT(ZIPC, 3), "900", "939") |
|
10/10/2001 |
Midland, TX area |
LEFT(ZIPC, 3) + "," $ "794,795,796,797,798,799," |
|
10/08/2001 |
Test on update |
Update = {10/3/2001} |
|
01/01/2001 |
Southeastern States |
State + "," $ "FL,GA,SC,AL,MS," |
The database software presents the subset definition list to the user in reverse chronological order, because more recently used subset definitions are more likely to be chosen. The end-user has detailed documentation on the construction of subset definitions. However, in most cases, the user needs a previously used subset definition; the plain-language description of each subset definition helps the user to rapidly choose an existing definition or to rapidly determine that a new definition must be entered!
In the early years of the Internet, one promise was that each of us could have an email address for life that never changed, no matter where we lived or traveled. With this single, permanent email address, we could always be in touch with people in our business and personal lives. The reality is that people frequently change ISPs and email addresses, as well as their other contact information. A contact database quickly becomes obsolete without vigorous efforts to keep its data fresh.
We use a convention from printed mail: we list the recipient's contact information at the very top of the email message, just as the recipient's name and address were traditionally typed at the top of business letters. This is the very first information the recipient sees in the message itself, and many recipients notice dated information and report updates to us!
However, as of version 21.1 of Gammadyne Mailer, there is no option to suppress blank data lines. For example, if you have two address fields in your database, but the second address field is often blank, you don't want a blank line in your letter when the second address field is blank. Say you export fields NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP and begin your email message with these lines:
[[NAME]]
[[ADDRESS1]]
[[ADDRESS2]]
[[CITY]], [[STATE]] [[ZIP]]
If the data is "John Jones", "123 Elm Street", "Apartment 4A", "Springfield", "MO", "54545", the recipient would see
John Jones
123 Elm Street
Apartment 4A
Springfield, MO 54545
but if the data is "John Jones", "123 Elm Street", "", "Springfield", "MO", "54545", the recipient would see
John Jones
123 Elm Street
Springfield, MO 54545
instead of
John Jones
123 Elm Street
Springfield, MO 54545
There is a labor-intensive way to simulate a "suppress blank data lines" feature using the G-Merge language recognized by Gammadyne Mailer. I will now describe this simulation.
G-Merge supports character strings with the plus sign as a concatenation operator. For example,
[[CITY]], [[STATE]] [[ZIP]]
produces the same results as
[[CITY + ", " + STATE + " " + ZIP]]
On the Database-Setup branch of each Gammadyne Mailer project there is a checkbox labeled "Trim whitespace from data". The tooltip for this checkbox states "If checked, all whitespace (blanks, tabs, carriage returns, and line feeds) will be trimmed from the beginning and end of all strings pulled from the database." In my tests with version 21.1, trailing blanks were removed even when the checkbox was unchecked.
A built-in function called str_length() permits you to determine whether a character field is empty: the field is empty if its length is 0. G-Merge's conditional logic permits you to include or exclude text based on logical tests, such as
[[if str_length(WORKPHONE) > 0]]Work Phone: [[WORKPHONE]][[else]]The Work Phone is missing.[[endif]]
which resolves to
The Work Phone is missing.
if WORKPHONE is all blanks, and to
(123) 123-1234 x12345
if WORKPHONE = "(123) 123-1234 x12345".
When I report contact information where I expect some field values to be missing (all blanks), I rely heavily on a FoxPro function I wrote called CC(FIELD1, SEPARATOR, FIELD2). When FIELD1 is all blanks, CC(FIELD1, SEPARATOR, FIELD2) returns ALLTRIM(FIELD2), where ALLTRIM() is a FoxPro function that removes all leading and trailing blanks. When FIELD2 is all blanks, CC(FIELD1, SEPARATOR, FIELD2) returns ALLTRIM(FIELD1). When FIELD1 is not blank and FIELD2 is not blank, CC(FIELD1, SEPARATOR, FIELD2) returns ALLTRIM(FIELD1) + SEPARATOR + ALLTRIM(FIELD2).
If I want to display both the work phone number and the home phone number on one line, separated by a comma if both are nonblank, I simply code CC(WORKPHONE, ", ", HOMEPHONE). The four different cases are "intelligently" handled as in this example:
| WORKPHONE | HOMEPHONE | CC(WORKPHONE, ", ", HOMEPHONE) |
| blank | blank | "" |
| "(123) 123-1234 x12345 " | blank | "(123) 123-1234 x12345" |
| blank | "(987) 765-4321" | "(987) 765-4321" |
| "(123) 123-1234 x12345 " | "(987) 765-4321" |
"(123) 123-1234 x12345, (987) 765-4321" |
As of version 21.1, Gammadyne Mailer does not support user-defined functions, so until Gammadyne Mailer does support user-defined functions, it will not be possible to write CC() in G-Merge. However, here is some G-Merge code which produces the same result as CC( WORKPHONE, ", ", HOMEPHONE) (assuming that you have checked "Trim whitespace from data" on the Database-Setup branch):
[[WORKPHONE]][[if
str_length(HOMEPHONE) > 0]][[if
str_length(WORKPHONE)>0]], [[endif]][[HOMEPHONE]][[endif]
With a slight modification, the first G-Merge code can be made smarter than CC(). Wouldn't it be nice to display "Work phone:" in front of the work phone when the work phone is nonblank, and similarly for the home phone? In the four combinations of WORKPHONE and HOMEPHONE which we have listed in the table, this G-Merge code
[[if
str_length(WORKPHONE) > 0]]Work phone: [[WORKPHONE]][[endif]][[if
str_length(HOMEPHONE) > 0]][[if
str_length(WORKPHONE)>0]], [[endif]]Home phone: [[HOMEPHONE]][[endif]
produces in these four cases a blank line and the following:
Work phone: (123) 123-1234 x12345
Home phone: (987) 765-4321
Work phone: (123) 123-1234 x12345, Home phone: (987) 765-4321
G-Merge has a built-in constant named CR which you can use to force a line break in a G-Merge expression, as shown in this example:
[["This is the first line." + CR + "This is the second line."]]
which would appear as
This is the first line.
This is the second line.
Let's make an example of suppressing blank data lines which is as clear and general as possible. You have three fields named LINE1, LINE2, and LINE3, to appear on three different lines. Data may be missing (all blanks) for any field, and you don't want any blank lines to appear when field data is all blanks. If Gammadyne Mailer suppressed blank data lines, you could get the result you want by simply coding:
[[LINE1]]
[[LINE2]]
[[LINE3]]
However, you can simulate the suppression of blank data lines by handling the line breaks yourself, as in this G-Merge code:
[[if str_length(LINE1) > 0]][[LINE1 + CR]][[endif]][[if
str_length(LINE2) > 0]][[LINE2 + CR]][[endif]][[if
str_length(LINE3) > 0]][[LINE3 + CR]][[endif]]
Note that the line breaks in the G-Merge code must occur inside G-Merge statements. The following would double-space your nonblank data lines, because the line breaks follow each [[endif]]:
[[if str_length(LINE1) > 0]][[LINE1 + CR]][[endif]]
[[if str_length(LINE2) > 0]][[LINE2 + CR]][[endif]]
[[if str_length(LINE3) > 0]][[LINE3 + CR]][[endif]]
You have exported the fields RECIPIENT, DEAR_NAME, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY. Data in the RECIPIENT field is in the format "Mary Lambda <MLambda@Fleece.COM>". If you start your email message with
[[-FullName-]]
[[ADDRESS1]]
[[ADDRESS2]]
[[ADDRESS3]]
[[COUNTRY]]Dear [[DEAR_NAME]],
you will get the results you want, except that blank data lines won't be suppressed. To suppress blank data lines, code as follows:
[[if str_length(-FullName-) > 0]][[-FullName- + CR]][[endif]][[if
str_length(ADDRESS1) > 0]][[ADDRESS1 + CR]][[endif]][[if
str_length(ADDRESS2) > 0]][[ADDRESS2 + CR]][[endif]][[if
str_length(ADDRESS3) > 0]][[ADDRESS3 + CR]][[endif]][[if
str_length(COUNTRY) > 0]][[COUNTRY+ CR]][[endif]]Dear [[DEAR_NAME]],
Let's see how your email messages would begin for the following sample data:
"Mr. Arthur Arlen <ArtArlen@A.COM>", "Art","123 Aspen Ave.", "", "Aspen, CO 85850","USA"
"Mrs. Betty Berlin <BB@B.COM>", "Betts", "234 Birch Blvd.", "Apt. 4B", "Boston, MA 01234","U.S.A."
"Prof. Charles Colton <CC@C.COM>", "Charlie", "Suite 1401", "1555 Clover Cove", "Commerce City, CA 91405",""
"Denise Dalton, D.O. <Deni@D.COM>", "Dr. Denise", "8779 Dogwood Drive", "", "Denver, CO 84223","US"
Mr. Arthur Arlen
123 Aspen Ave.
Aspen, CO 85850
USADear Art,
Mrs. Betty Berlin
234 Birch Blvd.
Apt. 4B
Boston, MA 01234
U.S.A.Dear Betts,
Prof. Charles Colton
Suite 1401
1555 Clover Cove
Commerce City, CA 91405Dear Charlie,
Denise Dalton, D.O.
8779 Dogwood Drive
Denver, CO 84223
USDear Dr. Denise,
Gammadyne Mailer offers an include-file function called file_fetch("<file name>") which inserts the contents of the specified file to replace the file_fetch() call. At first glance, you might think that you could use file_fetch() to build a library of no-argument G-Merge functions which reference your field names. For example, think how convenient it would be to store your message header
[[if str_length(-FullName-) > 0]][[-FullName- + CR]][[endif]][[if
str_length(ADDRESS1) > 0]][[ADDRESS1 + CR]][[endif]][[if
str_length(ADDRESS2) > 0]][[ADDRESS2 + CR]][[endif]][[if
str_length(ADDRESS3) > 0]][[ADDRESS3 + CR]][[endif]][[if
str_length(COUNTRY) > 0]][[COUNTRY+ CR]][[endif]]
in a file, say Message Header.TXT, and include it at the beginning of each message by putting a file_fetch() call on the first line of each message, as in
file_fetch("Message Header.TXT")
Unfortunately, as of version 21.1, Gammadyne Mailer does not process G-Merge statements in files inserted by file_fetch(). The actual G-Merge statements appear in your email message, which is definitely not what you want!
As I wrote above, contact databases become obsolete quite quickly unless vigorous efforts are made to keep their data fresh. We start our email messages with a standard header that lists the contact information we have for the recipient. Then at the end of the message, we list this information again with an appeal to send us updates.
In fact, we end each message with a P.S. designed to drive traffic to the Rainbow Ministries website, and the solicitation for data updates appears in a P.P.S. The format is then
<contact information header>
<project-specific text>
<the P.S.>
<the P.P.S.>
Remember that you'll prepare your email messages in two formats: plain text and HTML. In our case, each of these four parts appear in both formats.
We begin our plain-text messages and our HTML messages with the same contact information header, which contains this G-Merge code:
[[if str_length(-FullName-) > 0]][[-FullName- + CR]][[endif]][[if
str_length(TITLE) > 0]][[TITLE + CR]][[endif]][[if
str_length(ORGANIZATN + WORKPHONE) > 0]][[str_trim(ORGANIZATN + " " + WORKPHONE) + CR]][[endif]][[if
str_length( HOMEPHONE) > 0]][[HOMEPHONE + CR]][[endif]][[if
str_length(ADDRESS1) > 0]][[ADDRESS1 + CR]][[endif]][[if
str_length(ADDRESS2) > 0]][[ADDRESS2 + CR]][[endif]][[if
str_length(COUNTRY) > 0]][[COUNTRY + CR]][[endif]]
In the plain-text version, the header is followed by a blank line and the "dear" line:
Dear [[DEAR_NAME]],
In the HTML version, the contact information header appears in the default font. However, we apply red color, size 6, and boldface italics to the "dear" line in the HTML version:
Dear Mr. [[DEAR_NAME]],
Our standard postscript drives traffic to the Rainbow Ministries website. Note the importance of tailoring this postscript to the audience. In this case, the postscript expresses the exuberant personality of Rev. Rainbow and reminds recipients of their experiences with her:
|
P.S. If you can't join us, you can still have Rainbow Experiences -- on demand, no less! -- by ordering an audiotape or CD of me guiding meditations and reading my Daily Guides. Click here to go to the Products page of the Rainbow Ministries website. Visit the Rainbow Ministries website often to read about trips I have taken and tours I have led to destinations around the world. Many of the trip reports have photos, both serious and comic. Click here to visit my website, which has full color, a Rainbow-angel mascot, sound, and motion! WOW!!! IT'S LOVE, LOVE, LOVERLY!!! |
Contact information appears at the beginning of each email message, but recipients may ignore it or not think to send us updates if the contact information is not current. Therefore, at the end of the message, we explicitly ask recipients to supply us with updates:
| P.P.S. Could you verify the following
information and send any corrections to [Rainbow's email address
appears here in actual messages]? You are
entitled to One Mega-Brownie Point for each correction!!! No
waiting until Christmas, Hanukah, or your birthday!!!
Name: [[-FullName-]] |
Note the explicit language about providing a purely personal email or a work email address which is OK for personal use. This is to avoid two situations which we have encountered:
Click on these links to examine a sampling of our template files:
Content questions:
Info@RevRainbow.ORG Website problems:
Webmaster@RevRainbow.ORG
|