CDOSYS, CreateMHTMLBody and Stored Procedures

Question

Post reply

CDOSYS, CreateMHTMLBody and Stored Procedures

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
November 2, 2004 at 11:50 am

#182609

So I cribbed sp_send_cdosysmail from a SQL site I found through Google. Worked great as long as I set the message type property to HTMLBody and set the Body to an HTML string. I'd like the SP to be able to pick up the body from a file. I found some code on MSDN on using CDOSYS to send a web page. I thought " I can put the file I want to send on a 'dark' web page then just specify CreateMHTMLBody and the URL, just like Microsoft says." I even copied the URL from the SP and pasted it into IE to make sure the page displayed.
Didn't work. I got a blank mail message...
Anyone ever use this, particularly in an SP? If not, I'm welcome to other ways of picking up a file as the message body. (I'm doing this on SQL Server so I can send to a VERY large distribution internally in our company on a daily basis.)
Here's my modified SP in case anyone can point out a glaringly stupid error:
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
--   @From varchar(100) ,
   @To varchar(100) ,
   @Subject varchar(100)=" ",
   @Body varchar(4000) =" "
/*********************************************************************
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(".Value','2'">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(".Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtpserver.thehartford.com'
-- 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, 'From', '"The Hartford Today" <perfectly valid email address>'
   EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
   EXEC @hr = sp_OASetProperty @iMsg, 'CreateMHTMLBody', 'http://intranetserver/test/tht.htm'
   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
November 5, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dez Kotroczo Grasshopper Points: 17 More actions · Answer 1

Try

EXEC @hr = sp_OAMethod @iMsg, 'CreateMHTMLBody', NULL, 'http://intranetserver/test/tht.htm'.

The Null parameter is to handle the potential return value from the sp_OAMethod call

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

Thanks, I'll try that...

In another vein, since I didn't receive any replies initially and I had to move on this app I constructed the following SP cribbed from MS code. It worked fine for small distribution lists (<1000) but when I run it with our production list (27k+) it somehow exits the SP with an RC=0 after only processing 600-1500 entries (varies each time).

The SP is called by a VBS script that builds the HTML body from a file on another server (hence my previous post on CreateMHTMLBody). The basic functionality is to construct a message, then send it to each of the email addresses in the table passed to the SP by looping through the table, setting the To property and executing a SEND for each address. I even tried commenting out the SEND, but no difference.

Is there an issue with reusing a CDOSYS message? I seem to recall doing this just fine in CDONTS...

I apologize ahead of time for the large amount of code in the post. Here's the SP:

CREATE PROCEDURE [dbo].[proc_send_mail]

(

@tbl varchar(255)='', -- Table/view with the email addresses

@Subject varchar(200)='', -- Subject of the email

@Body varchar(8000) ='', -- HTML body

@sent int OUTPUT, -- Number of items sent

@started datetime OUTPUT, -- date/time started

@ended datetime OUTPUT -- date/time ended

)

AS

Declare @iMsg int

Declare @hr int

Declare @rc int

Declare @sc int

Declare @source varchar(255)

Declare @description varchar(500)

Declare @output varchar(1000)

Declare @ix int

Declare @lc int

Declare @nr int

Declare @msg varchar(255)

Declare @mail varchar(255)

SET NOCOUNT ON

--************* 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("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing&quot.Value','2'">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("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver&quot.Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'higmx.thehartford.com'
-- 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, 'From', ' "The Hartford Today" <'">TheHartfordToday@thehartford.com>' EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- Set HTML Body EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body -- Dynamically create distribution table from table name passed in parms set @msg = 'Create Distribution from '+@tbl EXEC @rc = dbo.[log] @msg create table #foo (mail varchar(255), idkey int identity(1,1)) insert into #foo exec('select mail from '+@tbl+' order by mail') -- Get count of temp tablefor output parameter select @sent=max(idkey) from #foo
-- Get start date/time for output parameter set @started=getdate() set @msg='Sending '+cast(@sent as varchar(10)) EXEC @rc = dbo.[log] @msg -- Get max key of temp teble select @nr=max(idkey) from #foo -- Get first key of temp table select @ix=min(idkey) from #foo -- Loop through table using set processing while @ix<=@nr BEGIN -- Save current key for processing set @lc = @ix -- Get current email address from table select @mail=mail from #foo where idkey=@ix -- Set message To to email address EXEC @hr = sp_OASetProperty @iMsg, 'To', @mail set @rc = @hr -- if Set To fails then destroy message and exit with SetProperty RC if @rc <> 0 BEGIN EXEC @hr = sp_OADestroy @iMsg set @ended = getdate() RETURN @rc END -- Log email address in distribution log EXEC @sc = dbo.dist_log @mail EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL SET @rc = @hr -- if Send fails then destroy message and return Send RC IF @rc <>0 BEGIN EXEC @hr = sp_OADestroy @iMsg set @ended = getdate() RETURN @rc END -- Get next key into temp table select @ix=min(idkey) from #foo where idkey>@lc END set @ended = getdate() EXEC @hr = sp_OADestroy @iMsg RETURN 0 GO