SQL Server 2008 sp_send_dbmail
The procedure will not report if the email is not received. It will be upon you and your support team to troubleshoot why the email was not received. SQL Server just cares about being able to execute the call. Thomas Stringer pointed out sysmail_allitems in a comment and that will show failed status:

USE msdb;
select recipients, sent_status
FROM sysmail_allitems
GO


If it actually failed the sent_status field will show failed

Edit: Found some more stuff. sp_send_dbmail does not provide a function for "feedback" on the success or failure of the email. You can check the status using the sysmail_xxx tables and in the database mail log. The best I have been able to do on an AD exchange server relaying through anonymous SMTP is a failed status in sysmail_allitems and an error msg in the Database mail log stating unknown user

Links Jack Corbett