|
Home > Archive > IIS and SMTP > October 2004 > My CDO Code is SPAMMING!? :) help
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
My CDO Code is SPAMMING!? :) help
|
|
|
| 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
| |
| Jason Brown [MSFT] 2004-09-29, 3:08 am |
| looks to me like you're looping and appending a new name each time through
when in fact you should be starting afresh.
i.e.
first time through, sends to fred.
second time, sends to fred and bob
third time, sends to fred, bob and margaret
fourth time..... you get the picture...
A little careful debugging and you should be able to see this happening.
--
Jason Brown
Microsoft GTSC, IIS
This posting is provided "AS IS" with no warranties, and confers no
rights.
"mgm" <mgm@discussions.microsoft.com> wrote in message
news:09BAF27C-E650-42C4-89E9-0570AE78FB39@microsoft.com...
> 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
>
>
| |
|
| Actually it's not doing that. First of all the send mail function is not in
a loop. Let me explain what I do know.
The first query searches for the orgs of pending users, 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) So, when I
debugged, all email aliases are assigned 1 time and sent to the function that
ran 1 time since there was only 1 pending user(org).
"Jason Brown [MSFT]" wrote:
> looks to me like you're looping and appending a new name each time through
> when in fact you should be starting afresh.
>
> i.e.
>
> first time through, sends to fred.
> second time, sends to fred and bob
> third time, sends to fred, bob and margaret
> fourth time..... you get the picture...
>
> A little careful debugging and you should be able to see this happening.
>
> --
> Jason Brown
> Microsoft GTSC, IIS
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "mgm" <mgm@discussions.microsoft.com> wrote in message
> news:09BAF27C-E650-42C4-89E9-0570AE78FB39@microsoft.com...
>
>
>
|
|
|
|
|