Mail

[sardinka2]

Here is my code for CDOMail. However when I try to send to attachemnt it will not work (working with one attachment only). Any idea why?
CREATE PROCEDURE CDO_SendMail(
@From varchar(255) = 'sql@msn.com',
@To varchar(255),
@Cc varchar(255) = '',
@Bcc varchar(255) = '',
@Subject varchar(255),
@MessageFormat int = 0, -- default to HTML, 1 = text, 0 = html
@Attachments varchar(8000) = null,
@Message varchar(8000),
@Priority int = 2, -- default to high, 1 = normal, 0 = low
@cdoSendUsingPort char(1) = '2', -- 1 = local smtp service, 2 = remote smtp service
@MailServer varchar(20) = 'server'
)
as
--Send Email
declare @CDO int, @OLEResult int, @Out int

-- Create CDO.Message object
execute @OLEResult = sp_OACreate 'CDO.Message', @CDO OUT
if @OLEResult <> 0 print 'CDO.Message'
-- Set CDO.Message configuration properties
execute @OLEResult = sp_OASetProperty @CDO, 'Configuration.fields ("

http://schemas.microsoft.com/cdo/configuration/sendusing").

Value', @cdoSendUsingPort
execute @OLEResult = sp_OASetProperty @CDO, 'Configuration.fields ("

http://schemas.microsoft.com/cdo/configuration/smtpserver").

Value', @MailServer
-- Save the configurations to the message object.
execute @OLEResult = sp_OAMethod @CDO, 'Configuration.Fields.Update', Null
-- Set CDO.Message properties
execute @OLEResult = sp_OASetProperty @CDO, 'From', @From
execute @OLEResult = sp_OASetProperty @CDO, 'To', @To
execute @OLEResult = sp_OASetProperty @CDO, 'Cc', @Cc
execute @OLEResult = sp_OASetProperty @CDO, 'Bcc', @Bcc
execute @OLEResult = sp_OASetProperty @CDO, 'Subject', @Subject
execute @OLEResult = sp_OASetProperty @CDO, 'TextBody', @Message
execute @OLEResult = sp_OASetProperty @CDO, 'Importance', @Priority
--Added to handle attachments

Declare @files table(fileid int identity(1,1),[file] varchar(255))
Declare @file varchar(255)
Declare @filecount int ; set @filecount=0
Declare @counter int ; set @counter = 1

IF @attachments IS NOT NULL
BEGIN
INSERT @files SELECT value FROM dbo.fn_split(@attachments,',')
SELECT @filecount=@@ROWCOUNT
WHILE @counter<(@filecount+1)
BEGIN
SELECT @file = [file]
FROM @files
WHERE fileid=@counter
EXEC @OLEResult = sp_OAMethod @CDO, 'AddAttachment', NULL, @attachments
SET @counter=@counter+1
END
END

-- Call Send method of the object
execute @OLEResult = sp_OAMethod @CDO, 'Send', Null
declare @source varchar(255), @description varchar(500), @output varchar(1000)
if @OLEResult <> 0
begin
execute @OLEResult = sp_OAGetErrorInfo Null, @source OUT, @description OUT
if @OLEResult = 0
begin
select @output = ' Source: ' + @source
print @output
select @output = ' Description: ' + @description
print @output
end
else
begin
print ' sp_OAGetErrorInfo failed.'
return
end
end
--Destroy CDO
execute @OLEResult = sp_OADestroy @CDO

return @OLEResult
GO