November 4, 2002 at 8:53 pm
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.
-- 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', 'xxx.xxx.xxx.xxx'-- 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
I wanted to send an attachement with the code above and i'm having problems. i used attachfile and addattachement but either of them didnot work. Any help would be appreciated.
November 5, 2002 at 7:47 am
Here are my stored procedures to send email use CDONTS, it supports attachments. I can't remember where I got this but, it might work for you (looks like someone named vince might have wrote this). I have not fully tested the attachment piece, but I'm guessing it works.
Here is the SP:
CREATE PROCEDURE usp_sendSMTPmail (@Recipients varchar(8000),
@Subject varchar(255),
@Message text = null,
@Importance int = 1, -- 0=low, 1=normal, 2=high
@cc varchar(8000) = null,
@Bcc varchar(8000) = null,
@Attachments varchar(8000) = null, -- delimeter is ;
@HTMLFormat int = 0,
@From varchar(255) = null)
/* Name: sp_sendSMTPmail
Purpose: Send an SMTP mail using CDONTS object.
Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.
Returns: 0 if successful, 1 if any errors
Sample Usage:
sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this',
@cc='irmsqlmail@db.com',
@Importance=1,
@Attachments='c:\boot.ini;c:\autoexec.bat'
History:
02/07/2001 VRI Created.
*/
AS
SET NOCOUNT ON
DECLARE @object int,
@hr int,
@StrEnd int,
@Attachment varchar(255),
@return int,
@Msg varchar(255)
SELECT @From = isnull(@From, @@SERVERNAME)
-- Create the CDONTS NewMail object.
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError
-- Add the optional properties if they are specified
IF @Message IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Body', @Message
IF @hr <> 0 GOTO ObjectError
END
IF @cc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Cc', @cc
IF @hr <> 0 GOTO ObjectError
END
IF @Bcc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
IF @hr <> 0 GOTO ObjectError
END
IF @HTMLFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0
IF @hr <> 0 GOTO ObjectError
END
-- Loop through the ; delimited files to attach
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
WHILE isnull(len(@Attachments),0) > 0
BEGIN
SELECT @StrEnd = CASE charindex(';', @Attachments)
WHEN 0 THEN len(@Attachments)
ELSE charindex(';', @Attachments) - 1
END
SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
-- Ensure we can find the file we want to send.
DELETE #FileExists
INSERT #FileExists
EXEC master..xp_fileexist @Attachment
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)
RETURN 1
END
EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
IF @hr <> 0 GOTO ObjectError
SELECT @Msg = 'File ' + @Attachment + ' attached.'
PRINT @Msg
END
-- Call the Send method with parms for standard properties
EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @Recipients, @Subject, @Importance=@Importance
IF @hr <> 0 GOTO ObjectError
-- Destroy the NewMail object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError
PRINT 'Message sent.'
RETURN 0
ObjectError:
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN 1
END
GO
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 6, 2002 at 3:34 pm
Thanks Greg,
Sorry for the late reply.
I tried using the procedure provided by you on our test environment and it is not working for Attachments. Secondly, the Server on which i have to run this procedure does not have SMTP service and hence i will have to use cdosys instead of cdonts. But if one of them works, the second should work. Can you let me know if it works for you or not ?
November 6, 2002 at 5:17 pm
I just tested this and the email attachment seems to work in my environment. Just to let you know this machine has both IIS and SQL. It is my understanding the the IIS install installed the necessary componets to allow CDONTS to work.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 6, 2002 at 5:24 pm
Thanks for the efforts Greg. If you want to use a remote SMTP server, cdonts does not support this and thus we have to use cdosys. I will try to make sure that the proedure works and let you know.
Thanks once again
November 7, 2002 at 8:35 am
The actual method for the CDOSys object to attach a file is AddAttachment. As in :
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', 'http://someuri/picture.gif'
OR
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', 'C:\file.txt'
Hope that helps....
Tim C.
//Will write code for food
Edited by - tcartwright@thesystemshop.com on 11/07/2002 08:35:35 AM
Tim C //Will code for food
November 7, 2002 at 9:20 am
Just to confirm Tim's comment. I was able to modify sp_send_cdosysmail by adding an attachment parameter and the line
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',@rv out, @ATTACHMENT
--@rv captures the method return
and recieved emails with attachments without a problem.
November 7, 2002 at 10:56 am
This looks really exciting (being able to use BCC: would be great) but I'm having a basic issue with running it. I'm receiving the following error code:
Invalid class string (0x800401f3)
If I understand correctly, this means that something needs to be registered on my SQL Server (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_3enj.asp), but I'm not exactly sure what. Can anyone help?
Note: The SMTP Server is already installed and the CDONTS Reference Library is available when working on Visual Basic projects. I'm using SQL Server 2000 with SP2.
Thanks.
Everett Wilson
ewilson10@yahoo.com
November 7, 2002 at 11:18 am
Doh! Never mind, didn't have SMTP Service installed.
Everett Wilson
ewilson10@yahoo.com
November 12, 2002 at 8:02 pm
Thanks everyone for the help.
December 30, 2002 at 8:40 am
I am still having problems with this. I have tried all the methods discussed and still can't get the thing to send attachments. Any more suggestions?
December 30, 2002 at 8:55 am
What kind of errors are you getting? How close is your SP to the one I send?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 30, 2002 at 9:06 am
I'm not getting any errors. I receive the email minus the attachment.
ALTER PROCEDURE [dbo].[sp_send_mail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@ATTACH varchar(300)=" "
/*********************************************************************
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 @rv int
--************* 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.
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', '10.120.5.130'
-- 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, 'AttachFile', @Attach
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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
December 30, 2002 at 9:23 am
Have you tried to change the attachment method to "AddAttachment"? From prior posts looks like it was suggested that this was the method to use for cdosys. The "Attachfile" method is the one for cdonts.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 30, 2002 at 9:25 am
I'm sorry I did try that and it does not seem to be working.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply