Email Job Table

Set cnt = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.recordset")


stADO = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GHRMS18;Data Source=(local)"
stSQL = "select top 1 runStatus,upload_conflicts+download_conflicts tot_conflict,start_time,end_time,duration," & _
"(select top 1 rh.comments from dbo.MSmerge_history rh  where rh.session_id = sessions2.session_id order by rh.session_id desc, rh.timestamp desc" & _
") as LastMessage from msMerge_Sessions sessions2 order by session_id desc"


With cnt
.Open stADO
         .CommandTimeout = 0
         Set rst = .Execute(stSQL)
End With


Set objMessage = CreateObject("CDO.Message")
While Not rst.EOF
         if rst("runStatus")=2 and rst("tot_conflict")>0 then
objMessage.Subject = "Replication Conflict - GHRMS18 LB1 (Table)"
objMessage.Sender = "isdept@ptids.co.id"
objMessage.From = "isdept@ptids.co.id"
objMessage.To = "cazico@genesysindonesia.com;nristin@genesysindonesia.com;ywardojo@ptids.co.id;drachmanto@ptids.co.id"
objMessage.CC = "notification@genesysindonesia.com"
objMessage.TextBody = rst("lastMessage") & vbcr & "Running on : " & rst("start_time") & " - " &  rst("end_time")
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.ids.co.id"
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
objMessage.Configuration.Fields.Update
objMessage.Send
elseif rst("runStatus")=6 then
objMessage.Subject = "Replication Fail - GHRMS18 LB1 (Table)"
objMessage.Sender = "isdept@ptids.co.id"
objMessage.From = "isdept@ptids.co.id"
objMessage.To = "cazico@genesysindonesia.com;nristin@genesysindonesia.com;ywardojo@ptids.co.id;drachmanto@ptids.co.id"
objMessage.CC = "notification@genesysindonesia.com"
objMessage.TextBody = rst("lastMessage") & vbcr & "Running on : " & rst("start_time") & " - " &  rst("end_time")
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.ids.co.id"
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
objMessage.Configuration.Fields.Update
objMessage.Send
else
if rst("runStatus")=1 then
stat = "Start"
elseif rst("runStatus")=2 then
stat = "Success"
elseif rst("runStatus")=3 then
stat = "In Progress"
elseif rst("runStatus")=4 then
stat = "Idle"
elseif rst("runStatus")=5 then
stat = "Retry"
end if
objMessage.Subject = "Replication " & stat & " - GHRMS18 LB1 (Table)"
objMessage.Sender = "isdept@ptids.co.id"
objMessage.From = "isdept@ptids.co.id"
objMessage.To = "notification@genesysindonesia.com;cazico@genesysindonesia.com;nristin@genesysindonesia.com;ywardojo@ptids.co.id;drachmanto@ptids.co.id"
objMessage.TextBody = rst("lastMessage") & vbcr & "Running on : " & rst("start_time") & " - " &  rst("end_time")
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.ids.co.id"
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
objMessage.Configuration.Fields.Update
objMessage.Send
end if
        rst.movenext
Wend
set objMessage = Nothing


rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing