Solved

EMAIL loop question

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating TABS in ASP Classic NOT using DIV 22 68
Get hold of longitude and latitude in iframe string 11 46
If condition on Html with Asp 11 17
innerHTML 7 19
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 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