| |
Mailing List Files and Databases |
|
You can create your mailing list within CustomMailer itself, read your mailing list from a plain text file, or read your mailing list from an SQL database such as Access, SQL Server, Oracle, or MySQL (with the CustomMailer Enterprise Edition).
Create Mailing List inside CustomMailer
The easiest way to create your mailing list is to do so inside
CustomMailer. Here's how:
1) Under the File menu, select the item "New Mailing
List...".
2) Use the dialog that appears to define a set
of mailing list columns such as: Email, FirstName, LastName, etc. In
this dialog you can also delete any unwanted column names and reorder
the column names by dragging them in the list. When you say OK,
you will go back
to the main window with your columns shown in the mailing list but with
no rows of data. CustomMailer will automatically add the column
SendStatus
and also the columns LastSent and/or NumberSent if these options are
checked
in Preferences.
3) Now under the Edit menu, select the item "Add
Mailing List Row..." to add rows to your mailing list.
4) Use the dialog that appears to enter values
in each column for one row of your mailing list, then say OK.
5) Repeat steps 3 and 4 for each new row to add to
your mailing list.
6) When you are done, select "Save Mailing List
As..." under the File menu. Thereafter CustomMailer will remember
this file name and open it each time you start up CustomMailer.
Under the Edit menu, there are
other useful commands for editing your mailing list, including:
1) "Edit Mailing List Row..." to change any of
the values in a selected row
2) "Delete Mailing List Row(s)" to delete any number
of selected rows in your mailing list
3) "Restore Last Deleted Row(s)" to restore the most
recently deleted set of rows back to your mailing list. NOTE: restored rows will be added to the end of the
list, not in their original positions.
4) "Add Mailing List Column..." to add a new column
to your mailing list. This command allows you to set the values
for each row of the new column which can be either:
a) a
string entered by you (which may be left blank)
b)
the value of a |tag|, which will copy the values from another mailing
list column,
c)
the value of a |macro|, which will be evaluated based on the values
from other mailing list columns for each row
d)
the values SELECTED or NOT SELECTED based on whether that row was
selected.
5) "Edit Mailing List Column..." to edit the values
in an existing column. You can either rename the column, perform
1st letter capitalization of the values in the column, or set row
values using the methods 4a), 4b), 4c), and 4d) above. "Edit
Mailing List Column..." also gives you the option to change values in
all rows or only in a subset of rows selected by you.
6) "Delete Mailing List Column..." to delete
unwanted columns.
Mailing List in a File
You can also create your mailing list file outside of CustomMailer
using a spreadsheet, word processor, or text editor, then save to a
text file, and read this file into CustomMailer. Or, if you have
your mailing list in a personal database, email program, or personal
information organizer, these programs usually let you export this data
to a text file (such as comma-separated values or tab-delimited text)
suitable for reading into
CustomMailer.
In your mailing list file, each recipient should be on a single line. Each line should contain one or more fields separated by delimiters (use tab, comma, or space). By default, the first line of your file is assumed to be the column headings specifying the names of the fields (you can turn this feature off in Preferences). For example, your mailing list might look like:
Date Company Product OS
Title
First Last Email
5/5/03 IBM display WinNT
Mr. James Jones test1@wildcrest.com
6/8/01 Apple CD ROM MacOS Ms.
Linda Blake test2@wildcrest.com
7/17/02 HP modem
Win95 Dr. Henry Flynn test3@wildcrest.com
NOTE: In general, we recommend using tabs for delimiters so that spaces in phrases like "CD ROM" are preserved in your mailing list or in case you want spaces in your column heading names.
If you use a spreadsheet, save your mailing list
as tab-delimited text or comma-separated values. For example, in
Microsoft Excel, use "Save As" and in the "Save as type" field select
"Text (Tab delimited) (*.txt)" or "CSV (Comma-delimited) (*.csv)".
If you use a *.csv file, CustomMailer will automatically
change your field delimiter character to comma in Preferences. If
you use a *.txt file, CustomMailer will attempt to guess the
field delimiter character you are using and offer to change your
Preferences setting as needed.
NOTE: You can also read in your mailing list directly from a Microsoft Excel spreadsheet using JDBC (this requires the CustomMailer Enterprise Edition), see Example #3 in the "Mailing List in Database" section below.
If you use a word processor or text editor, it is easiest to save your mailing list in a .txt file.
If you use a personal database such as Microsoft Works, Access, etc. you can generally "Export" your database as tab-delimited text or comma-separated values. Generally it is easiest to save your text in a .txt file. Regular databases such as Oracle, SQL Server, or MySQL also usually provide a way to save selected rows as tab-delimited text or comma-separated values.
If your want to use your Address Book from your regular email program, generally these programs let you "Export" your Address Book to a plain text tab-delimited file. For specific instructions on using Address Books from Microsoft Outlook or Netscape Messenger, see "Mailing Lists" under Frequently Asked Questions.
By default, multiple adjacent delimiters do not count as a single delimiter. This is how spreadsheets such as Excel export their tab-delimited records, and this allows you to have "empty" fields (denoted by back-to-back delimiters). You can change this in Preferences to allow the use of multiple spaces, tabs, etc. between individual fields (but then you will not be able to have "empty" fields). The most common need for this is if you have multiple spaces between fields, for example, if what used to be tabs in your mailing list got converted to multiple spaces.
The number of fields in the first line will determine the number of fields per line for the rest of the file. Empty fields will be added and extra fields ignored as needed. This makes the reading of mailing lists very robust. You can easily read in mailing lists which contain blank lines, comments, tabulation lines, extra fields, etc. and just send mail to those lines that make sense (in fact, CustomMailer will not send email if the TO: field does not contain a standard Internet address like user@domain.com, so CustomMailer will skip any such non-recipient lines even if you just "Select All" and send). If CustomMailer detects that your mailing list file has records with extra or missing fields, it will put up a dialog telling you how many of these records were encountered and will note which records they were in the SendLog.txt file (viewable from the View menu) in case they are actually errors you wish to correct.
In addition to guessing the number of fields per record and the field delimiter character in your mailing list, CustomMailer attempts to guess whether you are using the first line of the mailing list as column headings and, if different than the current setting, will offer to change your Preferences accordingly. The guess is based on whether there is a valid email address anywhere in the first line of the mailing list file.
NOTE: CustomMailer trims leading and trailing spaces from all
fields in the mailing list. These extra spaces often occur in
data entered by users. They are hard to spot in your mailing list
and would
otherwise frequently result in message expansions like:
Dear Susan
,
<-- note the extra space after the name
However, because of this feature, mailing list fields containing
only spaces or intentional leading or trailing spaces are not supported
by CustomMailer.
The CustomMailer Personal Edition supports mailing lists of up to 300 names. The CustomMailer Business and Enterprise Editions remove this limit (for example, we routinely use lists of over 10,000 names).
CustomMailer will remember the most recently read mailing list and what folder it is in when you run CustomMailer next time. CustomMailer will also remember a set of the several most recent mailing list files you have opened (by default, 5, which you can change in Preferences). The Open Mailing List menu will display and let you quickly select any of these previous mailing lists in addition to letting you open any mailing list using a standard Windows file dialog.
Since the current mailing list appears first in the Open Mailing List "history" menu, you can invoke this menu item to quickly "reload" a mailing list that you may have changed inside CustomMailer in order to restore it to its original state or to pick up any changes you may have made to the mailing list from a different program while CustomMailer is running. Equivalently, the "Reload Mailing List" command will perform the same operation, that is, re-reading the file from which your current mailing list was originally read.
CustomMailer also has an "Append Mailing List from File..." command
under the File menu which lets you read another mailing list file and
add it to the end of your existing mailing list. It is not
necessary that the appended file match your current mailing list in
either number or order of columns. CustomMailer will attempt to
match (case-insensitive) the column headings in your mailing list to
column headings given in the first
line of your appended file. A dialog will display each mailing
list
column with a popup menu showing the appended file column to be used
(or
"leave blank" when no matching name is found). You can use the
popup
menus to change the selection for any mailing list column to any column
in
the file (or "leave blank"). For further details, see "Append
Mailing List from File" command under the "File"
menu in the chapter "Menus".
Mailing List in a Database (requires
CustomMailer Enterprise Edition)
To use a mailing list from a database, you make a standard SQL-style
query against any local or remote database (or Microsoft Excel
spreadsheet) for which you have a JDBC or ODBC driver. In
Preferences, under the Mailing list tab, you indicate that your mailing
list is stored in a "Database" instead of a "File". This will
enable the database control fields
of the Preferences dialog, into which you specify the access parameters
and query (select) command for your database.
To read your mailing list from a database, you will need the
following five pieces of information:
1) your database driver name (or use
sun.jdbc.odbc.JdbcOdbcDriver which should almost always work)
2) your data source name ("DSN") or URL,
3) your database login name,
4) your corresponding password, and
5) your SQL query ("SELECT") command.
These values are entered in accordance with values you set up in the
Windows "ODBC" Control Panel (variously called, on Win 95: "32bit
ODBC", on Win98: "ODBC Data Sources (32bit)", on Win NT: "ODBC Data
Sources", on Windows 2000 and XP: "Data Sources (ODBC)" under
"Administrative Tools") to tell Windows about your database.
When you hit "OK" in the Preferences dialog, CustomMailer will read your database using these values. By the nature of the underlying system software, CustomMailer cannot do much more than report whether this transaction was or was not successful. You might have the DSN wrong, the password wrong, the select command wrong, or your network connection may be down, and in all cases you'll get the same "Not able to connect to database" error message inside CustomMailer. So, if you aren't sure of your correct values, it is a better idea to verify them using the database reader provided by your database vendor rather than do a lot of trial-and-error inside CustomMailer. Once they are correct, the same values should work inside CustomMailer.
The CustomMailer Enterprise Edition will show the most recent database file you have read as your mailing list in the "history" list under the "Open Mailing List" menu command. This allows you a way to quickly re-execute your database query using the current Preferences settings without going back to Preferences. This "reload" operation is useful as a way to pick up any changes made to the database by another program (or user) while CustomMailer is running. Equivalently, the "Reload Mailing List" command will perform the same operation, that is, re-executing the database query with which your current mailing list was originally read.
Example 1: Microsoft Access database. Here's how to use the sample database ("Northwind") supplied with Microsoft Access 97. These instructions assume you have already installed the Northwind sample database and your Microsoft Access Driver on your system using your Microsoft Access (or Microsoft Office) installation disk. To verify this, be sure you can open Northwind inside Microsoft Access. If you didn't install Northwind when you first installed Access or Office, you can use your Microsoft Access or Office installation disk to add it now.
Next, you need to make a "User DSN" for Northwind. To do this, go into your Windows Control Panels and open your system's ODBC control panel. Look at the "User DSN" (or "Data Sources") list. Depending on your release of Microsoft Windows and Microsoft Office, the Northwind database (along with other Access databases) may already appear in the list and be ready for use. If you don't see "Northwind" in the list, click on "Add", then Microsoft Access Driver, then Finish, then enter the DSN name "Northwind", then hit Select and locate Northwind.mdb on your system, then say OK. Now Northwind should appear in your User DSN list.
Now run CustomMailer and use the following settings for the Database driver, Data Source Name/URL, Username, Password, and SQL query in the Preferences dialog under the Mailing list tab:
sun.jdbc.odbc.JdbcOdbcDriver
jdbc:odbc:Northwind
(user name not needed)
(password not needed)
select * from customers
Ordinary SQL syntax applies in the SQL query field, which is passed
directly to the database. Other typical queries might be:
select * from customers
where Country="USA"
select * from customers
where Country="USA" order by ContactName
You do not need to be running Access when you run CustomMailer, since CustomMailer is able to read your database directly without needing to run Access. Once you get Northwind to work, other Access databases should work in the similar manner. Of course, if all else fails, you can always do an "Export" under Access (and most other databases) to produce a tab-delimited text file and read that into CustomMailer.
Example 2: Sybase database. To use the sample database supplied with Sybase Adaptive Server Anywhere 6.0 database, use the following settings in CustomMailer in the Preferences dialog under the Mailing list tab:
sun.jdbc.odbc.JdbcOdbcDriver
jdbc:odbc:ADA 6.0 Sample
dba
sql
select * from customer
There are a large number of database vendors and third-party "middleware" vendors who offer JDBC and/or ODBC drivers for use with various local and remote databases. A list of these is maintained by Sun at http://industry.java.sun.com/products/jdbc/drivers. Each solution is different in the syntax and setup they require so you need to consult with your particular vendor and/or network administrator to determine the correct driver, URL/DSN, name, and password to use in CustomMailer. Note, however, almost all databases accessible from Windows come with an ODBC driver, in which case you can use the generic driver sun.jdbc.odbc.JdbcOdbcDriver which we have included with CustomMailer.
Example 3: Microsoft Excel spreadsheet. If you have the CustomMailer Enterprise Edition, you can read in a Microsoft Excel spreadsheet directly using JDBC without having to save it as tab-delimited text. You must have the first row of your spreadsheet provide the names you wish to have for your spreadsheet column headings. First, go to Windows "Control Panel". Double-click your Windows "ODBC" control panel (see above). Select the "User DSN" (or "Data Sources") tab. Select "Add". Choose "Microsoft Excel Driver" and "Finish" (if you do not see a Microsoft Excel Driver option, reinstall Microsoft Excel and be sure to include its "ODBC" drivers). Enter a "Data Source Name", which might be a name like: myMailingList. Click on "Select Workbook", then navigate to and select your Excel spreadsheet, e.g. mySpreadSheet.xls. Say "OK" the rest of the way to complete this operation.
Now run CustomMailer and use the following settings for the Database driver, Data Source Name/URL, Username, Password, and SQL query in the Preferences dialog under the Mailing list tab:
sun.jdbc.odbc.JdbcOdbcDriver
jdbc:odbc:myMailingList
(user name not needed)
(password not needed)
select * from [mySpreadSheet$]
Notice that the DSN goes in the second (jdbc:odbc:) line,
and the Excel workbook name goes in the last (select * from)
line. The latter must be followed by a dollar sign and surrounded
by square brackets. The select command can reference column
headings in your Microsoft Excel column, e.g.
select * from
[mySpreadSheet$] where Month='July' and Year=2001
where Month and Year are column headings (= names
in the first row) of your Excel spreadsheet.