This question:

Created script to send mails using sp_send_dbmail- working like a charm.
Now searching for a way to get result code of sent mail (like Success = Recipient got it,
Failure = Did not get regardless of the reason).
I mean SP return codes 0 (success) or 1 (failure) refer to correct mail Profile, not missing Recipient, etc.
Frankly not sure this is possible as it looks like outside Sql Server authority/responsibility?!

asked in this thread on SQLServerCentral prompted me to do some research into Database Mail.  The result of the research is that there is no way to get this information from SQL Server.

Basically the way Database Mail/sp_send_dbmail works is that the message is placed in a Service Broker queue (sp_send_dbmail returns success), the external Database Mail executable reads the queue and sends the message to the designated SMTP mail server.  If the mail server accepts the message then Database Mail is done and the status is set to sent.  So, if you have an incorrect email address or the receiving server refuses it, SQL Server has no way to know.  In order to find this out you would need to use a valid Reply To or From email address and monitor that mailbox.

Here’s the query I use for checking Database Mail:

SELECT
SEL.event_type,
SEL.log_date,
SEL.description,
SF.mailitem_id,
SF.recipients,
SF.copy_recipients,
SF.blind_copy_recipients,
SF.subject,
SF.body,
    SF.sent_status,
SF.sent_date
FROM
msdb.dbo.sysmail_faileditems AS SF JOIN
msdb.dbo.sysmail_event_log AS SEL
ON SF.mailitem_id = SEL.mailitem_id

Let me know if you have any better ways to find errors for Database Mail.