Like most SQL groups, we use Database Mail to sent email notifications regarding job status and a host of other tasks.
Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.
Sometimes, we like to review our Database Mail history. This is accomplished by querying the VIEW sysmail_allitems, in the MSDB database. This view contains one row for each message processed by Database Mail. Use this view when you want to see the status of all messages.
SQL Query Database Mail History – Example Query:
FROM msdb.dbo.sysmail_allitems am
ORDER BY am.sent_date DESC
msdb.dbo.sysmail_allitems VIEW Schema:
I hope you find the example above informative. Please use and modify to suit your needs.