M
mgm
ok, I wrote a script that queries a database, compares some data, and sends
emails to people's email that resulted from one of the queries. When I
tested it and had it only send to me it worked fine, when I had it send to
the query result emails (To 1 person, CC to 3 people and bcc to me), we all
got it 5 times! I can't figure it out. When I debug it only executes
"oMessageObject.Send" once. The only thing i could figure out is that it was
sent out to 5 people so maybe somehow CDO repeated it for each person, but I
don't know how to figure that out..
Any help would be greatly appreciated.
Thanks!!
Below is my code (i took out my defined variables)
'*****send mail function
Function SendMail(sSendTo, sCC, sSubject, sBody)
Dim oMsg, oConfig, oFields
SendMail = false
Set oConfig = WScript.CreateObject("CDO.Configuration")
Set oMsg = WScript.CreateObject("CDO.Message")
Set oFields = oConfig.Fields
oFields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
oFields("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
EMAIL_SMTPSERVER
oFields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") =
25
oFields("http://schemas.microsoft.com/cdo/configuration/smtpaccountname")
= EMAIL_SMTPACCOUNTNAME
oFields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1
oFields("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")
= """" & EMAIL_SENDERNAME & """ <" & EMAIL_SENDERADDRESS & ">"
oFields.Update
Set oMsg.Configuration = oConfig
oMsg.From = EMAIL_SENDERNAME
oMsg.To = sSendTo
oMsg.CC = sCC
oMsg.BCC = "myemail"
oMsg.ReplyTo = """" & EMAIL_REPLYTONAME & """ <" & EMAIL_REPLYTOADDRESS &
">"
oMsg.Subject = sSubject
oMsg.BodyPart.ContentMediaType = "text/html"
oMsg.HTMLBody = sBody
oMsg.Send
SendMail = true
End Function
Set oConn = CreateObject("ADODB.Connection")
Set oPendOrgRs = CreateObject("ADODB.Recordset")
Set oExistingRs = CreateObject("ADODB.Recordset")
Set oPendUsr = CreateObject("ADODB.Recordset")
oConn.open = "My Connection String"
'*****get orgs with pending requests
oPendOrgRs.Open sPendOrgQry, oConn
If Not oPendOrgRs.BOF and Not oPendOrgRs.EOF Then
aOrgs = oPendOrgRs.GetRows()
'*****for each pending org get VAMs and emails, set queries, find # of
PIDAccess users that already exist
For nCtr = 0 to ubound(aOrgs,2)
'*****get VAM and CoVAM for org
sVam = aOrgs(1,nCtr)
sVamEmail = aOrgs(2,nCtr)
sCoVam = aOrgs(3,nCtr)
sCoVamEmail = aOrgs(4,nCtr)
sExistingQry1 = sExistingQry & aOrgs(0,nCtr) & "'"
sPendUsrQry1 = sPendUsrQry & aOrgs(0,nCtr) & "'"
oExistingRs.Open sExistingQry1, oConn
If Not oExistingRs.BOF and Not oExistingRs.EOF Then
aExisting = oExistingRs.GetRows()
nExisting = CInt(Ubound(aExisting,2) + 1)
oExistingRs.Close
End If
oPendUsr.Open sPendUsrQry1, oConn
aPending = oPendUsr.GetRows()
oPendUsr.Close
'*****add pending to existing to see if they add to over 5
**possible issue** repeated name in both lists.
If (Ubound(aPending,2) + 1) + nExisting > 5 Then
'*****build email 2 here, attach pending user name and existing
--not approved
sBody = "<html><body><font face=Arial size=2>Organization Name:
" & "<b>" & aOrgs(0,nCtr) & "</b><br><br>"
sBody = sBody & sMessage1 & "<br><br>"
sBody = sBody & "<b>Pending Access User(s):</b><br>"
For I = 0 to Ubound(aPending,2)
sBody = sBody & "<li>" & aPending(0,I) & "</li>"
Next
sBody = sBody & "<br><br>"
sBody = sBody & "<b>Existing Access User(s):</b><br>"
For I = 0 to Ubound(aExisting,2)
sBody = sBody & "<li>" & aExisting(0,I) & "</li>"
Next
sBody = sBody & "</table></font></body></html>"
'*****send mail here
sSendTo = sVamEmail & "; " & sCoVamEmail
sCC = "pksetup"
If Not SendMail(sSendTo, sCC, sSubject, sBody) Then
WScript.Echo "Failed to send e-mail"
WScript.Quit(1)
End If
Else
'*****create email 1 here, attach pending user name --approved
sBody = "<html><body><font face=Arial size=2>"
sBody = sBody & sMessage2 & "<br>"
sBody = sBody & "Organization: <b>" & aOrgs(0,nCtr) & "</b><br>"
For I = 0 to Ubound(aPending,2)
sBody = sBody & "<li>" & aPending(0,I) & "</li>"
Next
sBody = sBody & "</table></font></body></html>"
'*****send mail here
sSendTo = "support"
sCC = sVamEmail & "; " & sCoVamEmail & "; " & "setup"
If Not SendMail(sSendTo, sCC, sSubject, sBody) Then
WScript.Echo "Failed to send e-mail"
WScript.Quit(1)
End If
End If
Next
End If
oConn.Close
emails to people's email that resulted from one of the queries. When I
tested it and had it only send to me it worked fine, when I had it send to
the query result emails (To 1 person, CC to 3 people and bcc to me), we all
got it 5 times! I can't figure it out. When I debug it only executes
"oMessageObject.Send" once. The only thing i could figure out is that it was
sent out to 5 people so maybe somehow CDO repeated it for each person, but I
don't know how to figure that out..
Any help would be greatly appreciated.
Thanks!!
Below is my code (i took out my defined variables)
'*****send mail function
Function SendMail(sSendTo, sCC, sSubject, sBody)
Dim oMsg, oConfig, oFields
SendMail = false
Set oConfig = WScript.CreateObject("CDO.Configuration")
Set oMsg = WScript.CreateObject("CDO.Message")
Set oFields = oConfig.Fields
oFields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
oFields("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
EMAIL_SMTPSERVER
oFields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") =
25
oFields("http://schemas.microsoft.com/cdo/configuration/smtpaccountname")
= EMAIL_SMTPACCOUNTNAME
oFields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1
oFields("http://schemas.microsoft.com/cdo/configuration/sendemailaddress")
= """" & EMAIL_SENDERNAME & """ <" & EMAIL_SENDERADDRESS & ">"
oFields.Update
Set oMsg.Configuration = oConfig
oMsg.From = EMAIL_SENDERNAME
oMsg.To = sSendTo
oMsg.CC = sCC
oMsg.BCC = "myemail"
oMsg.ReplyTo = """" & EMAIL_REPLYTONAME & """ <" & EMAIL_REPLYTOADDRESS &
">"
oMsg.Subject = sSubject
oMsg.BodyPart.ContentMediaType = "text/html"
oMsg.HTMLBody = sBody
oMsg.Send
SendMail = true
End Function
Set oConn = CreateObject("ADODB.Connection")
Set oPendOrgRs = CreateObject("ADODB.Recordset")
Set oExistingRs = CreateObject("ADODB.Recordset")
Set oPendUsr = CreateObject("ADODB.Recordset")
oConn.open = "My Connection String"
'*****get orgs with pending requests
oPendOrgRs.Open sPendOrgQry, oConn
If Not oPendOrgRs.BOF and Not oPendOrgRs.EOF Then
aOrgs = oPendOrgRs.GetRows()
'*****for each pending org get VAMs and emails, set queries, find # of
PIDAccess users that already exist
For nCtr = 0 to ubound(aOrgs,2)
'*****get VAM and CoVAM for org
sVam = aOrgs(1,nCtr)
sVamEmail = aOrgs(2,nCtr)
sCoVam = aOrgs(3,nCtr)
sCoVamEmail = aOrgs(4,nCtr)
sExistingQry1 = sExistingQry & aOrgs(0,nCtr) & "'"
sPendUsrQry1 = sPendUsrQry & aOrgs(0,nCtr) & "'"
oExistingRs.Open sExistingQry1, oConn
If Not oExistingRs.BOF and Not oExistingRs.EOF Then
aExisting = oExistingRs.GetRows()
nExisting = CInt(Ubound(aExisting,2) + 1)
oExistingRs.Close
End If
oPendUsr.Open sPendUsrQry1, oConn
aPending = oPendUsr.GetRows()
oPendUsr.Close
'*****add pending to existing to see if they add to over 5
**possible issue** repeated name in both lists.
If (Ubound(aPending,2) + 1) + nExisting > 5 Then
'*****build email 2 here, attach pending user name and existing
--not approved
sBody = "<html><body><font face=Arial size=2>Organization Name:
" & "<b>" & aOrgs(0,nCtr) & "</b><br><br>"
sBody = sBody & sMessage1 & "<br><br>"
sBody = sBody & "<b>Pending Access User(s):</b><br>"
For I = 0 to Ubound(aPending,2)
sBody = sBody & "<li>" & aPending(0,I) & "</li>"
Next
sBody = sBody & "<br><br>"
sBody = sBody & "<b>Existing Access User(s):</b><br>"
For I = 0 to Ubound(aExisting,2)
sBody = sBody & "<li>" & aExisting(0,I) & "</li>"
Next
sBody = sBody & "</table></font></body></html>"
'*****send mail here
sSendTo = sVamEmail & "; " & sCoVamEmail
sCC = "pksetup"
If Not SendMail(sSendTo, sCC, sSubject, sBody) Then
WScript.Echo "Failed to send e-mail"
WScript.Quit(1)
End If
Else
'*****create email 1 here, attach pending user name --approved
sBody = "<html><body><font face=Arial size=2>"
sBody = sBody & sMessage2 & "<br>"
sBody = sBody & "Organization: <b>" & aOrgs(0,nCtr) & "</b><br>"
For I = 0 to Ubound(aPending,2)
sBody = sBody & "<li>" & aPending(0,I) & "</li>"
Next
sBody = sBody & "</table></font></body></html>"
'*****send mail here
sSendTo = "support"
sCC = sVamEmail & "; " & sCoVamEmail & "; " & "setup"
If Not SendMail(sSendTo, sCC, sSubject, sBody) Then
WScript.Echo "Failed to send e-mail"
WScript.Quit(1)
End If
End If
Next
End If
oConn.Close