Somebody Please Help Me Convert this ASP to VBS

Discussion in 'ASP General' started by andy.mcvicker@siemens.com, Jun 29, 2006.

  1. Guest

    Hi Gang

    I'm not sure what needs to be changed when converting an asp to vbs.
    I'm not sure if I can with my code below. Can someone PLEASE convert
    the text below to vbs?

    Thanks a million.
    Andy


    <!--
    METADATA
    TYPE="typelib"
    UUID="CD000000-8B95-11D1-82DB-00C04FB1625D"
    NAME="CDO for Windows 2000 Library"
    -->

    <%
    ' Create a connection to the Notification database and open it.
    Set NotificationDatabaseConn = Server.CreateObject("ADODB.Connection")
    NotificationDatabaseConn.Open "Data Source=" &
    Server.Mappath("../cadim/data/notifications.mdb") &
    ";Provider=Microsoft.Jet.OLEDB.4.0;"

    lcSQL = "SELECT CADIMUser, CADIMUserPassword, SMTPServer, eMailAdmin
    FROM SystemConfig"
    Set RSSystemDefaults = NotificationDatabaseConn.Execute(lcSQL)

    lcCADIMUser = RSSystemDefaults ("CADIMUser")
    lcCADIMUserPassword = RSSystemDefaults ("CADIMUserPassword")
    lcSMTPServer = RSSystemDefaults ("SMTPServer")
    lceMailAdmin = RSSystemDefaults ("eMailAdmin")

    ' Create a connection to the CADIM database and open it.
    Set CADIMDatabaseConn = Server.CreateObject("ADODB.Connection")
    CADIMDatabaseConn.Open "dsn=CADIMProductionDB;uid=" & lcCADIMUser &
    ";pwd=" & lcCADIMUserPassword & ";"

    ' Delete all the previous notifications
    lcSQL = "DELETE * FROM Notifications"
    Set RSDeleteNotifications = NotificationDatabaseConn.Execute(lcSQL)


    ' Create a simple SQL Select statement for testing.
    lcSQL = "SELECT C_ID, ELEM_ID, S010000_AEND_ART_ENG FROM
    PLMAD_PROD.T_EWO_DAT WHERE S010000_ORG_EINH='013060' AND (LEV_IND =
    '605' OR LEV_IND = '610') AND S010000_AEND_ART_ENG <> 'Normal change A
    doc'"

    Set RSECOList = CADIMDatabaseConn.Execute(lcSQL)

    Do While Not RSECOList.EOF
    lcECOType = RSECOList("S010000_AEND_ART_ENG")
    lcECOID = RSECOList("C_ID")
    lcECONum = RSECOList("ELEM_ID")

    lcSQL = "SELECT BEARB_PRS_ID, FUNKTION, BEREICH, MAIL_AUSG, OK_KZ
    FROM PLMAD_PROD.T_S010000EWOM WHERE C_ID_1 = '" & lcECOID & "' AND
    OK_KZ = '_'"
    Set RSECODetails = CADIMDatabaseConn.Execute(lcSQL)

    lcApplicantSentRequestForApproval = "False"
    Do While Not RSECODetails.EOF
    If Not IsNull(RSECODetails("MAIL_AUSG")) Or
    Len(RSECODetails("MAIL_AUSG")) <> 0 Then
    lcApplicantSentRequestForApproval = "True"
    End If
    RSECODetails.MoveNext
    Loop


    ' Loop through the details again now we know someone has been
    ' notified once to approve the change and build the notification
    table

    If lcApplicantSentRequestForApproval = "True" Then
    RSECODetails.MoveFirst
    Do While Not RSECODetails.EOF

    lcDeptFunction = RSECODetails("BEREICH")
    lcJobRole = RSECODetails("FUNKTION")

    If lcJobRole <> "Applicant" Then

    lcSQL = "SELECT COUNT(ecotype) AS ecocount FROM
    notificationconfig " & _
    "WHERE ecotype = '" & lcECOType & "' AND jobrole =
    '" & lcJobRole & "' AND deptfunction = '" & lcDeptFunction & "'"
    Set RSECOCount =
    NotificationDatabaseConn.Execute(lcSQL)

    If RSECOCount("ecocount") > 0 Then

    lcSQL = "SELECT sequence, changestage, ecotype FROM
    notificationconfig " & _
    "WHERE ecotype = '" & lcECOType & "' AND jobrole =
    '" & lcJobRole & "' AND deptfunction = '" & lcDeptFunction & "'"
    Set RSECOConfigDetails =
    NotificationDatabaseConn.Execute(lcSQL)

    lcSequence = RSECOConfigDetails("sequence")
    lcChangeStage = RSECOConfigDetails("changestage")
    lcECOType = RSECOConfigDetails("ecotype")

    lcECOPersonId = RSECODetails("BEARB_PRS_ID")
    lcSQL = "SELECT S_USER, S_FIRST_NAME, S_EMAIL FROM
    PLMAD_PROD.T_PRS_DAT WHERE EDB_ID = '" & lcECOPersonId & "'"
    Set RSPersonDetails = CADIMDatabaseConn.Execute(lcSQL)

    lcFirstName = RSPersonDetails("S_FIRST_NAME")
    lcLastName = RSPersonDetails("S_USER")
    lceMailAddress = RSPersonDetails("S_EMAIL")


    'Insert the new notification into the notification
    table

    lcSQL = "INSERT INTO notifications " &_
    "(econum, changestage, sequence, firstname, lastname,
    emailaddress, jobrole, deptfunction, ecotype) " &_
    "VALUES ('" & lcECONum & "', '" & lcChangeStage & "', " &
    lcSequence & ", '" & lcFirstName & "', '" & lcLastName & "', '" &
    lceMailAddress & "', '" & lcJobRole & "', '" & lcDeptFunction & "', '"
    & lcECOType & "')"
    Set RSInsert = NotificationDatabaseConn.Execute(lcSQL)

    Else

    ' There was a dept function and/or job role that was not
    recognized by
    ' the notificationconfig table.

    lcBodyError = "<html><head><meta http-equiv='Content-Language'
    content='en-us'><meta http-equiv='Content-Type' content='text/html;
    charset=windows-1252'><title>CADIM EC Action
    Requirements</title></head><body><font face='Arial' style='FONT-SIZE:
    9pt'>"
    lcBodyError = lcBodyError & "The following signature is not a
    default signature. The user will not be sent a notification and this
    needs to be corrected.<br><br>"
    lcBodyError = lcBodyError & "<b>ECO Number: </b>" & lcECONum &
    "<br>"
    lcBodyError = lcBodyError & "<b>Department Function: </b>" &
    lcDeptFunction & "<br>"
    lcBodyError = lcBodyError & "<b>Job Role: </b>" & lcJobRole &
    "<br>"
    lcBodyError = lcBodyError & "</font></body></html>"

    Set cdoConfig = CreateObject("CDO.Configuration")
    With cdoConfig.Fields
    .Item(cdoSendUsingMethod) = cdoSendUsingPort
    .Item(cdoSMTPServer) = lcSMTPServer
    .Update
    End With

    Set cdoMessage = CreateObject("CDO.Message")
    With cdoMessage
    Set .Configuration = cdoConfig
    .From = lceMailAdmin
    .To = ""
    .Subject = "CADIM Comm List Member Can't Be Notified"
    .HTMLBody = lcBodyError
    .Send
    End With

    Set cdoMessage = Nothing
    Set cdoConfig = Nothing

    End If

    End If

    RSECODetails.MoveNext
    Loop
    End If

    RSECOList.MoveNext
    Loop


    ' Setup the send status for each notification. We only want to send
    ' an email to a user once with all the action.

    lcSQL = "SELECT * FROM notifications ORDER BY econum, sequence"
    Set RSNotifications = NotificationDatabaseConn.Execute(lcSQL)

    lcOldSequence = ""
    lcOldECONum = ""

    Do While Not RSNotifications.EOF
    lcBodyTableRecs = ""
    lcECONum = ""
    lcChangeStage = ""
    lcECOType = ""
    lcAction = ""

    If lcOldECONum <> RSNotifications("econum") Then
    lcOldSequence = RSNotifications("sequence")
    End If

    If lcOldSequence = RSNotifications("sequence") Then
    lcECONum = RSNotifications("econum")
    lcChangeStage = RSNotifications("changestage")
    lcECOType = RSNotifications("ecotype")
    lcJobRole = RSNotifications("jobrole")
    lcDeptFunction = RSNotifications("deptfunction")
    lcSequence = RSNotifications("sequence")
    lcFirstName = RSNotifications("firstname")
    lcLastName = RSNotifications("lastname")
    lceMailAddress = RSNotifications("emailaddress")

    lcSQL = "UPDATE notifications SET sendstatus = 'Y' " &_
    "WHERE econum = '" & lcECONum & "' And " & _
    "changestage = '" & lcChangeStage & "' And " & _
    "sequence = " & lcSequence & " And " & _
    "jobrole = '" & lcJobRole & "' And " & _
    "deptfunction = '" & lcDeptFunction & "' And " & _
    "firstname = '" & lcFirstName & "' And " & _
    "lastname = '" & lcLastName & "' And " & _
    "emailaddress = '" & lceMailAddress & "'"

    Set RSUpdate = NotificationDatabaseConn.Execute(lcSQL)
    End If

    lcOldECONum = RSNotifications("econum")
    ' lcOldSequence = RSNotifications("sequence")
    RSNotifications.MoveNext
    Loop



    'Send out the notifications.

    lcSQL = "SELECT header, body, bodytable, footer FROM eMailConfig"
    Set RSeMailDefaults = NotificationDatabaseConn.Execute(lcSQL)

    lcHeader = RSeMailDefaults("header")
    lcBody = RSeMailDefaults("body")
    lcBodyTable = RSeMailDefaults("bodytable")
    lcFooter = RSeMailDefaults("footer")


    lcSQL = "SELECT * FROM notifications WHERE sendstatus = 'Y' ORDER BY
    emailaddress, sequence DESC"
    Set RSNotifications = NotificationDatabaseConn.Execute(lcSQL)


    lcOldeMail = ""
    lceMailBody = ""
    lcBodyTableRecs = ""
    lcFirst = "True"

    Do While Not RSNotifications.EOF

    If lcOldeMail <> RSNotifications("emailaddress") And lcFirst <> "True"
    Then

    Set cdoConfig = CreateObject("CDO.Configuration")
    With cdoConfig.Fields
    .Item(cdoSendUsingMethod) = cdoSendUsingPort
    .Item(cdoSMTPServer) = lcSMTPServer
    .Update
    End With

    Set cdoMessage = CreateObject("CDO.Message")
    With cdoMessage
    Set .Configuration = cdoConfig
    .From = lceMailAdmin
    .To = ""
    .Cc = ""
    .Subject = lceMailSubject
    .HTMLBody = lceMailBody
    .Send
    End With

    Set cdoMessage = Nothing
    Set cdoConfig = Nothing

    lceMailBody = ""
    lcBodyTableRecs = ""
    lceMailSubject = ""

    End If

    lcFirst = "False"
    lceMailSubject = "Required CADIM EC Actions For " &
    RSNotifications("firstname") & " " & RSNotifications("lastname")
    lcECONum = RSNotifications("econum")
    lcChangeStage = RSNotifications("changestage")
    lcECOType = RSNotifications("ecotype")
    lcAction = "Review as the " & RSNotifications("jobrole") & " - " &
    RSNotifications("deptfunction") & " representative."

    lcBodyTableRecs = lcBodyTableRecs & "<tr>"
    lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
    style='FONT-SIZE: 9pt'>" & lcECONum & "</font></td>"
    lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
    style='FONT-SIZE: 9pt'>" & lcAction & "</font></td>"
    lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
    style='FONT-SIZE: 9pt'>" & lcECOType & "</font></td>"
    lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
    style='FONT-SIZE: 9pt'>" & lcChangeStage & "</font></td>"
    lcBodyTableRecs = lcBodyTableRecs & "</tr>"

    lceMailBody = lcHeader
    lceMailBody = lceMailBody & RSNotifications("firstname")
    lceMailBody = lceMailBody & lcBody
    lceMailBody = lceMailBody & lcBodyTable
    lceMailBody = lceMailBody & lcBodyTableRecs
    lceMailBody = lceMailBody & lcFooter


    lcOldeMail = RSNotifications("emailaddress")
    RSNotifications.MoveNext
    Loop


    'Get the last user to send the email to

    Set cdoConfig = CreateObject("CDO.Configuration")
    With cdoConfig.Fields
    .Item(cdoSendUsingMethod) = cdoSendUsingPort
    .Item(cdoSMTPServer) = lcSMTPServer
    .Update
    End With

    Set cdoMessage = CreateObject("CDO.Message")
    With cdoMessage
    Set .Configuration = cdoConfig
    .From = lceMailAdmin
    .To = ""
    .Cc = ""
    .Subject = lceMailSubject
    .HTMLBody = lceMailBody
    .Send
    End With

    Set cdoMessage = Nothing
    Set cdoConfig = Nothing


    ' Close the connection to the databases
    CADIMDatabaseConn.Close
    NotificationDatabaseConn.Close
    %>
     
    , Jun 29, 2006
    #1
    1. Advertising

  2. Evertjan. Guest

    wrote on 29 jun 2006 in microsoft.public.inetserver.asp.general:

    > I'm not sure what needs to be changed when converting an asp to vbs.


    ASP is a platform for writing serverside code in VBS or Jscript [or ...].

    VBS is a scripting language.

    You cannot convert a platform INTO one of it's supported languages.


    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jun 29, 2006
    #2
    1. Advertising

  3. On 29 Jun 2006 21:22:41 GMT, "Evertjan." <>
    wrote:
    in <Xns97F1EDD4B40B2eejj99@194.109.133.242>

    > wrote on 29 jun 2006 in microsoft.public.inetserver.asp.general:
    >
    >> I'm not sure what needs to be changed when converting an asp to vbs.

    >
    >ASP is a platform for writing serverside code in VBS or Jscript [or ...].
    >
    >VBS is a scripting language.
    >
    >You cannot convert a platform INTO one of it's supported languages.


    Isn't ASP also an acronym for active server page? Then you'd have to
    take it's meaning from context and in this case the OP was obviously
    referring to an asp page. Ah, the Department of Redundancy Department.

    ---
    Stefan Berglund
     
    Stefan Berglund, Jun 30, 2006
    #3
  4. That's a pretty narrow-minded answer -- you must be taking lessons from
    Celko? Of course, Andy is asking about converting an ASP page (not the
    platform, duh) to a VBS script. Which is a pretty common task, in fact one
    I have performed many times.






    > ASP is a platform for writing serverside code in VBS or Jscript [or ...].
    >
    > VBS is a scripting language.
    >
    > You cannot convert a platform INTO one of it's supported languages.
     
    Aaron Bertrand [SQL Server MVP], Jun 30, 2006
    #4
  5. Andy, the most complicated part is getting all of your constants from the
    CDO library defined in the VBS script, since you cannot use that metadata
    tag in VBS as far as I know. So far the only constant I see in use is
    cdoSendUsingPort, and you can simply change these to a hard-coded value of
    2, or at the beginning of the VBS script, place this line:

    const cdoSendUsingPort = 2

    The most common pitfalls when switching over:
    - forgetting to change response.write to either msgbox or
    logfile.writeline
    - forgetting to take out any HTML, css or client-side script output
    And forgetting that you're no longer in the context of a "server":
    - changing references such as server.createobject to createobject
    - removing things like server.transfer / response.redirect - you're no
    longer in a browser
    - similarly with server.mappath, this no longer makes sense

    Basically, most of your code looks fine, except changing things like
    Server.CreateObject("ADODB.Connection") to CreateObject("ADODB.Connection")
    and changing Server.MapPath("../cadim/data/notifications.mdb") to a local
    path (e.g. c:\inetpub\wwwroot\cadim\data\notifications.mdb).




    <> wrote in message
    news:...
    > Hi Gang
    >
    > I'm not sure what needs to be changed when converting an asp to vbs.
    > I'm not sure if I can with my code below. Can someone PLEASE convert
    > the text below to vbs?
    >
    > Thanks a million.
    > Andy
    >
    >
    > <!--
    > METADATA
    > TYPE="typelib"
    > UUID="CD000000-8B95-11D1-82DB-00C04FB1625D"
    > NAME="CDO for Windows 2000 Library"
    > -->
    >
    > <%
    > ' Create a connection to the Notification database and open it.
    > Set NotificationDatabaseConn = Server.CreateObject("ADODB.Connection")
    > NotificationDatabaseConn.Open "Data Source=" &
    > Server.Mappath("../cadim/data/notifications.mdb") &
    > ";Provider=Microsoft.Jet.OLEDB.4.0;"
    >
    > lcSQL = "SELECT CADIMUser, CADIMUserPassword, SMTPServer, eMailAdmin
    > FROM SystemConfig"
    > Set RSSystemDefaults = NotificationDatabaseConn.Execute(lcSQL)
    >
    > lcCADIMUser = RSSystemDefaults ("CADIMUser")
    > lcCADIMUserPassword = RSSystemDefaults ("CADIMUserPassword")
    > lcSMTPServer = RSSystemDefaults ("SMTPServer")
    > lceMailAdmin = RSSystemDefaults ("eMailAdmin")
    >
    > ' Create a connection to the CADIM database and open it.
    > Set CADIMDatabaseConn = Server.CreateObject("ADODB.Connection")
    > CADIMDatabaseConn.Open "dsn=CADIMProductionDB;uid=" & lcCADIMUser &
    > ";pwd=" & lcCADIMUserPassword & ";"
    >
    > ' Delete all the previous notifications
    > lcSQL = "DELETE * FROM Notifications"
    > Set RSDeleteNotifications = NotificationDatabaseConn.Execute(lcSQL)
    >
    >
    > ' Create a simple SQL Select statement for testing.
    > lcSQL = "SELECT C_ID, ELEM_ID, S010000_AEND_ART_ENG FROM
    > PLMAD_PROD.T_EWO_DAT WHERE S010000_ORG_EINH='013060' AND (LEV_IND =
    > '605' OR LEV_IND = '610') AND S010000_AEND_ART_ENG <> 'Normal change A
    > doc'"
    >
    > Set RSECOList = CADIMDatabaseConn.Execute(lcSQL)
    >
    > Do While Not RSECOList.EOF
    > lcECOType = RSECOList("S010000_AEND_ART_ENG")
    > lcECOID = RSECOList("C_ID")
    > lcECONum = RSECOList("ELEM_ID")
    >
    > lcSQL = "SELECT BEARB_PRS_ID, FUNKTION, BEREICH, MAIL_AUSG, OK_KZ
    > FROM PLMAD_PROD.T_S010000EWOM WHERE C_ID_1 = '" & lcECOID & "' AND
    > OK_KZ = '_'"
    > Set RSECODetails = CADIMDatabaseConn.Execute(lcSQL)
    >
    > lcApplicantSentRequestForApproval = "False"
    > Do While Not RSECODetails.EOF
    > If Not IsNull(RSECODetails("MAIL_AUSG")) Or
    > Len(RSECODetails("MAIL_AUSG")) <> 0 Then
    > lcApplicantSentRequestForApproval = "True"
    > End If
    > RSECODetails.MoveNext
    > Loop
    >
    >
    > ' Loop through the details again now we know someone has been
    > ' notified once to approve the change and build the notification
    > table
    >
    > If lcApplicantSentRequestForApproval = "True" Then
    > RSECODetails.MoveFirst
    > Do While Not RSECODetails.EOF
    >
    > lcDeptFunction = RSECODetails("BEREICH")
    > lcJobRole = RSECODetails("FUNKTION")
    >
    > If lcJobRole <> "Applicant" Then
    >
    > lcSQL = "SELECT COUNT(ecotype) AS ecocount FROM
    > notificationconfig " & _
    > "WHERE ecotype = '" & lcECOType & "' AND jobrole =
    > '" & lcJobRole & "' AND deptfunction = '" & lcDeptFunction & "'"
    > Set RSECOCount =
    > NotificationDatabaseConn.Execute(lcSQL)
    >
    > If RSECOCount("ecocount") > 0 Then
    >
    > lcSQL = "SELECT sequence, changestage, ecotype FROM
    > notificationconfig " & _
    > "WHERE ecotype = '" & lcECOType & "' AND jobrole =
    > '" & lcJobRole & "' AND deptfunction = '" & lcDeptFunction & "'"
    > Set RSECOConfigDetails =
    > NotificationDatabaseConn.Execute(lcSQL)
    >
    > lcSequence = RSECOConfigDetails("sequence")
    > lcChangeStage = RSECOConfigDetails("changestage")
    > lcECOType = RSECOConfigDetails("ecotype")
    >
    > lcECOPersonId = RSECODetails("BEARB_PRS_ID")
    > lcSQL = "SELECT S_USER, S_FIRST_NAME, S_EMAIL FROM
    > PLMAD_PROD.T_PRS_DAT WHERE EDB_ID = '" & lcECOPersonId & "'"
    > Set RSPersonDetails = CADIMDatabaseConn.Execute(lcSQL)
    >
    > lcFirstName = RSPersonDetails("S_FIRST_NAME")
    > lcLastName = RSPersonDetails("S_USER")
    > lceMailAddress = RSPersonDetails("S_EMAIL")
    >
    >
    > 'Insert the new notification into the notification
    > table
    >
    > lcSQL = "INSERT INTO notifications " &_
    > "(econum, changestage, sequence, firstname, lastname,
    > emailaddress, jobrole, deptfunction, ecotype) " &_
    > "VALUES ('" & lcECONum & "', '" & lcChangeStage & "', " &
    > lcSequence & ", '" & lcFirstName & "', '" & lcLastName & "', '" &
    > lceMailAddress & "', '" & lcJobRole & "', '" & lcDeptFunction & "', '"
    > & lcECOType & "')"
    > Set RSInsert = NotificationDatabaseConn.Execute(lcSQL)
    >
    > Else
    >
    > ' There was a dept function and/or job role that was not
    > recognized by
    > ' the notificationconfig table.
    >
    > lcBodyError = "<html><head><meta http-equiv='Content-Language'
    > content='en-us'><meta http-equiv='Content-Type' content='text/html;
    > charset=windows-1252'><title>CADIM EC Action
    > Requirements</title></head><body><font face='Arial' style='FONT-SIZE:
    > 9pt'>"
    > lcBodyError = lcBodyError & "The following signature is not a
    > default signature. The user will not be sent a notification and this
    > needs to be corrected.<br><br>"
    > lcBodyError = lcBodyError & "<b>ECO Number: </b>" & lcECONum &
    > "<br>"
    > lcBodyError = lcBodyError & "<b>Department Function: </b>" &
    > lcDeptFunction & "<br>"
    > lcBodyError = lcBodyError & "<b>Job Role: </b>" & lcJobRole &
    > "<br>"
    > lcBodyError = lcBodyError & "</font></body></html>"
    >
    > Set cdoConfig = CreateObject("CDO.Configuration")
    > With cdoConfig.Fields
    > .Item(cdoSendUsingMethod) = cdoSendUsingPort
    > .Item(cdoSMTPServer) = lcSMTPServer
    > .Update
    > End With
    >
    > Set cdoMessage = CreateObject("CDO.Message")
    > With cdoMessage
    > Set .Configuration = cdoConfig
    > .From = lceMailAdmin
    > .To = ""
    > .Subject = "CADIM Comm List Member Can't Be Notified"
    > .HTMLBody = lcBodyError
    > .Send
    > End With
    >
    > Set cdoMessage = Nothing
    > Set cdoConfig = Nothing
    >
    > End If
    >
    > End If
    >
    > RSECODetails.MoveNext
    > Loop
    > End If
    >
    > RSECOList.MoveNext
    > Loop
    >
    >
    > ' Setup the send status for each notification. We only want to send
    > ' an email to a user once with all the action.
    >
    > lcSQL = "SELECT * FROM notifications ORDER BY econum, sequence"
    > Set RSNotifications = NotificationDatabaseConn.Execute(lcSQL)
    >
    > lcOldSequence = ""
    > lcOldECONum = ""
    >
    > Do While Not RSNotifications.EOF
    > lcBodyTableRecs = ""
    > lcECONum = ""
    > lcChangeStage = ""
    > lcECOType = ""
    > lcAction = ""
    >
    > If lcOldECONum <> RSNotifications("econum") Then
    > lcOldSequence = RSNotifications("sequence")
    > End If
    >
    > If lcOldSequence = RSNotifications("sequence") Then
    > lcECONum = RSNotifications("econum")
    > lcChangeStage = RSNotifications("changestage")
    > lcECOType = RSNotifications("ecotype")
    > lcJobRole = RSNotifications("jobrole")
    > lcDeptFunction = RSNotifications("deptfunction")
    > lcSequence = RSNotifications("sequence")
    > lcFirstName = RSNotifications("firstname")
    > lcLastName = RSNotifications("lastname")
    > lceMailAddress = RSNotifications("emailaddress")
    >
    > lcSQL = "UPDATE notifications SET sendstatus = 'Y' " &_
    > "WHERE econum = '" & lcECONum & "' And " & _
    > "changestage = '" & lcChangeStage & "' And " & _
    > "sequence = " & lcSequence & " And " & _
    > "jobrole = '" & lcJobRole & "' And " & _
    > "deptfunction = '" & lcDeptFunction & "' And " & _
    > "firstname = '" & lcFirstName & "' And " & _
    > "lastname = '" & lcLastName & "' And " & _
    > "emailaddress = '" & lceMailAddress & "'"
    >
    > Set RSUpdate = NotificationDatabaseConn.Execute(lcSQL)
    > End If
    >
    > lcOldECONum = RSNotifications("econum")
    > ' lcOldSequence = RSNotifications("sequence")
    > RSNotifications.MoveNext
    > Loop
    >
    >
    >
    > 'Send out the notifications.
    >
    > lcSQL = "SELECT header, body, bodytable, footer FROM eMailConfig"
    > Set RSeMailDefaults = NotificationDatabaseConn.Execute(lcSQL)
    >
    > lcHeader = RSeMailDefaults("header")
    > lcBody = RSeMailDefaults("body")
    > lcBodyTable = RSeMailDefaults("bodytable")
    > lcFooter = RSeMailDefaults("footer")
    >
    >
    > lcSQL = "SELECT * FROM notifications WHERE sendstatus = 'Y' ORDER BY
    > emailaddress, sequence DESC"
    > Set RSNotifications = NotificationDatabaseConn.Execute(lcSQL)
    >
    >
    > lcOldeMail = ""
    > lceMailBody = ""
    > lcBodyTableRecs = ""
    > lcFirst = "True"
    >
    > Do While Not RSNotifications.EOF
    >
    > If lcOldeMail <> RSNotifications("emailaddress") And lcFirst <> "True"
    > Then
    >
    > Set cdoConfig = CreateObject("CDO.Configuration")
    > With cdoConfig.Fields
    > .Item(cdoSendUsingMethod) = cdoSendUsingPort
    > .Item(cdoSMTPServer) = lcSMTPServer
    > .Update
    > End With
    >
    > Set cdoMessage = CreateObject("CDO.Message")
    > With cdoMessage
    > Set .Configuration = cdoConfig
    > .From = lceMailAdmin
    > .To = ""
    > .Cc = ""
    > .Subject = lceMailSubject
    > .HTMLBody = lceMailBody
    > .Send
    > End With
    >
    > Set cdoMessage = Nothing
    > Set cdoConfig = Nothing
    >
    > lceMailBody = ""
    > lcBodyTableRecs = ""
    > lceMailSubject = ""
    >
    > End If
    >
    > lcFirst = "False"
    > lceMailSubject = "Required CADIM EC Actions For " &
    > RSNotifications("firstname") & " " & RSNotifications("lastname")
    > lcECONum = RSNotifications("econum")
    > lcChangeStage = RSNotifications("changestage")
    > lcECOType = RSNotifications("ecotype")
    > lcAction = "Review as the " & RSNotifications("jobrole") & " - " &
    > RSNotifications("deptfunction") & " representative."
    >
    > lcBodyTableRecs = lcBodyTableRecs & "<tr>"
    > lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
    > style='FONT-SIZE: 9pt'>" & lcECONum & "</font></td>"
    > lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
    > style='FONT-SIZE: 9pt'>" & lcAction & "</font></td>"
    > lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
    > style='FONT-SIZE: 9pt'>" & lcECOType & "</font></td>"
    > lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
    > style='FONT-SIZE: 9pt'>" & lcChangeStage & "</font></td>"
    > lcBodyTableRecs = lcBodyTableRecs & "</tr>"
    >
    > lceMailBody = lcHeader
    > lceMailBody = lceMailBody & RSNotifications("firstname")
    > lceMailBody = lceMailBody & lcBody
    > lceMailBody = lceMailBody & lcBodyTable
    > lceMailBody = lceMailBody & lcBodyTableRecs
    > lceMailBody = lceMailBody & lcFooter
    >
    >
    > lcOldeMail = RSNotifications("emailaddress")
    > RSNotifications.MoveNext
    > Loop
    >
    >
    > 'Get the last user to send the email to
    >
    > Set cdoConfig = CreateObject("CDO.Configuration")
    > With cdoConfig.Fields
    > .Item(cdoSendUsingMethod) = cdoSendUsingPort
    > .Item(cdoSMTPServer) = lcSMTPServer
    > .Update
    > End With
    >
    > Set cdoMessage = CreateObject("CDO.Message")
    > With cdoMessage
    > Set .Configuration = cdoConfig
    > .From = lceMailAdmin
    > .To = ""
    > .Cc = ""
    > .Subject = lceMailSubject
    > .HTMLBody = lceMailBody
    > .Send
    > End With
    >
    > Set cdoMessage = Nothing
    > Set cdoConfig = Nothing
    >
    >
    > ' Close the connection to the databases
    > CADIMDatabaseConn.Close
    > NotificationDatabaseConn.Close
    > %>
    >
     
    Aaron Bertrand [SQL Server MVP], Jun 30, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SmltIE1hY2U=?=

    Please, somebody help...

    =?Utf-8?B?SmltIE1hY2U=?=, Feb 26, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    287
    Trint Smith
    Feb 26, 2004
  2. Shapper
    Replies:
    3
    Views:
    552
    Scott Allen
    Jul 9, 2005
  3. Jai
    Replies:
    0
    Views:
    756
  4. Replies:
    14
    Views:
    491
    Zentrader
    Aug 3, 2007
  5. Replies:
    2
    Views:
    345
Loading...

Share This Page