how to send attachments through CDOSYS

Hi
This is working fine only attachments does not work.
so how to send attachments through CDOSYS

Here is my code:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



ALTER PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Cc varchar(4000)=null ,
@Bcc varchar(4000)=null ,
@Subject varchar(100)=" ",
@Body varchar(8000) =" ",
@Attachments varchar(4000) =Null,
@HTMLBodyFlg int=Null

AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

IF IS_SRVROLEMEMBER ('sysadmin') = 0
BEGIN
return 0
END

If @HTMLBodyFlg is Null
Begin
set @HTMLBodyFlg=0
End


--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail.ccipl.com'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
--New added
EXEC @hr = sp_OASetProperty @iMsg, 'cc',@Cc
EXEC @hr = sp_OASetProperty @iMsg, 'bcc',@Bcc


-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
IF @HTMLBodyFlg=0
BEGIN
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
End
Else
BEGIN
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
End
--set @Attachments=null

EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachments

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
IF @hr <>0
--select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
--PRINT @output
SELECT @output = ' Description: ' + @description
--PRINT @output
IF @output is null
RETURN 1
Else
RETURN 0
END
ELSE
BEGIN
--PRINT ' sp_OAGetErrorInfo failed.'
RETURN 0
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
RETURN 1




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Rajeev Kumar Srivastava
--ALWAYS BE POSITIVE!--

Attachments should work ok, they work ok for me <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> I'll check my proc against yours when I get back into work in the morning...<br /><br />but they need to be specified relative to the SQL Server and accessible by the service account that is running SQL Server... so c: est.txt is the c drive on the server and not the workstation...<br /><br />Cheers<br />Twan

Have just checked it against my proc and yes the call looks right, although if you want to attach multiple files, then you have to split it up into multiple AddAttachment calls

the code from my proc is here, ignore the is_on_winnt bit, as I still have to support Windows NT hosted SQL Servers too

Code:

/*********************************************************************
 Add attachments
*********************************************************************/ 
Set @lastaction = 'AddAttachment'

while datalength( @attachments ) > 0
begin
if charindex( '|', @attachments ) > 0
begin
set @attachment = left( @attachments, charindex( '|', @attachments ) - 1 )
set @attachments = right( @attachments, datalength( @attachments ) - charindex( '|', @attachments ) )
end
else
begin
set @attachment = @attachments
set @attachments = NULL
end

-- you can't seem to get a return code...
if( @is_on_winnt = 1 )
begin
EXEC sp_OAMethod @msg_id, 'AttachFile', null, @attachment
end
else
begin
EXEC sp_OAMethod @msg_id, 'AddAttachment', null, @attachment

-- kludgy way to get error messages in cases such as file not found, etc.
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @source = 'CDO.Message.1'
goto error
end
end
{/code]

Cheers
Twan 

Thanks Actually i was using my workstation path.Now it is runing fine.And thanks for the multiple Attachment Script.

Rajeev Kumar Srivastava
--ALWAYS BE POSITIVE!--

I have used CDOSYS to send mail it works fine.But attachment is a problem.I have added the following lines in my stored procedure.But it send s mail without ant attachment though the file exists.

EXEC sp_OAMethod @iMsg, 'AddAttachment', null, @file

where @file = 'c: est est.xls' a parameter passed from the code to the sp.

Please help me out.
Edit/Delete Message

I have used CDOSYS to send mail it works fine.But attachment is a problem.I have added the following lines in my stored procedure.But it send s mail without ant attachment though the file exists.

EXEC sp_OAMethod @iMsg, 'AddAttachment', null, @file

where @file = 'c: est est.xls' a parameter passed from the code to the sp.

Please help me out.

Check the following...

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649
http://support.microsoft.com/kb/312839


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.