Solved

Send email upon processing multiple records

Posted on 2011-02-28
4
428 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

8 Experts available now in Live!

Get 1:1 Help Now