Setting SQL Email, using ASP to email using database

**bakpao** · Aug 27 '07, 10:36 PM

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

The mail sending should be done in Order_Submit.as p. 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** · Aug 28 '07, 12:04 AM

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*

Originally posted by bakpao

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

The mail sending should be done in Order_Submit.as p. 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** · Aug 28 '07, 04:28 AM

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:

Code:

telnet whateveryourhostnameis 25

For example:

Code:

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** · Aug 28 '07, 06:47 PM

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** · Aug 28 '07, 08:30 PM

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.

Code:

     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 = "admin@somedomain.com"
     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** · Aug 28 '07, 08:37 PM

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** · Aug 28 '07, 09:59 PM

Code:

<%
Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
Mailer.FromName = "website"
Mailer.FromAddress= "user@yourdomain.com"
Mailer.RemoteHost = "localhost"
Mailer.AddRecipient "Name", "name@yourdomain.com"
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.serverobjec ts.com/products.htm
3) Install it and follow the installation/configuration instructions listed at this address - http://www.serverobjec ts.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** · Aug 28 '07, 10:18 PM

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=CreateOb ject("CDO.Messa ge")
myMail.Subject= "Sending email with CDO"
myMail.From="my mail@mydomain.c om"
myMail.To="v.w@ myemail.com"
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** · Aug 28 '07, 10:32 PM

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.

[CODE=asp]
<%
if myMail.Send Then
response.write "the mail was sent"
else
response.write "the mail was not sent"
end if
%>
[/CODE]

**bakpao** · Aug 28 '07, 11:26 PM

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

[code=asp]
Set objMail = CreateObject("C DO.Message")

objMail.From = strFrom
objMail.To = strTo
objMail.CC = strCC
objMail.Subject = strSubject
objMail.TextBod y = strBody
objMail.MimeFor matted = True

' ADD THESE
objMail.Configu ration.Fields.I tem("http://schemas.microso ft.com/cdo/configuration/sendusing") = 2
objMail.Configu ration.Fields.I tem("http://schemas.microso ft.com/cdo/configuration/smtpserver") = "smtp.myserver. com"
objMail.Configu ration.Fields.I tem("http://schemas.microso ft.com/cdo/configuration/smtpserverport" ) = 25
objMail.Configu ration.Fields.U pdate

objMail.Send

Set objMail = Nothing
[/code]

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

**vmethod** · Aug 29 '07, 08:41 PM

Mark,

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

Originally posted by bakpao

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

[code=asp]
Set objMail = CreateObject("C DO.Message")

objMail.From = strFrom
objMail.To = strTo
objMail.CC = strCC
objMail.Subject = strSubject
objMail.TextBod y = strBody
objMail.MimeFor matted = True

' ADD THESE
objMail.Configu ration.Fields.I tem("http://schemas.microso ft.com/cdo/configuration/sendusing") = 2
objMail.Configu ration.Fields.I tem("http://schemas.microso ft.com/cdo/configuration/smtpserver") = "smtp.myserver. com"
objMail.Configu ration.Fields.I tem("http://schemas.microso ft.com/cdo/configuration/smtpserverport" ) = 25
objMail.Configu ration.Fields.U pdate

objMail.Send

Set objMail = Nothing
[/code]

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

**bakpao** · Aug 29 '07, 10:53 PM

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.o rg/smtp_frames.htm

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

**vmethod** · Aug 30 '07, 08:27 PM

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.

Originally posted by 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.o rg/smtp_frames.htm

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

Setting SQL Email, using ASP to email using database

Setting SQL Email, using ASP to email using database

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment