MyITGuy
4/18/2014 - 12:20 PM

Used to send an email using Remedy contents

Used to send an email using Remedy contents

Sub SendRemedyIncidentEmail()
   
    Const adUseClient = 3
    Const adOpenStatic = 3
    Const adLockPessimistic = 2
    ' Remedy User Name
    Const AR_SYSTEM_ODBC_DRIVER_UID = ""
    ' Password for Remedy User Name (AR_SYSTEM_ODBC_DRIVER_UID)
    Const AR_SYSTEM_ODBC_DRIVER_PWD = ""
    ' Port used by the Remedy server
    Const AR_SYSTEM_ODBC_DRIVER_ARSERVERPORT = ""
    ' Leave this alone
    Const AR_SYSTEM_ODBC_DRIVER_SERVER = "NotTheServer"
    ' Leave this blank
    Const AR_SYSTEM_ODBC_DRIVER_ARAUTHENTICATION = ""
    ' NetBIOS/FQDN/IP of Remedy server
    Const AR_SYSTEM_ODBC_DRIVER_ARSERVER = ""
   
    Dim IncidentNumber
    Dim IncidentTitle
    Dim ClientLANID
    Dim RequesterLANID
    Dim Answer
   
    Do
        IncidentNumber = InputBox("Enter the Incident number:", "Incident Search", "")
        If IsNumeric(IncidentNumber) = True Then IncidentNumber = Right(String(8, "0") & IncidentNumber, 8)
        If Len(IncidentNumber) <> 8 Or IsNumeric(IncidentNumber) = False Then
            Answer = MsgBox("An invalid Incident number was entered. Try again?", vbQuestion + vbApplicationModal + vbYesNo, "Incident Search")
            If Answer = vbNo Then Exit Sub
        End If
    Loop Until Len(IncidentNumber) = 8 And IsNumeric(IncidentNumber) = True
   
    Dim Connection: Set Connection = CreateObject("ADODB.Connection")
    Dim Query: Query = "SELECT ""ClientID"", ""Lan_ID"", ""Requester"", ""Title"" FROM ""IS:INCIDENT TRACKING"" WHERE ""Ticket_ID"" = '" & IncidentNumber & "'"
    Dim ConnectionString: ConnectionString = "Driver=AR System ODBC Driver;ARServerPort=" & AR_SYSTEM_ODBC_DRIVER_ARSERVERPORT & ";SERVER=" & AR_SYSTEM_ODBC_DRIVER_SERVER & ";ARAuthentication=" & AR_SYSTEM_ODBC_DRIVER_ARAUTHENTICATION & ";ARServer=" & AR_SYSTEM_ODBC_DRIVER_ARSERVER & ";UID=" & AR_SYSTEM_ODBC_DRIVER_UID & ";PWD=" & AR_SYSTEM_ODBC_DRIVER_PWD & ";"
    Connection.ConnectionTimeout = 300
    Connection.CommandTimeout = 180
    Connection.Open ConnectionString
    Dim QueryResults: Set QueryResults = CreateObject("ADODB.Recordset")
    QueryResults.CursorLocation = adUseClient
    QueryResults.Open Query, Connection, adOpenStatic
    Set QueryResults.ActiveConnection = Nothing
    If QueryResults.BOF = False Then QueryResults.MoveFirst
    If QueryResults.EOF = True Then
        MsgBox "No results were returned.", vbCritical + vbApplicationModal + vbOKOnly, "Critical Error"
        Exit Sub
    End If
   
    If QueryResults.Recordcount = 1 Then
        QueryResults.MoveFirst
        Do While QueryResults.EOF = False
            IncidentTitle = QueryResults("Title").Value
            ClientLANID = LCase(QueryResults("Lan_ID").Value)
            RequesterLANID = LCase(QueryResults("Requester").Value)
            QueryResults.MoveNext
        Loop
    Else
        MsgBox "No results were returned.", vbCritical + vbApplicationModal + vbOKOnly, "Critical Error"
    End If
   
    If VarType(IncidentTitle) = vbString And VarType(ClientLANID) = vbString Then
        Dim NewMessage: Set NewMessage = ThisOutlookSession.CreateItem(olMailItem)
        With NewMessage
           .To = ClientLANID
           Dim CC: CC = LCase(Mid(ThisOutlookSession.GetNamespace("MAPI").CurrentUser.Address, InStrRev(ThisOutlookSession.GetNamespace("MAPI").CurrentUser.Address, "=") + 1, Len(ThisOutlookSession.GetNamespace("MAPI").CurrentUser.Address)))
           If VarType(RequesterLANID) = vbString Then CC = CC & ";" & RequesterLANID
           .CC = CC
           .Subject = "Incident #" & IncidentNumber & ": " & IncidentTitle
           .Display
        End With
    Else
        MsgBox "Incident was not found.", vbCritical + vbApplicationModal + vbOKOnly, "Incident Search"
    End If
End Sub