[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

EMAIL loop question

Posted on 2003-11-20
3
Medium Priority
?
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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 informatio…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

649 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