SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Member
    Join Date
    Nov 2001
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP Mail - Form submitted to database - autoresponder email

    Please help.

    I found a script from Matt's Script Archives that allows a form to be submitted, data is entered into an access database, data is also sent via email to a designated recipient, and the submitter receives an autoresponder email. This was BFormMail and is used on Unix servers using sendmail. (I also found a script to do this using jmail.)

    Our website will be hosted on NT Server and they support ASPMail. Does anyone know of a script that I can accomplish this using ASPMail?

    Any advice or direction would be appreciated.

    Thanks.

  2. #2
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Well, you'd send the mail using ASPMail, you'd add a row to the database, and to execute the autoresponder, you'd send a blank email from the recipient's email account.
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  3. #3
    SitePoint Member
    Join Date
    Nov 2001
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry. I thought this was the beginner's corner. You are specific enough - but do you know of such a script that already exists? Thanks.

  4. #4
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Sorry, my fault, I thought this was in the ASP forum (hence my general answer).

    Alright, let me find some information for you and get back to you in the next few minutes
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  5. #5
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Sending mail via ASP Mail (http://www.serverobjects.com):

    Okay, first off, I'm assuming this is coming from a form (<form method="post" action="mailprocess.asp">) and I'll write this up for you step by step

    Here's the example ASPMail code:

    Code:
    Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
    Mailer.FromName   = "Joe’s Widgets Corp."
    Mailer.FromAddress= "Joe@somehost.com"
    Mailer.RemoteHost = "mailhost.localisp.net"
    Mailer.AddRecipient "John Smith", "jsmith@anotherhostname.com"
    Mailer.Subject    = "Great SMTP Product!"
    Mailer.BodyText   = "Dear Stephen" & VbCrLf & "Your widgets order has been processed!"
    if Mailer.SendMail then
      Response.Write "Mail sent..."
    else
      Response.Write "Mail send failure. Error was " & Mailer.Response
    end if
    The way this works is you instantiate (start up) the component:
    Set Mailer = Server.CreateObject("SMTPsvg.Mailer")

    You then have various methods (actions) you can use:
    Mailer being the only one for this COMponent.

    Each method will have various properties (kind of like a phone being your object, and red being it's property). In this case there are quite a few but they are fairly self-explanatory. The only one you will need to be careful with is how you are sending the mail (SMTP server like: smtp1.sympatico.ca or something) on this line:
    Code:
    Mailer.RemoteHost = "mailhost.localisp.net"
    Okay, now that we've got that down, let's look at how to get information from your form into your ASP script (mailprocess.asp) so we can work with the data.

    Code:
    recName=request.form("yourname")
    recAddres=request.form("email")
    This is essentially all of the "dynamic" data we will need for this email. It is the person's name, and their email address. The rest of the email will be essentially the same for every recipient.

    Now we can adjust our Mailing script to use these new variables (recName the recipient's name, and recAddress the recipient's email address):

    Code:
    Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
    Mailer.FromName   = "Your Name"
    Mailer.FromAddress= "Your Email address"
    Mailer.RemoteHost = "smtp1.sympatico.ca"
    Mailer.AddRecipient recName, recAddress
    Mailer.Subject    = "Thank You For Your Order!"
    Mailer.BodyText   = "Dear Stephen" & VbCrLf & "Your widgets order has been processed!"
    if Mailer.SendMail then
      Response.Write "Mail sent..."
    else
      Response.Write "Mail send failure. Error was " & Mailer.Response
    end if
    The only things you will now need to customize and be aware of when sending emails are that you still need to put your Name and email address in the FromName and FromAddress properties as well as changing the BodyText and Subject to your desired settings. Also, just so you konw, "VbCrLf" is like hitting the "enter" key, it just pushes text to the next line and makes it easier for folk to read.

    Okay, so now we've sent our initial email, let's trigger the autoresponder you hopefully have setup:

    Code:
    Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
    Mailer.FromName   = recName
    Mailer.FromAddress= recAddress
    Mailer.RemoteHost = "smtp1.sympatico.ca"
    Mailer.AddRecipient "Autoresponder", "autorespond@mydomain.com"
    Mailer.Subject    = "Request for info"
    Mailer.BodyText   = "I would like more information from your autoresponder."
    if Mailer.SendMail then
      Response.Write "Mail sent..."
    else
      Response.Write "Mail send failure. Error was " & Mailer.Response
    end if
    All I've changed here is that I have inverted the FromName/FromAddress and the information in AddRecipient so that it will send an email to your autoresponder (autoresponder@mydomain.com) with the individual's details as the sender (spoofing an email address). Your autoresponder will then respond with all of the information you have already programmed into it.

    Okay, that's 2 of our three tasks done. Now let's throw some data into our database. I'm going to assume you're using access and that you already know how to connect to a database using a DSN-less connection. If not, just ask, but here's the code anyways:

    Code:
    	DBPath =Server.Mappath("data.mdb")
    	Set con = Server.CreateObject( "ADODB.Connection" )
    	con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBPath
    The above code assumes the database is in the same directory as the script, and that the database is called data.mdb.

    Aright, let's throw the name and email address of our recipient into our database so that we have a record of all of the emails we have sent, and all the people we have sent it to. This assumes the table name is "email".

    Code:
    CON.EXECUTE("INSERT INTO email(name,address) values('" & recName & "','" & recAddress & "')")
    Alright, we could take this one step further and have a status column in the database to say if the email had in fact been sent by modifying our sending code like this. Change:

    Code:
    if Mailer.SendMail then
      Response.Write "Mail sent..."
      set newest=CON.EXECUTE("SELECT max(id) AS maxid FROM email WHERE name='" & recName & "'")
      CON.EXECUTE("UPDATE email SET status='sent' WHERE id=" & newest("maxid"))
      newest.close
      set newest=nothing
    else
      Response.Write "Mail send failure. Error was " & 
      set newest=CON.EXECUTE("SELECT max(id) AS maxid FROM email WHERE name='" & recName & "'")
      CON.EXECUTE("UPDATE email SET status='error' WHERE id=" & newest("maxid"))
      newest.close
      set newest=nothingMailer.Response
    end if
    What I'm doing here is I'm assuming we have already inserted our row into our database (good thing to do first). I am then doing a SELECT max(id). What this does is it gets me the newest id from someone with the name we just entered. Since we have just put it in a nanosecond ago, the record we have just inserted will be returned.

    The reason we need the ID is because we will be updating the status to either sent or unsent (from the default of 'unknown' we will be setting).

    After we have the ID we can run an UPDATE statement as required, close the database object and we're done.

    Alright, here's the complete code for you:

    Code:
    <%
    'CONNECT TO DATABASE
    DBPath =Server.Mappath("data.mdb")
    Set con = Server.CreateObject( "ADODB.Connection" )
    con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBPath
    
    
    'GET INFO FROM FORM
    recName=request.form("yourname")
    recAddres=request.form("email")
    
    
    'INSERT INFO INTO DATABASE
    CON.EXECUTE("INSERT INTO email(name,address,status) values('" & recName & "','" & recAddress & "','unknown')")
    
    
    'SEND EMAIL TO RECIPIENT
    Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
    Mailer.FromName	 = "Your Name"
    Mailer.FromAddress= "Your Email address"
    Mailer.RemoteHost = "smtp1.sympatico.ca"
    Mailer.AddRecipient recName, recAddress
    Mailer.Subject = "Thank You For Your Order!"
    Mailer.BodyText	= "Dear Stephen" & VbCrLf & "Your widgets order has been processed!"
    'ERROR CHECK AND UPDATE STATUS OF RECORD WITH STATUS OF MAILSEND
    if Mailer.SendMail then
    	Response.Write "Mail sent..."
    	set newest=CON.EXECUTE("SELECT max(id) AS maxid FROM email WHERE name='" & recName & "'")
    	CON.EXECUTE("UPDATE email SET status='sent' WHERE id=" & newest("maxid"))
    	newest.close
    	set newest=nothing
    else
    	Response.Write "Mail send failure. Error was " & Mailer.Response
    	set newest=CON.EXECUTE("SELECT max(id) AS maxid FROM email WHERE name='" & recName & "'")
    	CON.EXECUTE("UPDATE email SET status='error' WHERE id=" & newest("maxid"))
    	newest.close
    	set newest=nothing
    end if
    
    
    'TRIGGER AUTORESPONDER
    Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
    Mailer.FromName = recName
    Mailer.FromAddress= recAddress
    Mailer.RemoteHost = "smtp1.sympatico.ca"
    Mailer.AddRecipient "Autoresponder", "autorespond@mydomain.com"
    Mailer.Subject = "Request for info"
    Mailer.BodyText = "I would like more information from your autoresponder."
    if Mailer.SendMail then
    	Response.Write "Mail sent..."
    else
    	Response.Write "Mail send failure. Error was " & Mailer.Response
    end if
    
    %>
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  6. #6
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hope that helps
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  7. #7
    SitePoint Member
    Join Date
    Nov 2001
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THANK YOU!

    Let me digest this and I will let you know how this works.

  8. #8
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Not a problem, I don't often wander into the beginner's threads, but when I do I at least try to be helpful, so I hope my second reply was better then teh first
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  9. #9
    SitePoint Member
    Join Date
    Nov 2001
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok Jeremy -- I've been trying to tailor it to my needs.

    Again -- here is specifically, my objective. This is a form for people to register for an upcoming conference. Upon submit, their information (data) will automatically go into our database (conference.mdb), a copy of the data will be sent via-email to our admin asst, the person registering for the conference will also receive an automated response thanking them for their registration.

    If it's ok -- I would like to show you the code to look at.

    How do I list the fields that the registrant completed into the email that I am sending to the admin assist? Response.write??

  10. #10
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    This isn't chas, is it?

    This is exactly what I've just finished (chas being my workmate).

    I can't so much give you the exact code as show you how to go about it. Alternately I could sell it to you *L*

    Anyways, let's see some code and where you're stuck
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  11. #11
    SitePoint Member
    Join Date
    Nov 2001
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to dissapoint you. I'm thinking Chas is a male --that I'm not.

    (BTW -- getting ready to make that million dollar pitch on Friday)

    Here's my code attached.

  12. #12
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Fair enough, good luck on the pitch!

    ...

    :waits for code:
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  13. #13
    SitePoint Member
    Join Date
    Nov 2001
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code
    Last edited by shossy; Nov 7, 2001 at 11:31.

  14. #14
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Looks good to me... What's the problem?
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •