Solved

EMAIL loop question

Posted on 2003-11-20
3
331 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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