[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Send email upon processing multiple records

Posted on 2011-02-28
4
Medium Priority
?
440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 400 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 1600 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

656 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