Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 16th, 2006, 10:10 AM
Authorized User
 
Join Date: Sep 2006
Location: Jax, NC, USA.
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default Send e-mails to multiple recipients using database

Hello everyone!

PURPOSE: I have some email addresses hard coded into a program. I want to send those addresses to a variable name, so when employees are no longer working on a certain project the change can be made within the database (rather than searching through the code to delete/change emails).

example: If certain criteria in a form is completely filled certain emails are sent out.
if AdminCriteriaEntered = True then
[email code]
.From = Session(UserEmail)
.To = myemail@myplace.com
end if

Well I want to change this to something like
.From = Session(UserEmail)
.To = AdminUsers

and I wanted AdminUsers to be set to data from the database (through a sql query).
example:
rs.open "SELECT * FROM EmployeeTable WHERE UserJob = Admin"
AdminUsers = "Results From Query".

I was thinking it needed to be somewhere on the lines of
if not rs.EOF then
AdminUsers = rs("UserEmail") & "; "
end if

If anyone can help with this I'd appreciate it. Thanks in advance.

Toni Burgess
misskaos99@yahoo.com
__________________
T.B.
misskaos99@yahoo.com
Reply With Quote
  #2 (permalink)  
Old October 16th, 2006, 10:17 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

To make this easier on you, have your Network Admin setup an alias on your mail server so: Admins@mydomain.com would send emails to, say, 5 different people. The problem becomes that CDONTS, for whatever reason, has a slight problem when trying to send an email to say 3000 people but an alias is much more effective.

If you can't get an alias setup (first smack your network admin) you can do this:

While Not rs.eof
AdminUsers = AdminUsers + rs("userEmail") & ";"
Wend

hth

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Reply With Quote
  #3 (permalink)  
Old October 16th, 2006, 10:33 AM
Authorized User
 
Join Date: Sep 2006
Location: Jax, NC, USA.
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default

lol I will try that thanks

Quote:
quote:Originally posted by dparsons
If you can't get an alias setup (first smack your network admin)....
U can count on me standing in the unemployment line afterwards lol

Toni Burgess
misskaos99@yahoo.com
Reply With Quote
  #4 (permalink)  
Old October 16th, 2006, 03:13 PM
Authorized User
 
Join Date: Sep 2006
Location: Jax, NC, USA.
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default

Can't tell if that works or not, server keeps timing out.

Code:
Dim emailsmeTO, NewPostAlert, SMEcompleteAlert, PMcompleteAlert
NewPostAlert = "NewPostAlert"
...........................code..............................
pConnection = "Provider=SQLOLEDB.1;UID=sa;PWD=;Initial Catalog=ecpSQL2;Data Source=localhost"
Set pRS = server.CreateObject("ADODB.RecordSet")
Set eRS = server.CreateObject("ADODB.RecordSet")

   pRS.open "INSERT INTO ecpForms (ecpNumber, ecpTitle, ecpFilename, ecpOriginator, ecpDateDistributed, ecpDateDue, ecpSMEcomplete, ecpPMcomplete) VALUES ('" & pEcpNumber & "', '" & pEcpTitle & "', '" & pEcpFilename & "', '" & pEcpOriginator & "', '" & pEcpDateDistributed & "', '" & pEcpDateDue & "', '" & pEcpSmeComplete & "', '" & pEcpPmComplete & "') ", pConnection
eRS.open "SELECT * FROM users WHERE emailAdress=" & NewPostAlert, pConnection
While Not eRS.EOF
emailsmeTO = emailsmeTO & eRS("emailAddress") & ";"
Wend
I'm using two different tables so I didn't know if I needed to open two different record sets, hence the eRS...
the Form information is going to posted into a table called "ecpForms", however the email information (query) will be derived from another table called "users". I should still be able to do this, even tho I'm using two different tables, right? (I'm sorry, I don't know why I didn't say that earlier *slap on the head*).

(BTW, the server times out if I try using just one rs also)

Toni Burgess
misskaos99@yahoo.com
Reply With Quote
  #5 (permalink)  
Old October 16th, 2006, 03:17 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Ok. Only use one record set, execute your INSERT close the recordset then open it back up with the select statement (better yet use a SQL Stored procedure and do everything inside the procedure)

this is a syntax error too:
"SELECT * FROM users WHERE emailAdress=" & NewPostAlert

should be

"SELECT * FROM users WHERE emailAdress='" & NewPostAlert & "'"

(Assuming that your email address field is of type (n)varchar ;]

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Reply With Quote
  #6 (permalink)  
Old October 17th, 2006, 04:35 PM
Authorized User
 
Join Date: Sep 2006
Location: Jax, NC, USA.
Posts: 74
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to misskaos
Default

Okay, for some reason it's not catching the email addresses...



this is how I have my stored procedure set up in SQL:
Code:
CREATE procedure sendemail_new @emailTo  varchar(20),
@emailCC  varchar(20),
@emailBCC  varchar(20)

As begin
declare @email_new varchar(30), @emailsmeTO varchar(255)

select * from ecpSQL2.dbo.users where ecpSQL2.dbo.users.emailTo = @email_new or ecpSQL2.dbo.users.emailCC = @email_new or ecpSQL2.dbo.users.emailBCC = @email_new
end;
GO
This is in my asp page (postnew2.asp)
where variables are declared:
Code:
Dim emailsmeTO, email_new
email_new = "NewPostAlert"
[.......code......]

