sp_send_dbmail not executing in execute sql task without throwing any error

sp_send_dbmail only queues up the mail. Does not attempt to deliver, does not validate the email profile, cannot raise any of the errors you complain are missing.

Read here how to check the status of emails queued: Check the Status of E-Mail Messages Sent With Database Mail:

USE msdb ;  
GO  

-- Show the subject, the time that the mail item row was last  
-- modified, and the log information.  
-- Join sysmail_faileditems to sysmail_event_log   
-- on the mailitem_id column.  
-- In the WHERE clause list items where danw was in the recipients,  
-- copy_recipients, or blind_copy_recipients.  
-- These are the items that would have been sent  
-- to danw.  

SELECT items.subject,  
    items.last_mod_date  
    ,l.description FROM dbo.sysmail_faileditems as items  
INNER JOIN dbo.sysmail_event_log AS l  
    ON items.mailitem_id = l.mailitem_id  
WHERE items.recipients LIKE '%danw%'    
    OR items.copy_recipients LIKE '%danw%'   
    OR items.blind_copy_recipients LIKE '%danw%'  
GO  

More articles on the topic:

• Troubleshooting Database Mail Failures
• Complete Troubleshooting Guide for SQL Server Database Mail

Your error is pretty clear in what is required. First off, create a profile and note down all the values that you will input on the wizard. This guide will help you create the profile correctly.

Once you have verified that the dbmail profile has been setup, its time to put it to test. If you cant send out email through the stored proc, I would want to talk to the AD admin or the person responsible for setting up the email server. This is to make sure that you can anonymously send emails and to take care of any potential relay server issues. Hope this helps.