Extract cell value from Excel and import data via email

[doraima29]

Hi,

I have 2 questions on how to get this code working from a form I am building.
Notes-- I only have Classic ASP running on all of the servers that I am working on. My other option is that I have CGI and Perl running too.

First, I have found an easy way to extract data from excel spreadsheet. However, I'm still working on how to read it line by line, and verify if last data has been used already.

Second, is how to connect the data I want to be shown on a HTML email. I need some explanations how this is going to work, so I can modify a bit.

Here is the code for extracting data from excel:

<%
' Set Connection Params
Set oConn = Server.CreateObject("ADODB.connection")
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};"&_
"DriverId=790;" &_
"DBQ=z:\educator\codes.xls;" &_
"DefaultDir = z:\educator\"

Set RS=Server.CreateObject("ADODB.recordset")

' Write the SQL Query
RS.open "SELECT * FROM dsctCodes", oConn

do until RS.EOF
Response.Write ( RS("dsctCodes") & " -- " & RS("EMAIL") & "")
RS.movenext
Loop

'Close the recordset/connection

RS.Close
oConn.Close
Set RS = Nothing
%>

Here is the send_mail code:

<%@ Language=VBScript %>

<%
Option Explicit
%>

<%
' -----------------------------------------------------
' CDONTS Email send script
' © [url]http://www.designplace.org/[/url]
' Comments must remain intact for re-use of this code
' -----------------------------------------------------

dim strName, strEmailOne, strEmailTwo, strMessage, optSuggestions, strEmailTo

    strName = Request.Form("name") ' holds inputted name
    'strEmailOne = Request.Form("emailOne") ' holds inputted email address
    strEmailTwo = Request.Form("emailTwo") ' holds inputted email address
    strMessage = Request.Form("message") ' holds inputted message
    optSuggestions = Request.Form("suggestions").Item ' drop down list selection

' -- check all fields for empty values --
' -- remove and add new as required --

if strMessage = "" then
    Response.Redirect "suggestion_box.asp?action=err4"
end if  

' if strName = "" then
    ' Response.Redirect "suggestion_box.asp?action=err1"
        ' else if strEmailOne = "" then
         ' Response.Redirect "suggestion_box.asp?action=err2"
          ' else if strEmailTwo = "" then
             'Response.Redirect "suggestion_box.asp?action=err3"
                'else if strMessage = "" then
                    'Response.Redirect "suggestion_box.asp?action=err4"
                                'else if optSuggestions = "" then
                                    'Response.Redirect "suggestion_box.asp?action=err5"
                                'end if
                        'end if
                'end if
        'end if
'end if

' -- start determine correct send to address based on suggestion type --

Select Case optSuggestions

Case "Web Enhancements"
    strEmailTo = "[e-mail]"    

Case "Application Enhancements"
    strEmailTo = "[e-mail];[e-mail]"  

Case "New Product Suggestions"
    strEmailTo = "[e-mail];[e-mail]"  

Case "Workplace Suggestions"
    strEmailTo = "[e-mail];[e-mail]"  

Case "Employee Recognition"
    strEmailTo = "[e-mail];[e-mail]"  

Case "Cultural Events"
    strEmailTo = "[e-mail];[e-mail]"  

Case "Other"
    strEmailTo = "person3@optIT;[e-mail]"  

Case Else
    strEmailTo = "[e-mail]"

End Select

' -- end determine correct send to address based on suggestion type --

' -- begin email send process --

dim objMail

Set objMail = Server.CreateObject("CDO.Message")
'Set objMail = CreateObject("CDONTS.NewMail")

    ' -- email variables --
    objMail.From = Trim(""[e-mail];[e-mail]")
    objMail.To = Trim(strEmailTo)
    objMail.Subject = "Educators Program"
    'objMail.BodyFormat = "0" ' HTML format
    objMail.TextBody = "Your Discount Code from Educators Program:"   & Trim(dsctCodes) & vbCrLf _
                   & vbCrLf _
                   & "Question1: " & Trim(optSuggestions) & vbCrLf _
                   & vbCrLf _
                   & "Message: " & Trim(strMessage)

    ' -- send the email --             
    objMail.Send()

' -- clean up object
Set objMail = Nothing

' -- execute confirmation page
Response.Redirect "thankyou.asp"
%>