How to Import Existing Mailing Lists in Caliban Mailer 1.20
by Raza Jaffri raza@aarknet.com
March 2001


Note: This technique may not work if the new mailing list to be imported includes more than 65000 addresses. Also, if you are not using Access 2000, then you don't need to follow the steps for conversion of the database. 


1. Start Caliban Mailer (CM), click on Mailing Lists tab, click on New button and then enter name for the new list and click the OK button. Close CM. Make sure that while you are following the subsequent steps, you should not open CM, and as a safety measure, create a copy of CalibanMailer.mdb in a different location and rename it as CalibanMailer_standby.mdb so that you have the original file to go back to if something goes wrong.

2. If you are using MS-Access 97 then skip this step. Rename CalibanMailer.mdb as CalibanMailer_original.mdb and then open it as a converted file for Access 2000. Give it any temporary file name.

3. Open the Addresses table and note the total number of records as appearing in the status bar below on a piece of paper and lets call it Item CM1. 
Now open the table MailingLists and note the Mailing List ID of the Mailing List you created in step 1 above. Lets note this on the same piece of paper and then minimize MS-Access.

4. Extract the mailing list from its source through any means into a single column list or at maximum a two column list as Name and Email Address.

5. Start MS-Excel, and import the list. Then Insert a New Row at the top of the list and label columns a, b,c and d as follows: 
AddressID
EmailAddress
Name
Notes
Active
If the need be, move the email addresses and their respective name, if available in columns b and c so that they are under the correct headings. The sequence of the above headings must NOT change.
Now go to cell A2, look at the total number of records noted in step 3 above (CM1's value) and enter the next consecutive number in this cell. After that, fill in all the relevant rows in column A with subsequent numbers. (These are unqiue AddressIDs that CM will use and therefore there should be no duplications, missing values or errors in this column. It only accepts numeric values.)
Then go to cell D2 and fill-in all the relevant rows with the digit "1" in this column.

6. Next, create a copy of this spreadsheet by clicking on Edit > Move or Copy Sheet and then click the "Create a copy" checkbox and press OK. Now double-click the sheet name near the bottom of the screen and rename the active sheet as MyList. 
Then, delete columns b,c , d and e. 
Click on cell A1 and insert a new column through Insert > Column.
In cell A1 enter MailingListID and then fill all relevant rows in this column with Mailing List ID noted in step 3 above.

Use commonsense to ensure that there are no mistakes anywhere in the two spreadsheets and then save it as a MS-Excel file and close Excel.

7. Now go back to Access and click on File > Get External Data > Import. Then navigate your way to the file saved in step 6 and open the file. Here, remember to change the File type in the File Open dialogbox to MS-Excel, otherwise it will not show the file. 
You will notice that it will show two worksheets to choose from from. First select the worksheet which contains the addresses and then click Next. On the next screen it will ask your confirmation that the first row contains column headings. Fill the checkbox and click Next. On the next screen it will ask you where would you like to store your data? from the drop-down list choose the table "Addresses" and click next. If there were no errors in the worksheet, it will give you the final screen where you click the Finish button and it will append all the data to the "Addresses" table. If it gives you an error message, then read it carefully and find fix the error accordingly.

8. Now the repeat the same procedure and import the worksheet "MyList" in to the table "ListDetails". 

9. Now click on Tools > Database Utilities > Compact and Repair Database. If you are using MS-Acess 97, then your database is ready. Close MS-Access. 

10. If you are using Access 2000 then you need to do the next step before closing Access. Click on Tools > Database Utilities > Concert Database > To Prior Access Database Version. It will ask you for a file name and location. Save it in the same folder where the original database is saved and name it CalibanMailer.mdb

11. Start CM. You will note that the new addresses have been added in the Addresses database and included in the new mailing list.

Enjoy!

If you have any comments and corrections, please write to me at raza@aarknet.com. I am not associated with Caliban Computing in anyway. I am an independent user but I will be glad to help other users.

Close Window