Mail - 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)

Mail

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2004-07-26 : 11:40:32

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-26 : 19:22:20

Why don't you put the functions in here also so we can try it out. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sardinka
Posting Yak Master

142 Posts

Posted - 2004-07-27 : 08:59:57

CREATE FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can¨ˆt find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources