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)
 Attaching A file to Mail

Author  Topic 

jpiscit1
Posting Yak Master

130 Posts
Posted - 2002-10-11 : 11:45:52

I have an existing stored procedure that sends mail for us. Since we use SMTP (Lotus Notes) mail we could not use the conventional xp supplied for MAPI.

With that, I am trying to write a stored procedure that will attach a file when sending mail. For those of you that helped with my previous problem your probably realizing I had to much data to send in a simple text format. Here's where I am:

The existing procedure we call to send mail is:

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

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference 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', 'mailservername'

-- 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_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 need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO


The query I am executing to send simple text messages is:

declare @Body varchar(4000)
select
@body = 'See attached production report'
Select @Body
exec sp_send_cdosysmail 'someone@microsoft.com','someone2@microsoft.com','Test of CDOSYS',@Body

Where someone = the sender and someone2 is the reciever. What code do I need to add to attach a file to this email?

Any help you can provide would be greatly appreciated...

Thanks!

John

jasper_smith
SQL Server MVP &amp; SQLTeam MVY

846 Posts
Posted - 2002-10-11 : 18:03:54
Here's a modification to allow attachments. Either 1 file or a comma delimitted list can be passed (it requires
the function fn_Split or similar [url]http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/treatyourselfListing_01.txt[/url])
e.g.
exec sp_send_cdosysmail 'foo@bar.com','bar@foo.com','test','test','c:\1.txt,c:\2.txt'

ALTER PROCEDURE [dbo].[sp_send_cdosysmail] 
@From varchar(100) , 
@To varchar(100) , 
@Subject varchar(100)=" ", 
@Body varchar(4000) =" " ,
@attachments varchar(4000)=NULL
/********************************************************************* 
Supply attachments as either a single file or a comma delimitted list

This stored procedure takes the above parameters and sends an e-mail. 
All of the mail configurations are hard-coded in the stored procedure. 
Comments are added to the stored procedure where necessary. 
Reference 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) 
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

--************* 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', '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 

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

                SET @counter=@counter+1
        END
END

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

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




HTH
Jasper Smith
Go to Top of Page

jpiscit1
Posting Yak Master

130 Posts
Posted - 2002-10-14 : 11:49:30
Thanks Jasper,

That worked fine. Very nice.

John
Go to Top of Page

tvle
Starting Member

1 Post
Posted - 2004-05-25 : 09:47:10
Hi Jasper,

I have tried your modification and it did not work.
What am I missing?
I have copied exactly what you have posted.
Please help. Thanks so much.

quote:
Originally posted by jasper_smith

Here's a modification to allow attachments. Either 1 file or a comma delimitted list can be passed (it requires
the function fn_Split or similar [url]http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/treatyourselfListing_01.txt[/url])
e.g.
exec sp_send_cdosysmail 'foo@bar.com','bar@foo.com','test','test','c:\1.txt,c:\2.txt'

ALTER PROCEDURE [dbo].[sp_send_cdosysmail] 
@From varchar(100) , 
@To varchar(100) , 
@Subject varchar(100)=" ", 
@Body varchar(4000) =" " ,
@attachments varchar(4000)=NULL
/********************************************************************* 
Supply attachments as either a single file or a comma delimitted list

This stored procedure takes the above parameters and sends an e-mail. 
All of the mail configurations are hard-coded in the stored procedure. 
Comments are added to the stored procedure where necessary. 
Reference 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) 
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

--************* 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', '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 

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

                SET @counter=@counter+1
        END
END

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

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




HTH
Jasper Smith


Thao Le
Go to Top of Page

Darron Michael
Starting Member

1 Post
Posted - 2004-06-04 : 09:37:40
You SO Rock!!!

Thanks so much.


Darron
Go to Top of Page

rnbguy
Constraint Violating Yak Guru

293 Posts
Posted - 2007-02-05 : 01:59:55
this fails to attatch the file, send email with blank attatchment
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts
Posted - 2008-08-11 : 10:21:05
Guys,

Is there a way to use this stored procedure to send the email to multiple recipients (without sending the same email multiple times)?


Thank you!
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts
Posted - 2009-04-21 : 12:22:14
Hello,

This approach of sending an email with an attachment has worked very well for me. I created a job to run this stored procedure, and the emails were sent with the attachment. However, at times, the job runs successfully, but the email is simply not sent and I have no idea why.

Is there a way to catch some errors to find out why an email is not being sent?


Thank you!
Go to Top of Page

billsut
Starting Member

1 Post
Posted - 2010-01-13 : 11:03:56
I need to send the email to another server (SMTP). How do I tell the stored procedure the IP of the server I want to send it to?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-01-13 : 11:05:50
quote:
Originally posted by billsut

I need to send the email to another server (SMTP). How do I tell the stored procedure the IP of the server I want to send it to?

please post it as a new thread Bill. that will increase the chance of people seeing it quickly and replying. please dont reopen old threads.
Go to Top of Page

navbingo20
Starting Member

12 Posts
Posted - 2011-04-07 : 01:33:21
can u send me the procedure for
Here's a modification to allow attachments -- function fn_Split
Go to Top of Page

navbingo20
Starting Member

12 Posts
Posted - 2011-04-07 : 01:36:53
hi jasper smith..

can u provide me the function of dbo.fn_split

plz i need it very urgent
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site