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
Solved

Mailer - txtTo line - adding variable

Posted on 2013-05-23
12
263 Views
Last Modified: 2013-05-23
I want to add a variable to the txtto line. The email function was working fine, but now I need to add a variable to the txto line. The variables are used later in the code, so I know they are correct. Following is the code:

<%
'THE FOLLOWING WHILE statement IS CREATING THE ERRO
While Not objRS2.EOF
      If objRS2("VRContact") = "Y" AND objRS2("VRAlternate")  = "Y" then
            txtTo = "name@##.com" & "; " & objRS2("SDEmail") & VbCrLf
      End If

   'Advance the recordset
   objRS2.MoveNext

      Wend
      objRS2.Close
      Set objRS2 = Nothing

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): "

      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



%>
0
Comment
Question by:Malloy1446
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39192452
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.
0
 
LVL 28

Expert Comment

by:omgang
ID: 39192488
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
0
 
LVL 28

Expert Comment

by:omgang
ID: 39192491
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
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39192580
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") &">"
0
 

Author Comment

by:Malloy1446
ID: 39192681
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?
0
 
LVL 28

Expert Comment

by:omgang
ID: 39192710
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
0
 
LVL 28

Expert Comment

by:omgang
ID: 39192719
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


%>
0
 
LVL 28

Expert Comment

by:omgang
ID: 39192740
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



%>
0
 

Author Comment

by:Malloy1446
ID: 39192747
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
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 39192753
Try adding
objRS2.MoveFirst
just before the second While statement (see the sample I posted just prior to your last post).  The previous While loop enumberated all the way through the recordset.  We need to move back to the first record before enumerating it again.
OM Gang
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39192778
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

Open in new window

0
 

Author Closing Comment

by:Malloy1446
ID: 39193014
Worked perfectly; code is easy to modify to add additional emails which are all included in the message.

THANK YOU!!!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

829 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