Mailer - txtTo line - adding variable

I think you can pretty be guaranteed that you will have problems with that.  The 'To:' field in an email is supposed to be nothing more than a comma separated list of email addresses.  It is likely that some mail servers including your own will not decode the email addresses properly if you add stuff to that field.

You need to get rid of the vbCrLf

Dim strRecips
strRecips = "name@##.com"
might be easier to use
While Not objRS2.EOF
      If objRS2("VRContact") = "Y" AND objRS2("VRAlternate")  = "Y" then
           strRecips = strRecips & ";" & objRS2("SDEmail")
            'txtTo = "name@##.com" & "; " & objRS2("SDEmail") & VbCrLf
      End If

   'Advance the recordset
   objRS2.MoveNext

      Wend
txtTo = strRecips



OM Gang

Sorry, meant to say

might be easier to use


Dim strRecips
strRecips = "name@##.com"

While Not objRS2.EOF
      If objRS2("VRContact") = "Y" AND objRS2("VRAlternate")  = "Y" then
           strRecips = strRecips & ";" & objRS2("SDEmail")
            'txtTo = "name@##.com" & "; " & objRS2("SDEmail") & VbCrLf
      End If

   'Advance the recordset
   objRS2.MoveNext

      Wend
txtTo = strRecips

I believe your txtTo line should look like this

txtTo= """Me"" <me@my.com>"

Therefore change

 txtTo = "name@##.com" & "; " & objRS2("SDEmail")

to

  txtTo = """name@##.com"" <" & objRS2("SDEmail") &">"

omgang:  your code is a partial solution. It does correctly send the email to the variable.

The "glitch" is it negates the 2nd while statement using the same SQL statement.

Do I need a 2nd SQL?

Looking at the routine you originally posted you close the objRS2 recordset and destroy the object variable for it immediately after the Wend statement for the While Loop.  You never reinstantiate the recordset object (for objRS2) after that.  I don't believe my changes had any impact on that.

Perhaps what you need to do is construct both the recipient list and the body text into variables as you're looping through the recordset?

OM Gang

Maybe rearrange the routine a bit.....
OM Gang


<%
'I don't see where you open instantiate and open objRS3 but am guessing you left out that part for this Q

txtFrom = request.form("VRRequestorsEmail")

txtSub = "Question"

txtBody = "Name: " & request.form("VRRequestorsName")
txtBody = txtBody & "<p>Location: " & objRS3("FacName") & "&nbsp;(" & objRS3("City") & ",&nbsp;" & objRS3("State") & ")"& VbCrLf
txtBody = txtBody & "<p>Email: " & request.form("VRRequestorsEmail")
txtBody = txtBody & "<p>Position: " & request.form("VRStaffType")
txtBody = txtBody & "<p>Type of Question: " & request.form("VRQuestionType")
txtBody = txtBody & "<p>Question: " & request.form("VRQuestion")

txtBody = txtBody & "<p>Local contact(s): "



'THE FOLLOWING WHILE statement IS CREATING THE ERRO
Dim strRecips
strRecips = "name@##.com"

While Not objRS2.EOF
      If objRS2("VRContact") = "Y" AND objRS2("VRAlternate")  = "Y" then
           strRecips = strRecips & ";" & objRS2("SDEmail")
            'txtTo = "name@##.com" & "; " & objRS2("SDEmail") & VbCrLf
      End If

            If objRS2("VRContact") = "Y" then
                  txtBody = txtBody & "<br>- " & objRS2("SDFname") & "&nbsp;" & objRS2("SDLName") & "&nbsp;&nbsp;&nbsp;&nbsp;" & objRS2("SDEmail") & VbCrLf
            End If

            If objRS2("VRAlternate") = "Y" then
                  txtBody = txtBody & "<br>- " & objRS2("SDFname") & "&nbsp;" & objRS2("SDLName") & "&nbsp;&nbsp;&nbsp;&nbsp;" & objRS2("SDEmail") & VbCrLf
            End If


   'Advance the recordset
   objRS2.MoveNext

      Wend
txtTo = strRecips

      objRS2.Close
      Set objRS2 = Nothing


%>

Another option is to use your original routine but simply eliminate the two lines where you initially close the objRS2 recordset and destroy the object variable.  Then you can enumerate the recordset again (and again) as you originally intended.  Perhaps not the most efficient but if it works....
OM Gang


<%
'THE FOLLOWING WHILE statement IS CREATING THE ERRO
Dim strRecips
strRecips = "name@##.com"

While Not objRS2.EOF
      If objRS2("VRContact") = "Y" AND objRS2("VRAlternate")  = "Y" then
           strRecips = strRecips & ";" & objRS2("SDEmail")
            'txtTo = "name@##.com" & "; " & objRS2("SDEmail") & VbCrLf
      End If

   'Advance the recordset
   objRS2.MoveNext

      Wend
txtTo = strRecips


txtFrom = request.form("VRRequestorsEmail")

txtSub = "Question"

txtBody = "Name: " & request.form("VRRequestorsName")
txtBody = txtBody & "<p>Location: " & objRS3("FacName") & "&nbsp;(" & objRS3("City") & ",&nbsp;" & objRS3("State") & ")"& VbCrLf
txtBody = txtBody & "<p>Email: " & request.form("VRRequestorsEmail")
txtBody = txtBody & "<p>Position: " & request.form("VRStaffType")
txtBody = txtBody & "<p>Type of Question: " & request.form("VRQuestionType")
txtBody = txtBody & "<p>Question: " & request.form("VRQuestion")

txtBody = txtBody & "<p>Local contact(s): "

      objRS2.MoveFirst
      While Not objRS2.EOF
            If objRS2("VRContact") = "Y" then
                  txtBody = txtBody & "<br>- " & objRS2("SDFname") & "&nbsp;" & objRS2("SDLName") & "&nbsp;&nbsp;&nbsp;&nbsp;" & objRS2("SDEmail") & VbCrLf
            End If

            If objRS2("VRAlternate") = "Y" then
                  txtBody = txtBody & "<br>- " & objRS2("SDFname") & "&nbsp;" & objRS2("SDLName") & "&nbsp;&nbsp;&nbsp;&nbsp;" & objRS2("SDEmail") & VbCrLf
            End If

   'Advance the recordset
   objRS2.MoveNext

      Wend
      objRS2.Close
      Set objRS2 = Nothing



%>

My email code is as follows (the 2nd divider line is where I am using another WHILE statement). The txtBody with the SDFName, SDLName and SDEmail will not print now.

'------------------------------------------------------------------
'The following correctly sends email to static email and variable emails

dim strRecips
strRecips = "name@##.com"


While Not objRS2.EOF
      If objRS2("VRContact") = "Y" OR objRS2("VRAlternate")  = "Y" then
           strRecips = strRecips & ";" & objRS2("SDEmail")
      End If

   'Advance the recordset
   objRS2.MoveNext

      Wend
'      objRS2.Close
'     Set objRS2 = Nothing

txtTo = strRecips



txtFrom = request.form("VRRequestorsEmail")

txtSub = "Question for Librarians On Call"

txtBody = "Name: " & request.form("VRRequestorsName")
txtBody = txtBody & "<p>Location: " & objRS3("FacName") & "&nbsp;(" & objRS3("City") & ",&nbsp;" & objRS3("State") & ")"& VbCrLf
txtBody = txtBody & "<p>Email: " & request.form("VRRequestorsEmail")
txtBody = txtBody & "<p>Position: " & request.form("VRStaffType")
txtBody = txtBody & "<p>Type of Question: " & request.form("VRQuestionType")
txtBody = txtBody & "<p>Question: " & request.form("VRQuestion")

txtBody = txtBody & "<p>Local contact(s): "

'------------------------------------------------------------------
'This while statement no longer displays the txtbody
      While Not objRS2.EOF
            If objRS2("VRContact") = "Y" then
                  txtBody = txtBody & "<br>- " & objRS2("SDFname") & "&nbsp;" & objRS2("SDLName") & "&nbsp;&nbsp;&nbsp;&nbsp;" & objRS2("SDEmail") & VbCrLf
            End If

            If objRS2("VRAlternate") = "Y" then

'                  txtBody = txtBody & "<br>- " & objRS2("SDFname") & "&nbsp;" & objRS2("SDLName") & "&nbsp;&nbsp;&nbsp;&nbsp;" & objRS2("SDEmail") & VbCrLf
            End If

   'Advance the recordset
   objRS2.MoveNext

      Wend
      objRS2.Close
      Set objRS2 = Nothing

This is getting confusing.  Try making the email send a function. This is very efficient.  

If not objRS2.bof or not objRS2.eof then ' make sure we have data

  arrData=objRS2.getrows() ' create an array of our data that can be used over and over without going back to the db.  Very fast

end if

' build the body
txtSub = "Question for Librarians On Call"

txtBody = "Name: " & request.form("VRRequestorsName")
txtBody = txtBody & "<p>Location: " & objRS3("FacName") & "&nbsp;(" & objRS3("City") & ",&nbsp;" & objRS3("State") & ")"& VbCrLf
txtBody = txtBody & "<p>Email: " & request.form("VRRequestorsEmail")
txtBody = txtBody & "<p>Position: " & request.form("VRStaffType")
txtBody = txtBody & "<p>Type of Question: " & request.form("VRQuestionType")
txtBody = txtBody & "<p>Question: " & request.form("VRQuestion")

txtBody = txtBody & "<p>Local contact(s): "



Subject=txtSub
Body= txtBody
from="me@myself.com"
 For r = LBound(arrData, 2) To UBound(arrData, 2)
' <-- if you have not use this method befor
' arrays are zero based and the order is determined by the order of your recordset -->

    Email 		=arrSource(0, r)
    Name		=arrSource(1, r)
   ' ----- send the mail  -----
  SendMail from,Email,subject,body
  
next


' -------  Here I create my template and make the email pretty ---------
function template(body)
html="<table><tr><td>header</td></tr>"
html=html&"<table><tr><td>"&body&"</td></tr>"
html=html&"<table><tr><td>footer</td></tr>"
template=html
end function

' ---------- this function sends the mail using the template
function SendMail(from,to,subject,body)

Set objMessage = CreateObject("CDO.Message") 
objMessage.Subject = subject
objMessage.From = from
objMessage.To = to
' objMessage.TextBody = "This is some sample message text." 
objMessage.HTMLBody = template(body)
objMessage.Send 

end function

Select allOpen in new window

Worked perfectly; code is easy to modify to add additional emails which are all included in the message.

THANK YOU!!!