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:
pRS.open "INSERT INTO ecpForms (ecpNumber, ecpTitle, ecpFilename, ecpOriginator, ecpDateDistributed, ecpDateDue, ecpSMEcomplete, ecpPMcomplete) VALUES ('" & pEcpNumber & "', '" & pEcpTitle & "', '" & pEcpFilename & "', '" & pEcpOriginator & "', '" & pEcpDateDistributed & "', '" & pEcpDateDue & "', '" & pEcpSmeComplete & "', '" & pEcpPmComplete & "') ", pConnection
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 * from ecpSQL2.dbo.users e
where e.emailTo = @email_new
or e.emailCC = @email_new or e.emailBCC = @email_new
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:
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
'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.
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