CDOSYSMAIL with 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

Transact-SQL (2000)

CDOSYSMAIL with attachment

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2005-06-08 : 15:32:27

I can send emails using CDOSYS and the code below, but I need to attach a file aas well witch is not working ANY THOUGHTS.

the Tsql code to create SP is below and execution code below that.

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@attachment varchar(100)= " "
/*********************************************************************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

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

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

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'bost****my smpt servername'

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

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OASetProperty @iMsg, 'Attachment', @attachment
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
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
go
**********************to execute run below***********************
declare @Body varchar(4000)
select @Body = 'This is a Test Message'
declare @attachment varchar(100)
select @attachment = 'c:\test.txt'
exec sp_send_cdosysmail 'steve.hebert@forum.com','steve.hebert@forum.com','Test of CDOSYS',@Body,@attachment

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-09 : 14:21:09

Might be your method.

Someone on this forum who I cannot credit without research, posted a loop solution for attachments. You might search for CDOSYS and find it.

Here's what I took, which works great for me.

-- Attachments...

IF @Attachments IS NOT NULL AND LEN(@Attachments) > 0 BEGIN
	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

        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 @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @file

                SET @counter=@counter+1
        END
END

Note the parameter is 'AddAttachment'

shebert
Yak Posting Veteran

85 Posts

Posted - 2005-06-14 : 13:07:28

Great I will try it.

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-02-05 : 20:17:51

i did this but i get a severe error message, however it still sends attatchement? why is that

sonnysingh
Starting Member

42 Posts

Posted - 2007-02-13 : 01:46:12

Hi All

I try to set up the mail through SQL SERVER without using SQL MAIL and find this thread. I have used the pasted solution of CDOSYS from this thread. but it's came up with following error..


Server: Msg 208, Level 16, State 1, Procedure sp_send_cdosysmail, Line 49
Invalid object name 'dbo.fn_split'.

where to get this functions from cos I could able to find it.

Thanks in advance....

SqlIndia

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-13 : 02:46:48

quote:
Originally posted by sonnysingh

Hi All

I try to set up the mail through SQL SERVER without using SQL MAIL and find this thread. I have used the pasted solution of CDOSYS from this thread. but it's came up with following error..

Server: Msg 208, Level 16, State 1, Procedure sp_send_cdosysmail, Line 49
Invalid object name 'dbo.fn_split'.

where to get this functions from cos I could able to find it.

Thanks in advance....

SqlIndia

did you try to google for it?

http://www.google.com/search?q=fn_split

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources