Logging email to a Domino / Notes database (for fun and profit)

In a previous article I mentioned the use of Email2DB  a package to facilitate the logging of mail from a POP account into a database. Out of the box the package is easy to configure and have mail stored into a Mysql / MS SQL database. However my requirement called for mail to be collected from a POP account and inserted into a Lotus Notes Domino server database.

What makes this problem interesting are the components you have to configure and the gotchas that present themselves along the way. So let me explain a few of the issues and then take you through the steps to get this working. Grab a coffee – this takes a little while to explain in detail !

I’m assuming a certain level of familiarity with each of the components listed below. The email2db elements are quite straightforward and any Notes administrator could help with the Notes side of things. 

1) a lotus notes domino server (on which you must be able to create new users) – in this case Notes 6.5 on Windows 2003 server

2) a server running email2db – can be the same server – in this case a windows 2003 standard server

3) a lotus notes id file which has no password and has rights to create documents to the database you are going to store the emails in.

4) Notessql – a free (but needs registration to get it) utility from IBM version 3.02J is the one used here. It acts as the glue to allow the email2db program to insert the email information into Notes. Can be used by any application which can access / update an ODBC data source.

So what are the issues ? Well the first is that its not made clear in the Notessql documentation that you should use a Notes ID with no password. I can hear the Notes admins among you thinking – what about the security implications but properly secured and giving only the appropriate rights – its no less secure than the usual password protected ID that a user has. Once you have an ID with no password (I called mine Data Access) created on the Notes server then copy the file to the location where you are going to access the Notes database from.

Based on that Notes ID – give write access (i.e allow to create documents) to a database on your server. I created a blank database for the purposes of the testing. More about that in a moment.

Install the email2db software on a machine.

Next install the Notessql utility on the machine with the email2db on it.

Create an Lotus Notes odbc datasource (a system dsn) that connects to the Notes database you selected or created above. Important – select the options button within the ODBC lotus notes setup dialog and press Add user. This allows you to select the notes ID with no password that you are going to use. – leave the password field empty. Then this should allow you to select the domino server which holds the database you want to store data in and also the database itself. If you were only interested in storing the data in a notes database on the email2db server – you would simply select local. Save the DSN. I named mine INM

Now in order to store the email in the notes database there are two further tasks. Using the Notes Designer create a form that will be used to load the emails – I called mine INMFORM. Create the following text fields on it – you can change their datatypes later as required.

Next create a view that displays the “records” in Notes that use that form.

Finally – create an account in Email2db to get the emails from the pop account. I named mine INM. Under the account properties Read Messages Tab – select Read from POP3 server and put a tick beside Read Messages from a Pop3 server – enter the address , pop port if not 110 and the username and password on the pop account – you can use the Test Account Settings button just to check all is well.

 Lastly you need to add a trigger – this is actually the important bit since it takes the messages, processes them and if required deletes them from the pop server. When adding the trigger – name it and select the Other actions tab. Select the run script tab – put a tick beside the run script and paste the following script in.

Sub Main()‘ commands start hereOn Error GoTo Err_handlerDim conn As New ADODB.Connection

WordPress – its blogtastic ! »« Logging mail to a database

Logging mail to a database

As a means of alerting you to issues with servers and other monitored devices – email works fine if you are interested in responding to the item and then not needing to record the message for future reference. However if you want to retain a record of emails received to a pop mailbox then read on.

Initially I wanted something that would integrate with a Helpdesk System written in Lotus Notes / Domino but I decided to get something that would log to another database MS SQL or Mysql with the capacity to extend to other databases.

Enter Email2DB from Parker Software – which you can get at www.email2db.com. Its not free but it is worth every penny if you need this functionality quickly and the ability to do an awful lot more.

In operation you need to add the details of the pop mail account you want to process mail from and the actions that you want to act on the emails. Finally you decide if you want to remove the email from the remote pop account once you have processed it. The application can store the emails you process in either Mysql or MS Sql databases and the actions that you can do (dependant on the version you buy) such as forwarding the original mail, running scripts to further manipulate the message contents or simply having a pop message appear.

In a future article I’ll cover using Email2DB to link with a Lotus Notes Database using NOTESSQL and a script.

Many thanks to Dan at Parker Software for his persistence with odbc issues during the Lotus integration work.

Logging email to a Domino / Notes database (for fun and profit) »« Adventures In X10 (A home automation project)

Adventures In X10 (A home automation project)

X10 is a system which uses electrical wiring to transmit signals from a controller – In my case a PC to remote devices such as lamps or other electrical appliances. Its quite possible to build elaborate monitoring and control systems for your home using items linked through the mains distribution cable in your home and various controllers.

 In my case I was initially looking for a system to manage switching lights on and off and being able to monitor and manage a house heating system. So some background might be useful. X10 really came about in the early 1970′s – and has persisted and been refined since then. The hardware I’m describing here is that based on the Marmitek brand of X10 equipment.

 As an introduction – you could start using X10 with 1 controller – 1 lamp module that plugs in between the socket and the lamp and whats called a whole house transceiver. In order to send instructions across the electricity mains – each device in an X10 network tends to have a unique unit number (for 1 to 16) and a house code (from A to P although P has a special meaning).

In this example – we set a lamp module to A1 using the two rotary switches on the front of the module. The transceiver is set to P (meaning accept signals for all house codes up to P) and plugged into the mains and finally the remote controller is set to house code A.

Now we are ready to switch on the light using the on off functionality of the remote control. Although this is a simplistic example – it shows how simply the X10 system can be implemented and used.

In the next article – we’ll look at expanding the number and variety of devices controller from X10, adding in a PC with software to allow us to control timing and sequences of events. In future articles we will dig deeper in integration with other systems and devices such as heating and alarm systems based on X10.

Logging mail to a database »« Return of the Lotus Notes Magic ?