I've posted this in several forums, either I'm told I'm in the wrong forum or
I get no answers, please if you think I am in the wrong forum just don't
respond - thanks.
ok, I wrote a script that queries a database, compares some data, and sends
emails to aliases (some are individuals, some are distribution lists) 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 debugged it only execute "oMsg.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..
Let me quickly explain what my queries do: The first query searches for
pending orgs, the second query searches for existing users for the first
pending org, the third query gets the pending user(s) for that pending org.
Then the code checks to see if the sum of the pending and existing is more
than 5, if more than 5 the pending org is disapproved and they receive 1
email, if the result is not more than 5 they are approved and they get
another email. Then the code loops back if there is another org and does the
same thing. When I ran the script â??liveâ?? the first time, there was only 1
pending org, 1 pending user, and 3 existing user so they received the
â??approvedâ?? email â?? 5 times! (as I did)
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
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) & "'"
nExisting = 0
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 = "setup"
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