I'm now a big fan of database mail in SQL Server 2005 - it is so much better than SQLMail. For starters, it is now cluster-aware (customers have been asking for this for a while now), supports SMTP and takes advantage of service broker. When database mail is configured, it is at the user database level. Various objects are installed in the database being enabled for mail. The stored procedure, sp_send_dbmail, is one of these objects and it takes a variety of parameters e.g. queries can be used and the results can be attached as a file. The following script provides a simple example:
There are also two DMV's which can be queried to look at the metadata and determine when the last message was sent; these are sysmail_mailitems & sysmail_log. Please feel free to post any comments on your thoughts regarding database mail.