If you've ever used SQL Mail to send email messages from T-SQL, you probably know how painful it is to get the whole thing up and running. One of the reasons is that both SQL Mail and SQL Agent Mail rely on MAPI, forcing you to go through a complicated setup process, including having to install Outlook on the SQL Server machine and creating an Exchange profile. Another problem is that MAPI is not reliable on SQL Server clusters and this scenario is actually not supported by Microsoft. BTW, SQL Server 2005 will remove the dependency on MAPI and it will allow you to use SMTP based mail.
I recently discovered that Gert Drapers, a member of the SQL Server team, wrote an extended stored procedure called xp_smtp_sendmail that allows you to easily send SMTP mail from T-SQL code. You can use it with both SQL Server 7.0 and 2000 but they are implemented as separate DLLs. You can download the xp_smtp_sendmail stored procedure from Gert's SQLDev.net website. The setup is very easy, just drop the dll to the binn folder and run sp_addextendedproc to register the extended procedure with SQL Server. Gert's site provides very good documentation and sample code to get you up and running very quickly. Aside from following Gert's install instructions you may need to configure the SMTP server you want to use to allow relaying from the database server. That was the only issue I found, other than that it works great for us on many production servers.
You cannot use xp_smtp_sendmail to replace SQL Agent Mail notifications, but you can add a T-SQL step to an existing job and send notifications based on the status of the previous step.