tSQL = "SELECT H.cod AS cod_hist, C.cod AS cod_cham, S.nome AS situacao, C.descricao AS descr_cham, RIGHT(C.logn_inc, 7) AS logn_inc, ABRIU.NOME AS nme_inc, RIGHT(C.logn_alt, 7) AS logn_alt, ALTEROU.NOME AS nme_alt, H.descricao AS resp_cham, RIGHT(C.logn_inc, 7) + '@mail.caixa' as email "
Question
Nelson Cruz
Pessoal, estou com um problema, fiz uma rotinha para enviar e-mail no SQL, porem não estã funcionando, alguém pode me ajudar?
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'Dim conexao, registro, cntServidor
set conexao = CreateObject("ADODB.Connection")
set registro = CreateObject("ADODB.Recordset")
set rs_historico = CreateObject("ADODB.Recordset")
cntServidor = "Network Library=dbnmpntw;SERVER=RJ7399NT004;uid=saGITEL;pwd=cef7399;driver={SQL Server};database=SIATE;"
conexao.CommandTimeout = 0
conexao.Open cntServidor
tSQL = "SELECT H.cod AS cod_hist, C.cod AS cod_cham, S.nome AS situacao, C.descricao AS descr_cham, RIGHT(C.logn_inc, 7) AS logn_inc, ABRIU.NOME AS nme_inc, RIGHT(C.logn_alt, 7) AS logn_alt, ALTEROU.NOME AS nme_alt, H.descricao AS resp_cham, RIGHT(C.logn_inc, 7) + '@mail.caixa' as email "
tSQL = tSQL + "FROM EMPREGADOS ALTEROU "
tSQL = tSQL + "RIGHT OUTER JOIN tbChamados C "
tSQL = tSQL + "INNER JOIN EMPREGADOS ABRIU ON RIGHT(C.logn_inc, 7) = ABRIU.LOGIN "
tSQL = tSQL + "RIGHT OUTER JOIN tbSituacao S "
tSQL = tSQL + "RIGHT OUTER JOIN tbHistorico H ON S.cod_sit = H.cod_sit ON C.cod = H.codcham ON ALTEROU.LOGIN = RIGHT(H.logn_inc, 7) "
tSQL = tSQL + "WHERE (H.dt_envio_email IS NULL) and (H.cod_sit in(3,4))"
registro.open tSQL, conexao, 3, 3
Set objConfig = CreateObject("CDO.Configuration")
With objConfig.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.correio.caixa"
.Update
End With
' Instantiate the CDO Message object.
Set objMail = CreateObject("CDO.Message")
If registro.EOF then
Else
Do while not registro.EOF
email_destino = registro("email")
'=======================Mensagem do envio de email================================
'conteúdo da mensagem
strBody = "<html><head>" & vbCrLf & vbCrLf & _
"</head><body> <b>MENSAGEM AUTOMATICA - SIATEL -CERAT/RJ</b>" & vbCrLf & _
"<br><br>NRº DO CHAMADO: " & registro("cod_cham") &"" & _
"<br><br>SITUAÇÃO: " & registro("situacao") &"" & _
"<br><br>DESCRIÇÃO: " & registro("descr_cham") & "" & _
"<br><br>RESPOSTA: " & registro("resp_cham") & "" & _
"<br><br>RESPONDIDO POR: " & registro("logn_alt") &" - " & registro("nme_alt") & "" & _
"<br><br>EQUIPE - SUPORTE TECNOLÓGICO."
strBody = strBody & "</body></html>"
With objMail
Set .Configuration = objConfig
.From = "ceratrj@mail.caixa"
.To = email_destino
.CC = "c079249@mail.caixa,c077237@mail.caixa,p606747@mail.caixa,p590848@mail.caixa,p605082@mail.caixa"
.Subject = "MSG AUTOMATICA - SIATEL - CERAT/RJ"
.HtmlBody = strBody
.Send
End With
tSQL = "select dt_envio_email from tbhistorico where cod = " & registro("cod_hist")
rs_historico.open tSQL, conexao, 3, 3
rs_historico("dt_envio_email") = now()
rs_historico.update
rs_historico.close
registro.movenext
loop
End if
registro.close
set registro = nothing
set rs_historico = nothing
SET objMail = nothing
Main = DTSTaskExecResult_Success
End Function
Link to comment
Share on other sites
8 answers to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.