Setting SQL Email, using ASP to email using database

[vmethod]

The low down:
Local Web Server on Windows 2003
Local SQL Server on Windows 2003
Hosting dynamic website tied to inhouse Access Application

Ok, basically, how it is set up, people can login to our website and enter data (insert record), on our end, we have an Access application where we can play with the data that was entered via the website. Currently, we do not have either server set up as a Mail server.

What we need to be able to do:
When a customer enters data on our website, their supervisor, and about 2-3 other people related to the transaction need to be emailed to be notified that an order was submitted. So how do I code that? On the page with the Insert Record? OR after Insert Record redirect them to another page that sends the mail out?

Also, it needs to dynamically email people, so I can't just put in [e-mail] for a value, I'd need to put in something like <%=(megalist.Fields.Item("email").Value)%>

Which server do I enable the mail?

I was reading about SQL Mail etc, which would be good since we do mass emails to clients weekly, but I have no idea how to set that up and I look crossed eyed at any tutorial.

Do I want to set up theSQL server to also be a Mail Server that way we can use the SQL database to email as well as data entered from the website? But then again, the website points to the Web Server which pulls data off the SQL server (so unless the Web Server is a mail server, nothing will be sent, am I right?)

*sigh*

I know very little about SQL and I'm being asked to impliment this and I am 100% confused. I'm a graphics artist not a programmer! LOL

Thanks in advance to anyone who can/will help me.

[bakpao]

Assuming you have the following pages:
Order_Form.asp = where user enters the items they want to order
Order_Submit.asp = where you are updating the database

The mail sending should be done in Order_Submit.asp. There is no need to forward the user to another special page which sends email.

To send email, try searching this site or Google for the terms: ASP, CDO and mail sending.

For the server, you need to have a dedicated SMTP server or enable your Windows 2003 server to handle email. You don't send the email from the SQL Server application.

If you think it's too hard, you can just let your supervisor know so he can help you. :)

[vmethod]

Actually, one the page where they enter data, it's just a single page set up with an Insert Record code. And that's what I meant about the send email from a second page (I think we are thinking the same, just didn't come out the same). I guess in other words what I am thinking, is do Insert Record on page1.asp then have it go to page2.asp and it doesn't necessarily need to carry over the information, but have it email from page2.asp using a Recordset (so I can grab the supervisor, client results manager, real estate agents, etc) from that to use in the code.

Ok, so set up the SMTP on the web server then? I actually looked but did not see a SMTP Service. There was a virtual mail server under the server configuration, but that was all I saw...

this is going to be a fun adventure... *sarcastic*

Assuming you have the following pages:
Order_Form.asp = where user enters the items they want to order
Order_Submit.asp = where you are updating the database

The mail sending should be done in Order_Submit.asp. There is no need to forward the user to another special page which sends email.

To send email, try searching this site or Google for the terms: ASP, CDO and mail sending.

For the server, you need to have a dedicated SMTP server or enable your Windows 2003 server to handle email. You don't send the email from the SQL Server application.

If you think it's too hard, you can just let your supervisor know so he can help you. :)

[bakpao]

I think it's better if you send the email straight after the insert instead of forwarding it to another page. But that's just my preference.

I never set up the SMTP myself but if you run IIS you may be able to find "Default SMTP Virtual Server" under the computer/server. It could be the starting point for you.

You can test whether or not your server is SMTP capable by running this in command prompt:

telnet whateveryourhostnameis 25
 

For example:

telnet smtp.gmail.com 25
 

If it works, it will show you some text about the SMTP server. Just type "quit" to exit.

You can then plug in that server name in your CDO code to be able to send email.

[vmethod]

That's just the thing, I don't know how to send it on the same page as the insert record page :(

I'm still researching as much as possible, but he really wants this ASaP

On the webserver, it has a Default SMTP Virtual Server set up but it is set to the default domain which is our local domain. Do I need to add another domain that reflects our domain name for the actual web site?

[markrawlingson]

You'll need an API installed to handle this. The company I work for currently uses ASPMail. I would suggest your first course of action to be asking a co-worker or your supervisor/superior if there is a mailing object installed on the server and if so - what is it?

If it happens to be ASPMail - Below is the code that we use (for privacy issues i've modified some of the content of course) However if it's not ASPMail - let us know what it is - myself, or someone else, will point you in the right direction.

 
     sSQL = "SELECT STATEMENT"            
     Set rsEmail = CreateObject("ADODB.RecordSet")
     rsEmail.Open sSQL, Application.StaticObjects("oConnSQL"), adOpenReadOnly, adLockOptimistic, adCmdText
     Set oMailer = CreateObject("SMTPsvg.Mailer")
     oMailer.RemoteHost = "whatever.something.com" <-- your mail host
     oMailer.FromAddress = "[e-mail]"
     oMailer.FromName = "Administrator"
     oMailer.AddRecipient Request.Form("sFirstName") & " " & Request.Form("sLastName") , Request.Form("sEmailAddress")
     If NOT IsNull( rsEmail("sCCEmail") ) Then
          oMailer.AddCC "Registrar",rsEmail("sCCEmail")
     End If
     If NOT IsNull( rsEmail("sBCCEmail") ) Then
          oMailer.AddBCC "Registrar",rsEmail("sBCCEmail")
     End If
     oMailer.Subject = rsEmail("sSubject")
     oMailer.ContentType = "text/html"
     oMailer.BodyText = rsEmail("sHTML")
     oMailer.SendMail
     Set oMailer = Nothing
     rsEmail.Close
     Set rsEmail = nothing
 

Where and when you send the email is entirely your decision and it completely depends on the scenario. For this particular scenario you have to consider that you wouldn't want to send an email to anyone until you know that 1) the user has filled in the form and submitted it... 2) the form contained no errors and the submission was successful... and 3) your database accepted the information and there is now a record in your database pertaining to the individual who filled out the form.

So Because of that reason, I wouldn't do it directly after your insert statement

rs.AddNew
rs("field1") = whatever
rs("field1") = whatever
rs("field1") = whatever
rs("field1") = whatever
rs("field1") = whatever
rs.Update

'place mailing code here, directly after the insert statement.

[vmethod]

Unfortunately, I do it all here. I'm "IT" (hehe sorry gotta have some humor). There is no mail set up on either server. I attempted in setting up smarthost on the virtual smtp server on the web server. Not sure if I did it correctly or not, I left the outbound to default anonymous authentication, but I entered the SMTP mail server address (through godaddy) under Advanced options. Again, not sure if that will work.

But really, I'm expected to do everything, server, network, pc, graphics, web design lol. It's a bit over whelming at times, and right now I think my head is in love with my desk because it's been pounding it all day. (Did not intentionally mean for that to be an innuendo LOL)

But I do want it to send the email right after the user clicks Update (or Insert) otherwise I don't see how else it would be triggered to pull the information they entered. (I'm guessing a Request.Form of some sort is going to play a role in this)

Thank you all for all your help so far, I'm sure I must be a pain knowing nothing about ASP and mixing it with everything. But I do what I can.

[markrawlingson]

<%
Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
Mailer.FromName = "website"
Mailer.FromAddress= "[e-mail]"
Mailer.RemoteHost = "localhost"
Mailer.AddRecipient "Name", "[e-mail]"
Mailer.AddExtraHeader "X-MimeOLE:Produced yourdomain.com"
Mailer.Subject = "Form Submission"
Mailer.BodyText = "Enter the body of the message here."
if Mailer.SendMail then
  Response.Write "Form information submitted..."
else
  Response.Write "Mail send failure. Error was " & Mailer.Response
end if
set Mailer = Nothing
%>
 

Do this for me...

1) Make sure SMTP is setup on your server properly.
2) Download the free version of ASPMail from Server Objects Inc. http://www.serverobjects.com/products.htm
3) Install it and follow the installation/configuration instructions listed at this address - http://www.serverobjects.com/comp/Aspmail4.htm
4) Create a new file and paste the above code into it.
5) Clean up the code, replace the values with your own email address, name, etc
6) save the file as something like mailer.asp
7) run the file and see if it works - if it doesn't it should spit out some sort of error at you. Show that error if you get one.

