try this simple and works find microsoft example:

CREATE PROCEDURE [dbo].[sp_TestMail]

@From varchar(100),

@To varchar(100),

@Subject varchar(100),

@Body varchar(4000),

@cc varchar(100) = null,

@BCC varchar(100) = null

AS

Declare @MailID int

Declare @hr int

EXEC @hr = sp_OACreate...