If you use Database Mail in SQL Server, you can use the sysmail_sentitems view to return a list of all emails that Database Mail has sent.

Example

Here’s an example of returning all sent emails. Note that this needs to be done on the msdb database.

SELECT * FROM msdb.dbo.sysmail_sentitems;

Result (using vertical output):

mailitem_id                 | 3
profile_id                  | 1
recipients                  | [email protected]
copy_recipients             | NULL
blind_copy_recipients       | NULL
subject                     | SQL Server Agent Job: FAILED
body                        | Your favorite SQL Server Agent job just failed
body_format                 | TEXT
importance                  | NORMAL
sensitivity                 | NORMAL
file_attachments            | NULL
attachment_encoding         | MIME
query                       | NULL
execute_query_database      | NULL
attach_query_result_as_file | 0
query_result_header         | 1
query_result_width          | 256
query_result_separator      |  
exclude_query_output        | 0
append_query_error          | 0
send_request_date           | 2020-08-24 03:58:57.887
send_request_user           | sa
sent_account_id             | 1
sent_status                 | sent
sent_date                   | 2020-08-24 03:59:01.000
last_mod_date               | 2020-08-24 03:59:01.543
last_mod_user               | sa

I used vertical output here so that you don’t need to scroll sideways to see all columns.

I’m also displaying just the first row (even though two rows were returned), to keep things concise.

Here it is again, except this time I’ll switch to horizontal output and display both rows. Also, I’ll specify just a few columns.

SELECT
    mailitem_id,
    sent_status,
    send_request_date
FROM msdb.dbo.sysmail_allitems;

Result (using vertical output):

+---------------+---------------+-------------------------+-------------------------+
| mailitem_id   | sent_status   | send_request_date       | sent_date               |
|---------------+---------------+-------------------------+-------------------------|
| 3             | sent          | 2020-08-24 03:58:57.887 | 2020-08-24 03:59:01.000 |
| 4             | sent          | 2020-08-24 04:11:19.300 | 2020-08-24 04:11:22.000 |
+---------------+---------------+-------------------------+-------------------------+

You can query sysmail_unsentitems to return a list of unsent emails (ones that are yet to be sent, not necessarily failed).

You can also query sysmail_faileditems to get all failed emails.

You can also query sysmail_allitems to get all emails (sent, unsent, failed, and retrying).