Code:
pConnection = "Provider=SQLOLEDB.1;UID=sa;PWD=;Initial Catalog=ecpSQL2;Data Source=localhost"
Set pRS = server.CreateObject("ADODB.RecordSet")

pRS.open "INSERT INTO ecpForms (ecpNumber, ecpTitle, ecpFilename, ecpOriginator, ecpDateDistributed, ecpDateDue, ecpSMEcomplete, ecpPMcomplete) VALUES ('" & pEcpNumber & "', '" & pEcpTitle & "', '" & pEcpFilename & "', '" & pEcpOriginator & "', '" & pEcpDateDistributed & "', '" & pEcpDateDue & "', '" & pEcpSmeComplete & "', '" & pEcpPmComplete & "') ", pConnection
pRS.close
pRS.open "Exec sendemail_new" & email_new, pConnection
if not pRS.EOF then
emailsmeTO = pRS("emailAddress") & ";" + emailsmeTO
end if
I have this set up like this so I can jus see that my emailsmeTO was working (and it is not)
Code:
With cdoMsg
   Set .Configuration = cdoConf
   .From = Session("UserEmail")
   .To = "programmer@needhelp.com"
   .Cc = emailsmeTO
   .Bcc = emailsmeTO
   .Subject = "ECP Number: " & pEcpNumber & " -- " & pEcpTitle 
   .TextBody = emailsmeTO
   .Send
 response.redirect("viewAll2.asp") 
End With
I still receive an e-mail, just no message in the body.

Toni Burgess
misskaos99@yahoo.com
Reply With Quote
  #7 (permalink)  
Old October 17th, 2006, 11:09 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Ok! Let me try to get a grasp on this! When I said use a stored procedure I meant for you to do all of your work inside the stored procedure so lets start there shall we?

We are going to take these lines:

Code:
pRS.open "INSERT INTO ecpForms (ecpNumber, ecpTitle, ecpFilename, ecpOriginator, ecpDateDistributed, ecpDateDue, ecpSMEcomplete, ecpPMcomplete) VALUES ('" & pEcpNumber & "', '" & pEcpTitle & "', '" & pEcpFilename & "', '" & pEcpOriginator & "', '" & pEcpDateDistributed & "', '" & pEcpDateDue & "', '" & pEcpSmeComplete & "', '" & pEcpPmComplete & "') ", pConnection
pRS.close
pRS.open "Exec sendemail_new" & email_new, pConnection
And turn them into one call!

First the stored procedure:

CREATE PROCEDURE sendemail_new @ecpNumber varchar(50), @ecpTitle varchar(50), @ecpFilename varchar(50), @ecpOriginator varchar(50), @ecpDateDistributed varchar(50), @ecpDateDue varchar(50), @ecpSMEcomplete varchar(50), @ecpPMcomplete varchar(50), @email_new as
--This will insert your record into the database
INSERT INTO ecpForms (ecpNumber, ecpTitle, ecpFilename, ecpOriginator, ecpDateDistributed, ecpDateDue, ecpSMEcomplete, ecpPMcomplete) VALUES (@ecpNumber, @ecpTitle, @ecpFilename, @ecpOriginator, @ecpDateDistributed, @ecpDateDue, @ecpSMEComplete, @ecpPMcomplete)

--SELECT Data
     select * from ecpSQL2.dbo.users e
     where e.emailTo = @email_new
     or e.emailCC = @email_new or e.emailBCC = @email_new

/Stored Proc

Ok just real quick, a database lesson. You don't need to fully qualifiy the database UNLESS the stored procedure is attached to a different database then the tables you are calling. For example, I assume that the stored procedure belongs to the database ecpSQL2 you could Simply do SELECT * from users! In the event that the stored procedure was attached to a database called, say, ecpSQL3 you then would have to fully qualifiy your name as ecpSQL2.dbo.users.

Also, to save you key strokes, alias your table names in the above example ecpSQL2.dbo.users notice that i put an e after the table path, that is called aliasing so now all i have to do is use the alias e to refernce the column names in SQL (mucho time saver)

Ok now what you need to do is:
Dim sSQL

sSQL = "exec sendmail_new " "'" &pEcpNumber & "', '" & pEcpTitle & "', '" & pEcpFilename & "', '" & pEcpOriginator & "', '" & pEcpDateDistributed & "', '" & pEcpDateDue & "', '" & pEcpSmeComplete & "', '" & pEcpPmComplete & "','" & email_new &"'"
pRS.open sSQL, pConnection
While not pRS.EOF then
emailsmeTO = pRS("emailAddress") & ";" + emailsmeTO
Wend

'the rest of your code here

I hope i have explained this enough for you, essentially what i have done is take all of the SQL statements and wrapped them up into one procedure; your insert will execute first (which returns 0 records) and then i execute the select wich will return you a result set that you can iterate through.

Lastly, on your stored procedure, i have @variable varchar(50) the varchar part needs to match the datatype of the column that element will be inserted into.

hth

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error when i send mails ssomchai BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 May 7th, 2008 03:44 AM
Send email using CDO, multiple recipients for DSN? Mattallyc Classic ASP Professional 7 October 2nd, 2006 10:43 AM
send message to many recipients eda BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 0 May 29th, 2006 08:49 PM
How to send e-mails??? Varg_88 Classic ASP Basics 4 December 31st, 2004 03:42 AM
Send mail to multiple recipients...it hurts sherbir Servlets 1 September 24th, 2004 01:04 PM



All times are GMT -4. The time now is 06:46 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.