Solved

Send email upon processing multiple records

Posted on 2011-02-28
4
431 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:baxtalo
  • 2
4 Comments
 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 100 total points
ID: 34997050
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
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34997056
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
 
LVL 2

Accepted Solution

by:
pforeman earned 400 total points
ID: 34997147
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
 

Author Closing Comment

by:baxtalo
ID: 34997667
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

773 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