Generating unique link to send via email

OK - I have bitten off more than I can chew, and I hoping for some very specific help here.  I have designed simple .asp pages before that just dump into Access databases.  Now though, things are getting more complicated and I don't know where to even start.  The scenario is: I need to create something where students can sign up for a class, then when they submit their form the email client (Lotus Notes 5.0) is opened with a unique link that point back to the record that they just entered (there is an autonumber field in Access as the primary key, but that can be changed if necessary).  They will then select their manager's email from the Lotus Address book and send it off.  Their manager will receive the email, click on the link, the database results for that particular entry will display, then the manager will update the record by clicking on an approval button.  The .asp page is called TrainingSignUp.asp, the database is TrainingSignUp.mdb, the Primary Key is ID (autonumber in Access DB).

I have generated the appropriate forms and the database, but I don't know what to do On Submit in order to get it to retrieve the unique ID of the record that was just submitted in order to create the specific hyperlink to be emailed to the supervisor.  I also don't know how to get it to just open Lotus Notes instead of automatically sending the email (they won't know their supervisor's email off hand).  

I recognize that this is a loaded question, and that it is waaaaay beyond my skills, but I am hoping an expert will take pity on me and show me what to do!!
LVL 1
bcadAsked:
Who is Participating?
 
peterxlaneCommented:
Here is the basic idea of the first part.  I actually even simplified it even more by simply storing the manager's email right in your existing table, but the ideal solution would have a separate manager table with the associated email addresses.  

<% @Language = VBScript %>
<html>
<head>
<title></title>
</head>
<body>

<form method="post">
      
<table border="1" cellpadding="5" cellspacing="0" align="center">
      <tr>
            <td>Your Name:</td>
            <td><input type="text" name="username" size="30" value="Peter" /></td>
      </tr>
      <tr>
            <td>Your Manager:</td>
            <td>
                  <select name="manager">
                        <option value="manager1@yourcompany.com">Manager One</option>
                        <option value="manager2@yourcompany.com">Manager Two</option>
                        <option value="manager3@yourcompany.com">Manager Three</option>
                  </select>
            </td>
      </tr>
      <tr>
            <td colspan="2" align="center"><input type="submit" value="Submit" name="cmdSubmit" /></td>
      </tr>            
</table>

</form>

<%
If Request.Form <> "" Then
      strUserName = Request.Form("username")
      strManagerEmail = Request.Form("manager")
      strSQL = "INSERT INTO tblYourTable (UserName, strManagerEmail) VALUES ('" & strUserName & "', '" & strManagerEmail & "')"
      Response.Write "<b>Sample of SQL you will need to run: </b>" & strSQL & "<br />"
      'You need to put your database code here to insert form results into database      
      strEmailToSend = "There has been a class sign up request.<br /><a href=""http://yourwebsite.asp?email=" & strManagerEmail & """>Click here to view approval requests</a>"
      strFromAddress = "you@yourcompany.com"
      strSubject = "your subject"
      Call SendMessage(strManagerEmail, strFromAddress, strSubject, strEmailToSend)      
      Response.Write "<h2>Your request has been submitted</h2>"      
End If


Sub SendMessage(strTo, strFrom, strSubject, strBody)
      Set oCDO = Server.CreateObject("CDO.Message")
            With oCDO
                  .To       = strTo
                  .From     = strFrom
                  .Subject  = strSubject
                  .HtmlBody = strBody
                  .Send
            End With
      Set oCDO = Nothing
End Sub
%>


</body>
</html>


Your second page would just read the value passed in the querystring to display only the items in the database that have a matching email address. The manager would then just approve individual items by clicking on them or something like that.  If you are storing the user's email in the database as well, then you could make it so that an email confirmation is sent to the user when the request is approved or denied, by using similar code as above to create the email...

0
 
WMIFCommented:
here is an article about getting the last id inserted.
http://www.aspfree.com/c/a/ASP-Code/Retrieving-ID-field-after-inserting-a-record-into-your-Microsoft-Access-Database/

as for opening any programs on the client computer, asp has no control over that.  you could prompt the user for their managers email and let them look that up on their own.
0
 
peterxlaneCommented:
This would be my advice.

1. Create a manager table that is used to display managers for the user to choose from.  This table could store the manager's email addresses and a unique key for each manager.

2. Add a manager foreign key to your existing table that will store the user's associated manager key.

3. Use CDO to send the email to the selected manager and pass the manager key in the query string.  This eliminates trying to get the user to send an email with Lotus.  They don't even need to know that an email is being sent for that matter.

4. The email sent to the manager will contain a link to a page with ALL of their unapproved requests.  This eliminates the need to even determine the last created ID, and insures that the manager doesn't miss any approvals.  By having the manager foreign key on your table, you can run a query based on the manager key.

If I have time tonight, I will put together a **rough** example.  I am guessing that you may need it based on the number of a's in the phrase:  "waaaaay beyond my skills"

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
bcadAuthor Commented:
Thanks for the help and the example (yes, the multiple aaaa's were definitely a top off as to my skill level!!).  I am going to work on it this morning and get back to you with any questions...
0
 
bcadAuthor Commented:
Fabulous!!  What a relief to have something working!  Thanks very much for the sample code peterxlane, that was just what I needed.  I may have other questions along the way that arise out of this, but I will post them seperately.

Insert big sigh of relief here...

Thanks again.
0
 
peterxlaneCommented:
glad I could help
0
 
bcadAuthor Commented:
Peterxlane;

I have a follow up question about a slightly different way to go about this...should a post a seperate question???

Thanks...
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.