EMAIL loop question

I have this loop which goes through and emails managers if someone buys something in there department however if the username is equal to the manager I dont want it to email them and go onto the next line... for some reason it emails everytime and emails the same person 2 times.


Here is what I have..

if line_error = 1 then
I = 1
for I = 1 to line_a
cur_dpt = department(I)

SET CC = server.createobject("adodb.recordset")
set CC = objConn.execute("SELECT manager FROM suf_po_dpt_managers WHERE dept='" & cur_dpt & "'")
dpt_manager            =      cc( "manager" )

if username = dpt_manager then
' go to next department but it doesnt...
end if

Dim objCDO
Dim strTo
Dim strFrom
Dim strSubject
Dim strBody

strTo = " " & dpt_manager & "@tfxhose.com"
strBody = "<BR><center>" & full_name & " has placed a PO in your Department<br><BR>  Please click on the link <a href=""http://10.151.1.4/posystem/suffield_po/deptpo.asp?dpt_num=" & cur_dpt & """>to view the PO request</a>."
Set jmail = Server.CreateObject("JMail.Message")
jmail.Logging = true
      jmail.AddRecipient strTo , ""
      jmail.From = "PO System"
      
      jmail.Subject = "" & full_name & " placed a PO in your Department"
      jmail.Body = ""

      jmail.HTMLBody = strBody
            
      jmail.Send( "smtp.teleflex.com" )

next
end if
jamesPeckAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

keystrokesCommented:
How about just change your query to:
"SELECT manager FROM suf_po_dpt_managers WHERE dept='" & cur_dpt & "'"  and manager <> '" & username & "'"

This way you only go throught the recordset once.
0
keystrokesCommented:
Also, I think it's the code "for I = 1 to a_line...Next" that did the email twice.  Maybe you can just loop throught the recordset and send out the mail.  One more thing, put the Set jmail = Server.CreateObject("JMail.Message") code outside the loop, this way you don't create the same object so many times.
0
IguanasanCommented:
This isn't perfect but it should work.  There are a number of changes here.  Keystrokes has a good idea that maybe you could alter your SQL statement and cycle through the recordset rather than making multiple calls to the database but without a full copy of the code and/or datastructures it's hard to see how to change it for sure.  This should get you going though: Note(I've added comments to explain as much as possible)

<%
Dim jmail, CC
' Create a single instance of the CC recordset object
SET CC = server.createobject("adodb.recordset")
' Create the jmail object for sending the email.
Set jmail = Server.CreateObject("JMail.Message")
' Fill in the stuff that will not change from person to person.
jmail.From = "PO System"
jmail.Subject = "" & full_name & " placed a PO in your Department"

if line_error = 1 then
      I = 1
      for I = 1 to line_a
            cur_dpt = department(I)

            SET CC = objConn.execute("SELECT manager FROM suf_po_dpt_managers WHERE dept='" & cur_dpt & "'")
            ' This next statement actually returns a Field object because of the way you are assigning it.
            'dpt_manager = cc( "manager" )
            ' Change it to this:
            dpt_manager = cc.Fields.Item("Manager").Value ' Obtain the deptmanager text string (not the Field object)

            if username <> dpt_manager then
                  Dim objCDO
                  Dim strTo
                  Dim strFrom
                  Dim strSubject
                  Dim strBody

                  strTo = " " & dpt_manager & "@tfxhose.com"
                  strBody = "<BR><center>" & full_name & " has placed a PO in your Department<br><BR>  Please click on the link <a href=""http://10.151.1.4/posystem/suffield_po/deptpo.asp?dpt_num=" & cur_dpt & """>to view the PO request</a>."
                  jmail.Body = ""
                  jmail.HTMLBody = strBody
                  jmail.Logging = true
                  jmail.ClearRecipients ' Clear the last recipient otherwise it just ADDS another recipient
                  jmail.AddRecipient strTo , "" ' Add the current recipient.
                  jmail.Send( "smtp.teleflex.com" ) ' Send the email
            end if
      next
end if
%>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.