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.
| Who From | |
| Received | |
| Processed | |
| Match | |
| Success | |
| Last Error | |
| AccountID | |
| Source | |
| UID | |
| Subject | |
| FromAddress | |
| ToAddress | |
| Importance | |
| Sensitivity | |
| MessageDate | |
| MessageFlags | |
| Pop3Removed | |
| ProcessedDate | |
| TriggerId | |
| TriggerMatch | |
| TriggerSuccess | |
| TriggerError | |
| Attachments | |
| Headers | |
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
conn.ConnectionString = “DSN=INM;”
conn.Open
Dim rs As New ADODB.Recordset
rs.LockType = adLockOptimistic
rs.ActiveConnection = conn
rs.Source = “SELECT * FROM INMForm where 1=0;”
rs.Open
rs.AddNew
rs.Fields(“WhoFrom”) = MSG_From
rs.Fields(“Received”) = MSG_Date
rs.Fields(“UID”) = MSG_UID
rs.Fields(“Subject”) = MSG_Subject
rs.Fields(“FromAddress”) = MSG_FromName
rs.Fields(“ToAddress”) = MSG_To
rs.Fields(“Headers”) = “Headers missing”
rs.Fields(“EmailText”) = MSG_Body
rs.Update
AddToLog(“INM Record added from: ” & MSG_From & ” subject ” & MSG_Subject)
Exit Sub
Err_handler:
AddToLog(“error in script ” & Err.Number & ” ” & Err.Description)
End Sub
Make sure you don’t duplicate the existing Sub Main() or End Sub. Lastly you need to click on Add Reference within the script editor – scroll down until you see the Microsoft Activex Data Objects Recordset 2.x Library and put a tick beside it and click ok. My version of this library was 2.8 – my advice is to select the newest you have. Save the trigger and return to the email2db mail screen – ie where the lower pane is marked Service Log.
Testing ? First thing is to send an email to the pop account that you are using. Wait a few moments and you should see it being picked up and processed. If you’ve followed everything above you should then be able to see the email as a record / document in the Lotus Notes database you used. If you don’t scroll up in the service log screen to see if there are any error messages. You may wish to increase the level of debug logging during this test phase – select File / Program Options / General / Logging / Debug from the email2db menu and click OK.
I hope thats given you enough detail to get this working and in another article I’ll show you how to extract further meaning from the email to allow even more information to be shown and used to categorise the email when stored in Lotus Notes.