Automate Row Update and send email?

I'd like to update rows matching a certain criteria each night automatically and then send an email.

I have a table with a Status column and an Expire Date column. I want to update rows where the Status = Open and the Expire Date < The current Date by changing the Status to Closed then send an HTML email. Do I have to write a program and schedule it to run or is there a way to do this in MS SQL? I looked at using a stored procedure but I don't see how to schedule one to run (other than at startup) and I'm not sure if the GETDATE function can be called.

Thanks for any help.

Joewindows service or a scheduled task.

sql -> a scheduled job.

It's not some procedure you can create or a function. You have to set it up on the main service to execute.|||create a SQL Server Agent Job to run the stored proc on a schedule...|||I have the same situation.
I am using Access, how can I do this?
Create a .vbs and schedule it with scheduled tasks?
I tried but i get an error in the first Dim statement.|||Thanks for the help. I ended up just creating another asp.net page to take care of it. Then I just run it as a scheduled task and kill it. I haven't learned enough about stored procedure to do it the otherway yet.

Joe