Well after a couple hours of fooling around with it and searching the internet, me and my coworker figured our a solution. For some reason sql server cannont assign an object to a property of another object. To get around this we figured out we could reference the object property directly. Here is the code...

create PROCEDURE [dbo].[sp_sendmail] @From varchar(100), @To varchar(100), @Subject varchar(100), @Body varchar(4000), @CC varchar(100) = null, @BCC varchar(100) = null
AS


declare @oMsg int
declare @resultCode int

EXEC @resultCode = sp_OACreate 'CDO.Message', @oMsg OUT

if @resultCode = 0 
begin

	EXEC @resultCode = sp_OASetProperty @oMsg, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing").value', '2'
	if @resultCode <> 0 return @resultCode

	EXEC @resultCode = sp_OASetProperty @oMsg, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', 'mail.server.com'
	if @resultCode <> 0 return @resultCode
	
	EXEC @resultCode = sp_OAMethod @oMsg, 'Configuration.Fields.Update'
	if @resultCode <> 0 return @resultCode


	EXEC @resultCode = sp_OASetProperty @oMsg, 'Subject', @Subject
	if @resultCode <> 0 return @resultCode

	EXEC @resultCode = sp_OASetProperty @oMsg, 'To', @To
	if @resultCode <> 0 return @resultCode

	EXEC @resultCode = sp_OASetProperty @oMsg, 'From',@From
	if @resultCode <> 0 return @resultCode

	EXEC @resultCode = sp_OASetProperty @oMsg, 'TextBody', @Body
	if @resultCode <> 0 return @resultCode

	EXEC @resultCode = sp_OASetProperty @oMsg, 'BCC',@BCC
	if @resultCode <> 0 return @resultCode

	EXEC @resultCode = sp_OASetProperty @oMsg, 'CC', @CC
	if @resultCode <> 0 return @resultCode

	EXEC @resultCode = sp_OAMethod @oMsg, 'Send', NULL
	if @resultCode <> 0 return @resultCode

	EXEC @resultCode = sp_OADestroy @oConf
	EXEC @resultCode = sp_OADestroy @oMsg

end

One pitfall was with the the 'http://schemas.microsoft.com/cdo/configuration/sendusing' property. It needs to be a string '2' not an integer. Dont know why, but it doesnt work otherwise. BTW this was on MS Server 2003 with MS SQL 2000 SP3a

Hope this helps someone else.

Eric




>Im trying to send mail via a stored procedure. Here is my code
>

>alter     PROCEDURE [dbo].[sp_sendmail] @From varchar(100), @To varchar(100), @Subject varchar(100), @Body varchar(4000), @CC varchar(100) = null, @BCC varchar(100) = null
>AS
>
>declare @oMsg int
>declare @oConf int
>declare @resultCode int, @resultCode2 int
>DECLARE @src varchar(255), @desc varchar(255)
>
>EXEC @resultCode = sp_OACreate 'CDO.Message', @oMsg OUT
>
>EXEC @resultCode2 = sp_OACreate 'CDO.Configuration', @oConf OUT
>
>if @resultCode = 0 and @resultCode2 = 0
>begin
>
>EXEC @resultCode = sp_OASetProperty @oConf, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort'
>EXEC @resultCode = sp_OASetProperty @oConf, 'Fields(cdoSMTPServer)', 'uwosh.edu'
>EXEC @resultCode = sp_OASetProperty @oConf, 'Fields(cdoSMTPServerPort)', 25
>EXEC @resultCode = sp_OASetProperty @oConf, 'Fields(cdoSMTPAuthenticate)', 'cdoAnonymous'
>
>EXEC @resultCode = sp_OAMethod @oConf, 'Fields.Update'
>
>EXEC @resultCode = sp_OASetProperty @oMsg, 'Configuration', @oConf
>
>IF @resultCode <> 0
>BEGIN
>   EXEC sp_OAGetErrorInfo @oMsg, @src OUT, @desc OUT
>   SELECT hr=convert(varbinary(4),@resultCode), Source=@src, Description=@desc
>   RETURN
>END
>
>
>EXEC @resultCode = sp_OASetProperty @oMsg, 'Subject', @Subject
>EXEC @resultCode = sp_OASetProperty @oMsg, 'To', @To
>EXEC @resultCode = sp_OASetProperty @oMsg, 'From',@From
>EXEC @resultCode = sp_OASetProperty @oMsg, 'TextBody', @Body
>EXEC @resultCode = sp_OASetProperty @oMsg, 'BCC',@BCC
>EXEC @resultCode = sp_OASetProperty @oMsg, 'CC', @CC
>EXEC @resultCode = sp_OAMethod @oMsg, 'Send', NULL
>EXEC @resultCode = sp_OADestroy @oMsg
>
>end

>Its giving me an error after the "EXEC @resultCode = sp_OASetProperty @oMsg, 'Configuration', @oConf" line. The error im getting is :0x80020005 ODSOLE Extended Procedure Type mismatch.
>
>Any ideas why this would be giving me this error? TIA
>
>Eric Stephani
>UWO DBA