The new email funcationality in SQL Server 2005 is quite nice. Every time you call msdb.dbo.sp_send_dbmail it sends the email in the background. This is important when you are sending a lot of emails on a already busy server. The natural question then is, how can I see what was sent, and if it was sent properly. The answer is in msdb database.
To see all the email sent out and the contents just do the following:
select * from msdb.dbo.sysmail_mailitems
sent_status field: 0 = waiting to process 1 = success 2 = error with the smtp server
To see if there were any errors, you can check the log using:
select * from msdb.dbo.sysmail_log
If you want to check retries for sending, try this:
select * from msdb.dbo.sysmail_send_retries
There are other tables, but they seem to be managed by the Microsoft SQL Server Management Studio or other stored procedures. Feel free to take a look at them though:
If you need help setting up SQL mail in the first place, follow the link above.