Multi-Select imput box to send automatic email.

[jaysonrowe]

Here's my problem - I have a page I inherited that based on a site number entered by the employee was populated w/ names of contacts from that site. The employee could select one of the names, and have pre-written email automatically sent.

Now, they want to email multiple people at the same site w/ one process, so simple, change the drop down list to a multi-select, but how to get the data from the multi-select box into the database to generate the email?

Here is some of my code (or at least what I've been tweaking trying to get this to work):

'#### MAIN ####################################################################
dim CONTACT
dim CONTACT_NAME
dim REGCODE
dim FULL_EMAIL
dim MAIL_TYPE
dim SUBJECT
dim EMAIL_ADDR
dim LAST_NAME
dim FIRST_NAME
dim ACCOUNT_NAME
dim ACCOUNTID
dim SITENUMBER
dim UNIQUE_ID
CONTACT = left(request.form("dropContact"),12)
  
REGCODE = request("uid")
MAIL_TYPE = request("dropType")
EMAIL_ADDR=""
getContactInfo(CONTACT)
getContactInfo(id)
FULL_EMAIL = emailBody(MAIL_TYPE)
if email_addr <> "" then
   Dim MyCDONTSMail2 
   Set MyCDONTSMail2 = CreateObject("CDONTS.NewMail")
   MyCDONTSMail2.From= "orders@somewhere.com"
   MyCDONTSMail2.To=email_addr
   MyCDONTSMail2.BCC="someone@somewhere.com"
   MyCDONTSMail2.Subject=SUBJECT
   MyCDONTSMail2.BodyFormat=0
   MyCDONTSMail2.MailFormat=0
   MyCDONTSMail2.Body=FULL_EMAIL
   MyCDONTSMail2.Send
   set MyCDONTSMail2=nothing
end if

AddToDB()




 

'##############################################################################
'#### SUBS ####################################################################
'------------------------------------------------------------------------------
private sub getContactInfo(id)
   dim rs, sql
   
   call dbOpenSOME_DBRO()
   set rs = server.createobject("ADODB.RECORDSET")
   sql = "select q4.*, q1.account, q2.ext_accountid from sysdba.account q1 " & _
         "left join sysdba.ext_account q2 on q1.accountid=q2.accountid " & _
         "left join sysdba.ext_contact q3 on q3.contactid='" & id & "' " & _
         "left join sysdba.contact q4 on q3.contactid=q4.contactid " & _
         "where q1.accountid = q4.accountid "
   rs.open sql, cnSOME_DB,0,1


 while NOT rs.eof
      EMAIL_ADDR=rs("EMAIL")
      LAST_NAME=replace(rs("Lastname"),"'","")
      FIRST_NAME=replace(rs("firstname"),"'","")
      CONTACT_NAME=FIRST_NAME & " " & LAST_NAME
      ACCOUNT_NAME=rs("account")
      ACCOUNTID=rs("accountid")
      SITENUMBER=rs("ext_accountid")
      rs.movenext()
  

wend


end sub
'------------------------------------------------------------------------------
'------------------------------------------------------------------------------

Then comes the "emailBody(t) sub, which I will leave off here for privacy issues

Then the pages finished out with:

Private Sub AddToDB()
   Dim rs, sql, rs2, sql2, lastName, firstName, AccName
   Dim siteNumber, contid, newContact
   
   GetNewID("HISTORY")
   siteNumber = Right(String(6,"0") & SITENUMBER, 6)
   
        
   Dim smallNote, longNote
   longNote =  "A Client Module Addon Email"
   smallNote = longNote
   
    
   If len(smallNote) > 255 Then
      smallNote = mid(smallNote,1,254)
   End If
      
   Dim nowT, dateT, startT, modT, endT
   nowT = iso_date(now())
   startT = iso_date(now())
   dateT = year(nowT) & "/" & month(nowT) & "/" & day(nowT)
   
   lastName = LAST_NAME 
   firstName = FIRST_NAME
   AccName = Replace(ACCOUNT_NAME,"'","")

   
      
   sql = "insert into history (HISTORYID, ACTIVITYID, " & _
         "ACCOUNTID, CONTACTID, CONTACTNAME, " & _
         "CATEGORY, STARTDATE, TIMELESS, USERID, " & _
         "USERNAME, ORIGINALDATE, CREATEDATE, " & _
         "CREATEUSER, MODIFYDATE, MODIFYUSER, " & _
         "COMPLETEDDATE, COMPLETEDUSER, Notes, " & _
         "LONGNOTES, ATTACHMENT, DESCRIPTION, DURATION, " & _
         "TYPE, ACCOUNTNAME, RESULT) values('" & UNIQUE_ID & "','" & _
         UNIQUE_ID & "','" & ACCOUNTID & "','" & _
         CONTACT  & "','" & lastName  & ", " & _
         firstName & "','Implementation Services" & _
         "','" & startT & "','T','UMQJXA0000G0','User, Web','" & _
         startT & "','" & nowT & "','" & "UMQJXA0000G0" & "','" & _
         nowT & "','UMQJXA0000G0','" & startT & "','UMQJXA0000G0','" & _
         smallNote  & "','" & longNote & _
         "','F','Quick Notes','15','262148','" & AccName & "','COMPLETE')" 
         
               
         
   Call dbOpenSOME_DBOLEDB()      
   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.open sql, cnSOME_DBOLEDB,2,3
   
   Set rs = Nothing
End Sub
'------------------------------------------------------------------------------
'------------------------------------------------------------------------------
Function GetNewID (TableName)
  Dim rs
  Call dbOpenSOME_DBOLEDB
  Set rs = cnSOME_DBOLEDB.Execute ("slx_dbids('" & TableName & "', 1)")
  UNIQUE_ID = rs.Fields(0).Value & ""
  cnSOME_DBOLEDB.Close
  Set cnSOME_DBOLEDB = Nothing
  Set rs = Nothing
End Function
'------------------------------------------------------------------------------
'##############################################################################
%>
<html>

<head>
<title>Email</title>

</head>

<body>
<%
if email_addr <> "" then
%>

<script language='javascript'>
alert("Email has been Sent");
</script>
<%
else
%>
<script language='javascript'>
alert("No Email address was found for contact. Please Print email and take the appropriate action");
</script>
<%
end if
%>
<%=FULL_EMAIL%>

</body>

</html>

Here is an example of something I've tried already:

For example, if I do this in the main section:

for i=1 to Request.Form("dropContact").Count
  
REGCODE = request("uid")
MAIL_TYPE = request("dropType")
EMAIL_ADDR=""
getContactInfo(CONTACT)
getContactInfo(id)
FULL_EMAIL = emailBody(MAIL_TYPE)
if email_addr <> "" then
   Dim MyCDONTSMail2 
   Set MyCDONTSMail2 = CreateObject("CDONTS.NewMail")
   MyCDONTSMail2.From= "jorders@somewhere.com"
   MyCDONTSMail2.To=email_addr
   MyCDONTSMail2.BCC="someone@somewhere.com"
   MyCDONTSMail2.Subject=SUBJECT
   MyCDONTSMail2.BodyFormat=0
   MyCDONTSMail2.MailFormat=0
   MyCDONTSMail2.Body=FULL_EMAIL
   MyCDONTSMail2.Send
   set MyCDONTSMail2=nothing
end if



next


AddToDB()

Doing this, I send 2 emails if I select 2 contacts, it's just that they are to the same person (the first user selected.)

Can anyone help me??? I don't even know what to "google" at this point...

 

[Sheco]

The submitted multi-select data will probably come across as a long string with commas between each selected value. If so you can use the Split function to turn this string into an array and then do a For/Next loop to iterate through the array processing them one at a time. The Ubound method will be used at the top of the loop.