The procedure works without problems for the basic 'textbody' and 'htmlbody' type messages. The problem is I would like to get the CreateMHTMLBody method working. This works beautifully as a VBScript which I'm attempting to convert to SQL.
CREATE PROCEDURE usp_send_cdosysmail
@From_Addr VARCHAR(500) ,
@To_Addr VARCHAR(500) ,
@Subject VARCHAR(500),
@Body VARCHAR(4000) ,
@SMTPserver VARCHAR(25) = 'localhost',
@BodyType VARCHAR(10) = 'textbody'
AS
DECLARE @imsg INT
DECLARE @hr INT
DECLARE @source VARCHAR(255)
DECLARE @description VARCHAR(500)
DECLARE @output VARCHAR(1000)
EXEC @hr = sp_oacreate 'cdo.message', @imsg out
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @SMTPserver
EXEC @hr = sp_oamethod @imsg, 'configuration.fields.UPDATE', NULL
EXEC @hr = sp_oasetproperty @imsg, 'to', @To_Addr
EXEC @hr = sp_oasetproperty @imsg, 'from', @From_Addr
EXEC @hr = sp_oasetproperty @imsg, 'subject', @Subject
IF @BodyType <> 'URL'
BEGIN
EXEC @hr = sp_oasetproperty @imsg, @BodyType, @Body
END
ELSE
BEGIN
-- Generate The Body From The Input URL
EXEC @hr = sp_oamethod @imsg, 'CreateMHTMLBody', @body, 0, '', ''
END
EXEC @hr = sp_oamethod @imsg, 'send', NULL
When I test the "EXEC @hr = sp_oamethod @imsg, 'CreateMHTMLBody', @body, 0, '', ''" step it gives me a blank email.
The return code (@hr) for that step is -2147211483 (x80042725).
There is no error message. Search as I might, I haven't been able to find a reference to that error.