Sending reports with CDONTS.NewMail
-
Hi,
I have a stored proc that runs a number of queries and outputs to a file. Now I need to email the report around but the client does not use Exchange. Thus, I need to use CDO. The email goes OK but I can not get the file to attach.
The relevant code is thus...
EXEC @r = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @r = sp_OAMethod @MailID, 'Attachfile', @FileName
EXEC @r = sp_OASetProperty @MailID, 'Body', @FileName
EXEC @r = sp_OASetProperty @MailID, 'From', @From
EXEC @r = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @r = sp_OASetProperty @MailID, 'To', @To
EXEC @r = sp_OASetProperty @MailID, 'BodyFormat ', 0
EXEC @r = sp_OAMethod @MailID, 'Send', NULL
EXEC @r = sp_OADestroy @MailID
The Filename is an absolute network address like \\server\dir\IntChk.wri and when I send it as a link, I can open the file. However, when I try to attach the file the return value [@R] from the AttachFile method is -2147211483. I have not found this code anywhere. Is there some conversion I have to do on this before it becomes meaningful?
If this is not possible, is there some way to assign the results of the report proc to a variable so I can include it in the email?
Thanks for your help!!
Cheers,
Brian
Cheers!Brian Wawrow
-
you could also use smtp mail using the sp and dll available at http://www.sqldev.net/xp/xpsmtp.htm
-
I would try that but it's not my server. I don't have access to install new DLLs so I have to work with the tools at hand.
I am under the impression that this should be possible using CDONTS. Is it not?
Cheers!
Brian
Cheers!Brian Wawrow
-
What OS are you running on? For Windows 2003 you might have better success using CDO.Message instead.
--------------------
Colt 45 - the original point and click interface
-
Thanks for pointing me in the right direction, Phil! Go Wallabies! That totally did the trick.
For those of you with the same problem, it basically goes like this...
/* new cdo.message test here */
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255)
DECLARE @desc varchar(255)
DECLARE @filename varchar(255)
DECLARE @smtpserver varchar(255)
SET @filename = '\\NOX\whatnot\TheHours.xls'
--##Create Message object
EXEC @hr = sp_OACreate 'CDO.Message', @object OUT
IF @hr 0
BEGIN
PRINT @hr
END
--##Set sendusing property
EXEC @hr = sp_OASetProperty @object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing")', '2'
IF @hr 0
BEGIN
PRINT @hr
END
--##Set port property
EXEC @hr = sp_OASetProperty @object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/SendUsingMethod")', '25'
IF @hr 0
BEGIN
PRINT @hr
END
--##Set smtp server property
EXEC @hr = sp_OASetProperty @object, 'Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")', @smtpserver
IF @hr 0
BEGIN
PRINT @hr
END
--##Update the configuration
EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', NULL
IF @hr 0
BEGIN
PRINT @hr
END
--##Set TO property
EXEC @hr = sp_OASetProperty @object, 'To', 'yermama@mamashouse.com'
IF @hr 0
BEGIN
PRINT @hr
END
--##Set FROM property
EXEC @hr = sp_OASetProperty @object, 'From', 'bwawrow@ringadingdang.com'
IF @hr 0
BEGIN
PRINT @hr
END
--##Set Subject property
EXEC @hr = sp_OASetProperty @object, 'Subject', 'test0rama'
IF @hr 0
BEGIN
PRINT @hr
END
--##set Subject property
EXEC @hr = sp_OASetProperty @object, 'TextBody', 'test0rama'
IF @hr 0
BEGIN
PRINT @hr
END
--##AddAttachment
IF @filename ''
BEGIN
EXEC sp_OAMethod @object, 'AddAttachment', NULL ,@filename
END
--##Send the mail
EXEC @hr = sp_OAMethod @object, 'Send', NULL
IF @hr 0
BEGIN
PRINT @hr
END
Et voila!
Cheers!Brian Wawrow
-
Brian, Happy to help.
BTW, I prefer the Aussie Cricket team to the Wallabies
--------------------
Colt 45 - the original point and click interface -
I would suggest changing your code structure like this:
DECLARE @object int
-- the rest of your variables
, @hr int
, @src varchar(255)
, @desc varchar(255)
...
--##Create Message object
EXEC @hr = sp_OACreate 'CDO.Message', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fso, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@desc, 'Create CDO.Message'GOTO CLEANUP
END
...
--##Send the mail
EXEC @hr = sp_OAMethod @object, 'Send', NULL
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fso, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@desc, 'Send message'GOTO CLEANUP
END
CLEANUP:
-- Add other cleanup tasks
IF @object <> 0
BEGIN
-- Destroy the object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@desc, 'Destroy CDO.Message object'END
END
GO
Otherwise you are in effect creating a memory leak in SQL Server by by not releasing the CDO.Message object!
Andy
-
I believe the object is destroyed when the batch has finished executing but in any event Andy is right in that it is good coding practice.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply