CDO mail and attachment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CDO mail and attachment

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2005-08-08 : 14:02:34

Any idea why my sp is not attaching file:
CREATE PROCEDURE CDO_SendMail(
@From varchar(255) = 'test@y.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) = 'websmtp'
)
as
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

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-08 : 16:13:16

Are you passing the physical pathname?

Have you tried executing it from Query Analyzer? Any errors?

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources