ASP Mail - Form submitted to database - autoresponder email

[shossy]

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.

[Jeremy W.]

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.

[shossy]

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.

[Jeremy W.]

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

[Jeremy W.]

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:

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:

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.

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):

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:

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:

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

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:

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:

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

%>

[Jeremy W.]

Hope that helps

[shossy]

THANK YOU!

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

[Jeremy W.]

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

[shossy]

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

[Jeremy W.]

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

[shossy]

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.

[Jeremy W.]

Fair enough, good luck on the pitch!

...

:waits for code:

[shossy]

Code

[Jeremy W.]

Looks good to me... What's the problem?