Mail Merge with an Access Database - Using Two database tables

This example demonstrates how to send an email using the mail merge features in NetMailBot, while using two database tables. The notable aspect of this example is that -dbquery uses a SQL Join to query data from two tables to be used in the mail merge. The database used is an MS Access database (Access 2000 version) which contains some sample data. NOTE: You should change the email addresses in the Access database to your own email address(es) before running a test.
  • Requirements
    • Working knowledge of creating and editing an MS Access database.
    • Working knowledge of creating and editing a batch file.
    • Understanding of basic SQL concepts including multi-table joins.

  • Example Download
    A working example can be downloaded, so that you can follow along with the example:
     Click Here ( 10KB)
    The downloadable example is a zip file that contains two files:
    • test.mdb - The HTML page that will be the email message body
    • mail_merge.bat - The batch file that will instruct NetMailBot to send the email message.

    Instructions for Download and Installation

    • Download the example zip file and save it to your hard drive
    • Create a directory on your C drive named "tmp"
    • Unzip all of the files in the example zip file into the newly created directory c:\tmp.

  • Steps
    1. Open the Access Database.
    2. Open the table Email for editing.
    3. Change all of the email addresses to your email address. The email addresses can all be the same.
    4. Close the Access Database.
    5. Open Notepad and edit the batch file mail_merge.bat.

      Some important parameters used in the batch file

      • Change the parameters: -to -from to your own email address.
      • Change the parameter: -server localhost to your own mail server
      • Note the use of -dsn "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\tmp\test.mdb". This is a normal connection string that instructs NetMailBot on how to connect to the Access database.
      • -dbquery "SELECT Customers.first_name, Customers.last_name, FROM Customers, Email WHERE Customers.ID = Email.customer_id" instructs NetMailBot to select all of the columns of data from the Customers and Email tables in the Access database. The columns first_name and last_name are from the Customers table, and the column email is from the Email table.
      • -dbemailcolumn "email" instructs NetMailBot to use the data in the column named "email" to address the message to the data contained in that column for the current row.
      • The parameter -body "<<first_name>>=first_name,<<last_name>>=last_name" contains replacementids which are explained next.
      • -dbreplacementids "<<first_name>>=first_name,<<last_name>>=last_name" instructs NetMailBot to replace the strings with double-angle brackets with the data contained in the mapped column for the current row. For example, in the message body, NetMailBot will replace the string <<first_name>> with the data contained in the column named "first_name".
      • -personalize instructs NetMailBot to do make the replacements. Without this parameter specified, NetMailBot will not perform any replacements.
    6. Execute the batch file by double-clicking it in "My Computer".