Hi

Im trying to set up a stored procedure which queries a table of users (userdetails)who have a birthday on the present day (field name 'dob') and then sends them a Happy birthday email.

My thinking is that I need to write a query to find the email adds of all users with a birthday on the present day.

so far I have this

SELECT email FROM userdetails WHERE DAY(userdetails.dob) = DATEPART(DAY,GETDATE()) and MONTH(userdetails.dob) = DATEPART(MONTH,GETDATE())

This returns the email add of users with birthdays today.

My question is how can I use the result of this query to populate my @to parameter

im using dbo.sp_send_cdosysmail to send the mail.

My other question is how can I get it to personlise the mailbody with the users name?

Any help would be great - I think im a long way off at the moment.

Thanks

Tim

First some links:

This KB article should point you in the right direction with using the CDONTS stored procedure:

http://support.microsoft.com/default.aspx/kb/312839

You may also want to look into:

http://www.sqldev.net/xp/xpsmtp.htm

which is a much better alternative then CDONTS.

Now to answer your question about getting the email address into the 'To' field. Since you will, no doubt, have multiple birthdays on the same day, I would suggest selecting all of the days birthdays into a Temp table and then :shudder: use a cursor to loop through that table populating the to field with the current email address.

Your second question about personaliazation, inside the Cursor create 2 variables, one that is the message body and one that is the name of the user and do something like this:

Set @messageBody = @messageBody + ' ' + @userName

That should add the users name to the message for you.

hth.

"The one language all programmers understand is profanity."

Thanks fpr ypur reply - I'll see how I get on with it!!

Thanks again

Tim