Efficient Email Bounce AutomationEfficient Email Bounce Automation

   Home > Resources > How-To Articles > Article 1 - Simple Email Bounce Automation - Part 1

Simple Email Bounce Automation Using BounceStudio Enterprise
Part 1 of 2

Configure BounceStudio Enterprise To Connect To Your MS Access Database

Before starting this step, you'll need to know the name of your MS Access database file, and the folder you saved it too. The name of my Access database is email.mdb, and I saved it in my c:\temp folder. I'll be creating a "System DSN" that points to it. Your Access database is probably in a different location.

In order for BounceStudio Enterprise to connect and log to your database, you must first create a "System" Data Source Name (DSN), on the computer running BounceStudio Enterprise, that points to your database. To create a new "System" DSN, click the Database TreeView item within the BounceStudio Enterprise TreeView Window (see image below) for the BounceStudio POP account you created in the previous step. Next, click the "..." button next to the DSN selection box. This will launch the ODBC Data Source Administrator Applet.

Sample BounceStudio Database Connection Window

Click on the "System DSN" tab, and then click the "ADD" button to launch the "Create New Datasource" dialog.

ODBC Data Source Administrator Applet

Select the "Microsoft Access Driver (*.mdb)" driver and click "FINISH" to display the ODBC Microsoft Access Setup dialog.

Create New Data Source Dialog

Enter a data source name and description for your database. For this article, my data source name (DSN) will be "Email Bounce Log Database" and the description will be "BounceStudio Enterprise Bounce Logging". You can name your DSN whatever you like. Next, click on the SELECT button to launch the "Select Database" dialog.

ODBC Microsoft Access Setup Dialog

Browse to your Access Database (Our example database name is called "email.mdb". Your database will probably be named something different), highlight it, and click OK. At this point, you'll be taken back to the "ODBC Data Source Administrator Applet".

Select Database Dialog

You will see a newly created System DSN called "Email Bounce Log Database". Click OK to return to BounceStudio Admin.

ODBC Data Source Administrator Applet

Select the "Choose DSN" dropdown box to select your newly created DSN. If your database has a password protection system then type in your login and password for the database.

Database Screen after selecting our newly created DSN.

Entering your SQL Statement

Now that you have configured the DSN, it's time to enter the SQL statement. In order for BounceStudio Enterprise to log bounce data to your database, you must enter the SQL statement that BounceStudio Enterprise will execute each time it retrieves a bounced message from your POP account. To acheive this goal, we'll be using a simple SQL INSERT statement (see below). You can copy/paste the following SQL into the "SQL Statement" text box in BounceStudio Admin.

SQL Statement
INSERT INTO btBounceLog (BounceType, PopAccountName, EmailAddress, RawMessageText) VALUES ('#BOUNCETYPE#', '#POPACCOUNT#', '#EMAIL#', '#RAWMESSAGE#')

Once you've entered the above SQL statement, your database settings window will look like the following for your BounceStudio POP account:

Using BounceStudio Variables

One important concept to remember when creating your SQL statement is the idea of BounceStudio variables. A BounceStudio variable is a data value that can be used anywhere in your SQL statement. They are most commonly used in the VALUES clause of an INSERT statement, the SET clause of an UPDATE statement, or as parameter values for a SQL stored procedure.

You can use a variable by typing it into the SQL Statement text area using surrounding pound symbols "##". For example, to refer to the variable "EMAIL", you would type "#EMAIL#". Variables are case-sensitive.

The table below lists the standard BounceStudio variables that BounceStudio can use within your SQL statements.

BOUNCETYPEThe bounce 'type' of the bounced message. (ie. SB, HB, GB, MB, MBKS, etc.)
EMAILThe recipient email address of the bounced message.
POPACCOUNTThe POP account that BounceStudio retrieved the bounced message from.
RAWMESSAGEThe raw text of the email message BounceStudio retrieved.

NOTE: There are several more BounceStudio variables that you can use, but they are not covered in this article.

Testing Your Database Connection & SQL Statement

You'll need to test your BounceStudio POP account database settings after you have selected your DSN and entered your SQL statement. To do this, click the "Test" button at the bottom of the POP account database settings window. If everything works correctly, you'll see a message like the one in the following image.

BounceStudio Successful Test Dialog

To confirm that the "Test" button worked properly, you can also open your Access database and check to see if the "test" record was inserted into the btBounceLog table. Each time you click the "Test" button at the bottom of the POP account database setttings window, a record will be created in your btBounceLog table. If everything worked correctly, you'll see a test record in the btBounceLog table. See image below.

Successfully inserted test record

Enable This Database Connection and Sql Statement

Once you're done testing your BounceStudio POP account database settings, you'll want to enable it. You do this by making sure the "Enable This Database Connection and Sql Statement" checkbox has a check mark in it. Selecting this checkbox will tell the BounceStudio service to connect to the DSN, and execute the SQL statement for every email bounce message it retrieves. If, at anytime, you'd like to stop logging to your database then un-check this same checkbox.

Save Your Settings

In order for your new database settings to take affect, you must click the "Save" button at the bottom of the POP account database settings window.

Page: 1 | 2 | 3 | 4 | 5 | Continue to Page 5 >>

"There are hundreds of bounce formats you can encounter on the Net, and the fact that new or obscure bounces can be caught simply and painlessly with BounceStudio saves us valuable development time."

John Gowland, CEO
©1999-2024 BoogieTools, Inc. All rights reserved.