[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Mailer - txtTo line - adding variable

Posted on 2013-05-23
12
Medium Priority
?
270 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
[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
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 84

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 54

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 2000 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 54

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on 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 …

650 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