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 = [email protected]
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
[email protected]

__________________
T.B.
[email protected]

To make this easier on you, have your Network Admin setup an alias on your mail server so: [email protected] 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

lol I will try that thanks

U can count on me standing in the unemployment line afterwards lol

Toni Burgess
[email protected]

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

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
[email protected]

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

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



this is how I have my stored procedure set up in SQL:
This is in my asp page (postnew2.asp)
where variables are declared:
[.......code......]

I have this set up like this so I can jus see that my emailsmeTO was working (and it is not)
I still receive an e-mail, just no message in the body.

Toni Burgess
[email protected]

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:

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