Hi there, try something like this:
-- The message that will be dispayed in the <BODY> element of the Mail
-- Please note taht explicit typing of <BODY> is not required as sp_send_dbmail will take care of it
SET @HTMLBody = ''
SET @HTMLBody =
N''+@UserName+'Thanks for using SSC.com'
--Subject of the Mail
SET @MailSubject = ''
SET @MailSubject = 'Learn and teach'
--Trigger the mail
EXEC @RetCode = msdb.dbo.sp_send_dbmail
@profile_name = 'SSC Notification Mailer',
@recipients = @EMailAddress,
@subject = @MailSubject,
@body = @HTMLBody,
@body_format = 'HTML',
@mailitem_id = @MailItemID OUTPUT
--Update after successfully sending Mail Notification
IF @RetCode = 0
BEGIN
SET @Msg = 'Success'
RAISERROR(@Msg,0,1) WITH NOWAIT
END
ELSE
BEGIN
SET @Msg = 'Mail to User: '+@UserName+' FAILED'
RAISERROR(@Msg,0,1) WITH NOWAIT
END
Hope this helps you!