Generating unique link to send via email

Posted on 2006-04-17
Last Modified: 2011-09-20
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!!
Question by:bcad
    LVL 22

    Expert Comment

    here is an article about getting the last id inserted.

    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.
    LVL 12

    Expert Comment

    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"

    LVL 12

    Accepted Solution

    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 %>

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


    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 = ""
          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
                End With
          Set oCDO = Nothing
    End Sub


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

    LVL 1

    Author Comment

    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...
    LVL 1

    Author Comment

    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.
    LVL 12

    Expert Comment

    glad I could help
    LVL 1

    Author Comment


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


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
    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…
    This video discusses moving either the default database or any database to a new volume.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    732 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

    22 Experts available now in Live!

    Get 1:1 Help Now