How to stop duplicate emails in email2db

Hey all,

I was having issues where sometimes within my triggers and multiple back and forths with the email2db that my triggers would fire over and over. For example:

I have a trigger called test. I have a second trigger called test 2.

First email is sent with this in the body : "We are going to start the test tomorrow"

End User gets reply

End User replies and hits reply in their client:

Hey I am more interested in test 2

-----Original Message-----
From: Parker Software Ltd [mailto:user@domain.com]
Sent: Friday, July 15, 2011 3:31 AM
To: Vecdid
Subject: test

We are going to start the test tomorrow

+++++++++++++++++++++++++++++++++++++++++++++++++++++

Now, by default email2db will reply to both triggers now. I do not want the same email with the same trigger going to the same user, makes no sense. So, I came up with the follow script I thought I would share and save someone else the time. Add this to your condition script and no dupes should ever happen.

Please note I am not using the built in database, I configured email2db to use SQL Server.

Enjoy:

Sub Main()

Dim blnDupe As Boolean
Dim strConnectionString As String
Dim strTriggerID As String
Dim strSQL As String
Dim objcon
Dim objRS

blnDupe = False

If InStr(1,MSG_Body,"MYTRIGGERPHRASE")Then

Set objcon = CreateObject("ADODB.Connection")
strConnectionString = "Provider=SQLOLEDB.1;Data " & _
"Source=MYSQLSERVER;Initial Catalog=Email2DB;user id = 'ID';password='PASSWORD'"

'Grab the trigger id from the Trigger table
strSQL = "Select TriggerID from Triggers where TriggerName = 'MYTRIGGERNAME"

objcon.Open strConnectionString

Set objRS = o.Execute(strSQL)

strTriggerID = objRS.Fields.Item(0)

objRS.Close
objcon.Close

'Check email2db table for existing reply to fromaddress by triggerID
objcon.Open strConnectionString

strSQL = "Select FromAddress from Email2DB where TriggerMatch = 1"
strSQL = strSQL + " And TriggerSuccess = 1 And TriggerID = '"
strSQL = strSQL + strTriggerID + "' AND FromAddress = '" + MSG_From + "'"

Set objRS = objcon.Execute(strSQL)

'Do we have a dupe by TriggerID/Email
Do Until objRS.EOF
blnDupe = True
objRS.MoveNext
Loop

objRS.Close
objcon.Close

'YOU MUST SET Email2DBTrigger, true fires any trigger actions and if you use a condition script all other conditions seem to be ignored

If blnDupe = True Then Email2DBTrigger = False
If blnDupe = False Then Email2DBTrigger = True

End If

Just a heads up, you may have to add to the triggerID lookup the accountID if you use multiple accounts.

So you would change this:

strSQL = "Select TriggerID from Triggers where TriggerName = 'MYTRIGGERNAME"

To this:

strSQL = "Select TriggerID from Triggers where TriggerName = 'MYTRIGGERNAME" AND AccountID = 'MYACCOUNTID'"