By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,289 Members | 3,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,289 IT Pros & Developers. It's quick & easy.

Setting SQL Email, using ASP to email using database

P: 9
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 john@doe.com 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.
Aug 27 '07 #1
Share this Question
Share on Google+
13 Replies


P: 21
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. :)
Aug 27 '07 #2

P: 9
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. :)
Aug 28 '07 #3

P: 21
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:
Expand|Select|Wrap|Line Numbers
  1. telnet whateveryourhostnameis 25
  2.  
For example:
Expand|Select|Wrap|Line Numbers
  1. telnet smtp.gmail.com 25
  2.  
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.
Aug 28 '07 #4

P: 9
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?
Aug 28 '07 #5

markrawlingson
Expert 100+
P: 346
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.      sSQL = "SELECT STATEMENT"            
  3.      Set rsEmail = CreateObject("ADODB.RecordSet")
  4.      rsEmail.Open sSQL, Application.StaticObjects("oConnSQL"), adOpenReadOnly, adLockOptimistic, adCmdText
  5.      Set oMailer = CreateObject("SMTPsvg.Mailer")
  6.      oMailer.RemoteHost = "whatever.something.com" <-- your mail host
  7.      oMailer.FromAddress = "admin@somedomain.com"
  8.      oMailer.FromName = "Administrator"
  9.      oMailer.AddRecipient Request.Form("sFirstName") & " " & Request.Form("sLastName") , Request.Form("sEmailAddress")
  10.      If NOT IsNull( rsEmail("sCCEmail") ) Then
  11.           oMailer.AddCC "Registrar",rsEmail("sCCEmail")
  12.      End If
  13.      If NOT IsNull( rsEmail("sBCCEmail") ) Then
  14.           oMailer.AddBCC "Registrar",rsEmail("sBCCEmail")
  15.      End If
  16.      oMailer.Subject = rsEmail("sSubject")
  17.      oMailer.ContentType = "text/html"
  18.      oMailer.BodyText = rsEmail("sHTML")
  19.      oMailer.SendMail
  20.      Set oMailer = Nothing
  21.      rsEmail.Close
  22.      Set rsEmail = nothing
  23.  
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.
Aug 28 '07 #6

P: 9
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.
Aug 28 '07 #7

markrawlingson
Expert 100+
P: 346
Expand|Select|Wrap|Line Numbers
  1. <%
  2. Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
  3. Mailer.FromName = "website"
  4. Mailer.FromAddress= "user@yourdomain.com"
  5. Mailer.RemoteHost = "localhost"
  6. Mailer.AddRecipient "Name", "name@yourdomain.com"
  7. Mailer.AddExtraHeader "X-MimeOLE:Produced yourdomain.com"
  8. Mailer.Subject = "Form Submission"
  9. Mailer.BodyText = "Enter the body of the message here."
  10. if Mailer.SendMail then
  11.   Response.Write "Form information submitted..."
  12. else
  13.   Response.Write "Mail send failure. Error was " & Mailer.Response
  14. end if
  15. set Mailer = Nothing
  16. %>
  17.  
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!
Aug 28 '07 #8

P: 9
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="mymail@mydomain.com"
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?
Aug 28 '07 #9

markrawlingson
Expert 100+
P: 346
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.

Expand|Select|Wrap|Line Numbers
  1. <%
  2. if myMail.Send Then
  3. response.write "the mail was sent"
  4. else
  5. response.write "the mail was not sent"
  6. end if
  7. %>
  8.  
Aug 28 '07 #10

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

Expand|Select|Wrap|Line Numbers
  1. Set objMail = CreateObject("CDO.Message") 
  2.  
  3. objMail.From = strFrom
  4. objMail.To = strTo
  5. objMail.CC = strCC
  6. objMail.Subject = strSubject
  7. objMail.TextBody = strBody
  8. objMail.MimeFormatted = True
  9.  
  10. ' ADD THESE
  11. objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  12. objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.myserver.com"
  13. objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  14. objMail.Configuration.Fields.Update
  15.  
  16. objMail.Send
  17.  
  18. Set objMail = Nothing
  19.  
You may want to read this too: http://www.paulsadowski.com/WSH/cdo.htm
Aug 28 '07 #11

P: 9
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.

Expand|Select|Wrap|Line Numbers
  1. Set objMail = CreateObject("CDO.Message") 
  2.  
  3. objMail.From = strFrom
  4. objMail.To = strTo
  5. objMail.CC = strCC
  6. objMail.Subject = strSubject
  7. objMail.TextBody = strBody
  8. objMail.MimeFormatted = True
  9.  
  10. ' ADD THESE
  11. objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  12. objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.myserver.com"
  13. objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  14. objMail.Configuration.Fields.Update
  15.  
  16. objMail.Send
  17.  
  18. Set objMail = Nothing
  19.  
You may want to read this too: http://www.paulsadowski.com/WSH/cdo.htm
Aug 29 '07 #12

P: 21
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!
Aug 29 '07 #13

P: 9
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!
Aug 30 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.