Send email upon processing multiple records

The page below updates multiple records in my Access database upon submission. Each record in the database has an email field: EmpEmail. I would like an email sent out to each recipient upon submission.
Right now when I submit the form I get an error message, saying that at least one recipient is needed. How could I modify my page so that the email gets sent out to each email address in the processed records?
Please take a look at my pages below. Thank you for your help.
<%
If Request.ServerVariables("LOGON_USER") = "" Then
Response.Status = "401 Access Denied"
Response.End
End If
%>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("/EWR/DB/Autos.mdb"))

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT * FROM EWRAuto_Table", conn
%>

<html>
<head>
<title>All Requests</title>
<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
function checkLikeMe($) {
    var x = document.getElementsByName('ID');
    for (var y = 0; y < x.length; y++)
        x[y].checked = $;
}

// End -->
</script>

</head>
<body>

<form name="editor" method="post" action="Process.asp">

<table border="1">
<tr>
<th>Employee Name</th>
<th>Emp.#</th>
<th>Auto Status</th>
<td><input type="checkbox" onClick="checkLikeMe(this.checked)"></td>
</tr>
<%
if not (rs.EOF or rs.BOF) then rs.movefirst
do until rs.EOF
%>
<tr>
<td><%=rs("EmpName")%></td>
<td><%=rs("Emp_Id")%></td>
<td><%=rs("AutoStatus")%></td>
<td><input type="checkbox" name="ID" value="<%=rs("ID") %>"></td>
</tr>
<%
rs.MoveNext
loop
rs.Close
conn.Close
%>
</table>

<br /><br />
<select name="AutoStatus">
<option value="Pending">Select Status</option>
<option value="Disapproved">Disapproved</option>
<option value="Approved">Approved</option>
</select>

<input type="hidden" name="Pipa" value="&#252">
<input name="ApprovedBy" type="hidden" id="ApprovedBy" value="<%=Request.ServerVariables("LOGON_USER")%>" />
<input type="Submit" name="Submit" value="Process" />
</center>
</form>

</body>
</html>





AND SEE BELOW THE Process.asp



<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("/EWR/DB/Autos.mdb") & ";"
objConn.Open

myId = Request.form("ID")

myAutoStatus = request.Form("AutoStatus")
myPipa = request.Form("Pipa")
myApprovedBy = request.Form("ApprovedBy")


StrSQL = "Update EWRAuto_Table SET AutoStatus= '" & myAutoStatus & "', Pipa= '" & myPipa & "', ApprovedBy= '" & myApprovedBy & "' Where ID in ("&myId&")"

objConn.Execute StrSQL

'///////////////// send cdo email.

Set myMail=CreateObject("CDO.Message")
myMail.Subject= Request.form("AutoStatus") & " - Auto Request" ' out your own subject here for the email
myMail.From= "AUTO@mycompany.com"
myMail.To= Request.form("EmpEmail")
myMail.HTMLBody = "<table width='650' align='center'><tr><td align='center'>Your AUTO request for " & Request.form("DayRequested") & Request.form("WeekRequested") & Request.form("MonthRequested") & " has been " & Request.form("AutoStatus") & "<hr style='height:1px; color:000080; width:450px;' /></td></tr></table><br /><center><a href='http://nfstvmwebaw03/EWR/Autos/ViewData1.asp?ID=" & Request.form("ID") & "'>View Request</a></center>"
myMail.Send
set myMail=nothing

'///////////////// send cdo email. end

'Redirect to the UPDATED page
Response.Redirect "All.asp"

%>

Open in new window

baxtaloAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pforemanConnect With a Mentor Commented:
Hi,

Currently it's erroring because your requesting Request.form("EmpEmail") from the form rather than the table.

And as your not passing it from the previous page it's always going to be blank.
If there is only one email address per submited form then pass it using a hidden field from the preious page. Then there is no changes to the process.asp page.

Regards
Pete
0
 
Simon BallConnect With a Mentor Commented:
do you need an if around lines 103 to 109 which only sets up the email if there is a valid empemial...

if (Request.form("EmpEmail") <> null and Request.form("EmpEmail") <> "") then...
0
 
Simon BallCommented:
also outside of that - how do you get a list of the empemail for every selected record?

pseudocode:
for each record
if empmail is not null
send email
end if
next record.

0
 
baxtaloAuthor Commented:
Thank you very much. It worked with the hidden field. I haven't played around with Sudonim's suggestion, but I will consider that too.
I'm really grateful four your quick responses.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.