Solved

Mailer - txtTo line - adding variable

Posted on 2013-05-23
12
261 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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Worked perfectly; code is easy to modify to add additional emails which are all included in the message.

THANK YOU!!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now