E-mail to SMS forwarding system

This case study is included in the evaluation version of the SMS Messaging Server

1. Background

ICT World is an IT company which provides support to a number of customers. It's employees spend a lot of time at different customer sites.

2. Problem Statement

At the customer site the employees aren't always able to read their e-mail. And some of the e-mails are notifications which require immediate attention.

3. Goals of the new System

This E-mail to Sms project demonstrates how to forward e-mail messages to SMS.

4. ActiveXperts SMS Messaging Server Solution

The ForwardingDemo.vbs trigger processes incoming e-mail messages. The trigger makes use of the Employees.mdb database. This database contains entries for each e-mail recipient. Each entry in this database has an e-mail address with an associates SMS number.When an e-mail message is received, the trigger queries the Employees.mdb database to look for the particular person that received the e-mail address, and forwards an SMS notification to the recipient

' // ========================================================================
' // Projects\Forwarding Demo (E-mail to SMS)\Triggers\Triggers\Default Trigger.vbs
' // ------------------------------------------------------------------------
' // 
' // 
' // ========================================================================

Option Explicit

CONST STR_EMPLOYEESDBFILE    = "D:\Installed\ActiveXperts\SMS Messaging Server\" & _
           "Projects\Forwarding Demo (E-mail to SMS)\Database\Employees.mdb"
CONST STR_DEBUGFILE          = "D:\Installed\ActiveXperts\SMS Messaging Server\" & _
           "Sys\Tmp\ForwardingDemo.txt"
CONST STR_SMSFAILUREADDRESS  = "+00000000"

' Declaration of global objects
Dim g_objMessageDB, g_objDebugger, g_objConstants

' Creation of global objects
Set g_objConstants      = CreateObject( "Axsms-messaging-server.Constants" )
Set g_objMessageDB      = CreateObject( "Axsms-messaging-server.MessageDB" ) 
Set g_objDebugger       = CreateObject( "ActiveXperts.VbDebugger" )

' Set Debug file - for troubleshooting purposes
g_objDebugger.DebugFile = STR_DEBUGFILE
g_objDebugger.Enabled   = True      


' // ========================================================================
' // Function: ProcessMessage
' // ------------------------------------------------------------------------
' // ProcessMessage trigger function to process incoming messages
' // ========================================================================

Function ProcessMessage( numMessageID )
   Dim objMessageIn, strSmsToAddress

   g_objDebugger.WriteLine ">> ProcessMessage"

   ' Open the Message Database
   g_objMessageDB.Open
   If( g_objMessageDB.LastError <> 0 ) Then
      g_objDebugger.WriteLine "<< ProcessMessage,  unable to open database"
      Exit Function
   End If

   ' Retrieve the message that has just been received. If it fails then exit script 
   Set objMessageIn   = g_objMessageDB.FindFirstMessage( "ID = " & numMessageID ) 
   If g_objMessageDB.LastError <> 0 Then
      g_objMessageDB.Close
      g_objDebugger.WriteLine "<< ProcessMessage, FindFirstMessage failed, error: [" & _
          g_objMessageDB.LastError & "]"
      Exit Function
   End If

   If( Not GetSmsAddress( objMessageIn.ToAddress, strSmsToAddress ) ) Then
      SendSms STR_SMSFAILUREADDRESS, "Cannot forward e-mail to SMS because " & _
          objMessageIn.ToAddress & _
          " has no entry in " & STR_EMPLOYEESDBFILE
   Else
      SendSms strSmsToAddress, "New message received from " & objMessageIn.FromAddress & _
          " with subject '" & objMessageIn.Subject & "'"
   End If
 
   ' Close the Message Database
   g_objMessageDB.Close

   g_objDebugger.WriteLine "<< ProcessMessage"

End Function


' // ========================================================================
' // SendSms
' // ------------------------------------------------------------------------
' // Create an outgoing, pending SMS message in the message database
' // ========================================================================

Function SendSms( strSmsToAddress, strBody )

   Dim objMessageOut

   g_objDebugger.WriteLine ">> SendSms"

   Set objMessageOut = g_objMessageDB.Create
   If( g_objMessageDB.LastError = 0 ) Then
      objMessageOut.Direction = g_objConstants.MESSAGEDIRECTION_OUT
      objMessageOut.Type      = g_objConstants.MESSAGETYPE_SMS 
      If( strSmsToAddress = STR_SMSFAILUREADDRESS ) Then
         objMessageOut.Status    = g_objConstants.MESSAGESTATUS_FAILED
      Else
         objMessageOut.Status    = g_objConstants.MESSAGESTATUS_PENDING
      End if
      objMessageOut.ToAddress        = strSmsToAddress
      objMessageOut.ChannelID = 0  ' Any available SMS channel
      objMessageOut.Body      = strBody
      g_objMessageDB.Save objMessageOut
    End If

  g_objDebugger.WriteLine "<< SendSms"

End Function


' // ========================================================================
' // GetSmsAddress
' // ------------------------------------------------------------------------
' // Lookup e-mail address in Empl
' // ========================================================================

Function GetSmsAddress( strEmailAddress, BYREF strSmsAddress )

   Dim objConn, strQuery, RS

   g_objDebugger.WriteLine( ">> GetSmsAddress( " & strEmailAddress & " )" )

   GetSmsAddress    = False   ' Default return value
   strSmsAddress    = ""      ' Default result of referenced string

   Set objConn = CreateObject("ADODB.Connection")
   objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_EMPLOYEESDBFILE & ";"
   strQuery =  "SELECT * FROM Employees WHERE EmailAddress='" & strEmailAddress & "'"
   Set RS = objConn.Execute( strQuery )
   If RS.EOF Then
      g_objDebugger.WriteLine( "<< GetSmsAddress, record not found: " )
      Exit Function 
   End If

   GetSmsAddress = True
   strSmsAddress = RS( "SmsAddress" )

   g_objDebugger.WriteLine( "<< QueryReservations, result: " & strSmsAddress )

End Function