I think that I need to use the send mail task in DTS but I do not know how to do this. I will be executing the DTS at around mid-night each night. There will be multiple emails sent out. Can I create a DTS package that will execute a stored procedure and for each record retrieved send an email to the recipient using Send Mail in DTS?
You probably could do that, but the standard Send Mail task will generally just email a dataset to a given address. I'm sure you could adapt it do do what you want, but I think it would be easier to code it in a stored proc and use xp_sendmail. I'm not sure, but I suspect that the Send Mail task would use the same email gateway as is used by xp_sendmail. Assuming it's a hosted environment, can your service provider give you any specifics on how they prefer you to do it?