I've never used "localhost" as the remote host, but it should work. I don't see why it wouldn't!

[vmethod]

Ok I ended up with this error:

Mail send failure. Error was This evaluation component has expired.

obviously because the trial expired... hmmm...

I attempted to even use the code:

<%
Set myMail=CreateObject("CDO.Message")
myMail.Subject="Sending email with CDO"
myMail.From="[e-mail]"
myMail.To="[e-mail]"
myMail.TextBody="This is a message."
myMail.Send
set myMail=nothing
%>

knowing I attempt to set up the smarthost for the virtual smtp server... it didn't give me an error, but I also did not receive an email. Suggestions?

[markrawlingson]

Boo. Sorry i didn't realize they'd limited their trial version like that.

Hmm, so you tried CDOSYS/CDONTS - that should work.

If you didn't get an error, the email probably at least tried to get sent.

You didn't use that EXACT code above did you? Because you'll obviously have to change the parameters to a real email address.

Try putting an if/else statement in there to see if it does anything... it can often help to troubleshoot but it looks like you're on the right path.

Try putting this in place of the myMail.Send portion of the code you supplied above.

<%
if myMail.Send Then
response.write "the mail was sent"
else
response.write "the mail was not sent"
end if
%>
 

[bakpao]

You need to specify your SMTP server or it won't work.

Set objMail = CreateObject("CDO.Message") 
 
objMail.From = strFrom
objMail.To = strTo
objMail.CC = strCC
objMail.Subject = strSubject
objMail.TextBody = strBody
objMail.MimeFormatted = True
 
' ADD THESE
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.myserver.com"
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMail.Configuration.Fields.Update
 
objMail.Send
 
Set objMail = Nothing
 

You may want to read this too: http://www.paulsadowski.com/WSH/cdo.htm

[vmethod]

Mark,

I got the message that it did not send, and pao, that didn't work either :/

You need to specify your SMTP server or it won't work.

Set objMail = CreateObject("CDO.Message") 
 
objMail.From = strFrom
objMail.To = strTo
objMail.CC = strCC
objMail.Subject = strSubject
objMail.TextBody = strBody
objMail.MimeFormatted = True
 
' ADD THESE
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.myserver.com"
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMail.Configuration.Fields.Update
 
objMail.Send
 
Set objMail = Nothing
 

You may want to read this too: http://www.paulsadowski.com/WSH/cdo.htm

[bakpao]

Have you set up your SMTP server? Have you confirmed that it is working? Here's a tutorial on how to test it by sending the email manually: http://exchange.mvps.org/smtp_frames.htm

If you SMTP server is not working, you cannot send email!

[vmethod]

Ok, tested it with telnet. I can get in, says the sender is ok, but it then tells me that it can not relay for the email... so apparently I didn't set that up correctly... I'll try to get that working first.

Have you set up your SMTP server? Have you confirmed that it is working? Here's a tutorial on how to test it by sending the email manually: http://exchange.mvps.org/smtp_frames.htm

If you SMTP server is not working, you cannot send email!