Solved

EMAIL loop question

Posted on 2003-11-20
3
336 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:jamesPeck
  • 2
3 Comments
 
LVL 3

Expert Comment

by:keystrokes
ID: 9788437
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
 
LVL 3

Expert Comment

by:keystrokes
ID: 9788491
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
 
LVL 4

Accepted Solution

by:
Iguanasan earned 125 total points
ID: 9790343
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now