I am new to using sql server stored procedures and CDONTS to send HTML email. I am using "DECLARE @body VARCHAR(8000)", and the stored procedure compiles and sends the email.
When I look at the email in my mailbox, the email gets cut off around 15,000 characters (I have over 20,000 characters in my HTML code).
I tried using "DECLARE @body TEXT" instead of "DECLARE @body VARCHAR(8000)", but I get the error:
"The text, ntext, and image data types are invalid for local variables."
Please help. Any suggestions or information would be appreciated. Thank you in advance.
---------- MY CODE ---------------
CREATE PROCEDURE sp_SendNewsletter
AS
DECLARE @result INT
DECLARE @object INT
DECLARE @from VARCHAR(500)
DECLARE @to VARCHAR(500)
DECLARE @bcc VARCHAR(500)
DECLARE @subject VARCHAR(1000)
DECLARE @body VARCHAR(8000)
--The text, ntext, and image data types are invalid for local variables.
--DECLARE @body TEXT
BEGIN
Print 'Sending Test Newletter to 1 Email Address'
--Create the object, if @result <> 0, there is an error.
PRINT 'Creating the CDONTS.NewMail object'
EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT
IF @result <> 0
BEGIN
PRINT 'sp_OACreate Failed'
RETURN @result
END
--HTML email
EXEC @result = sp_OASetProperty @object, 'MailFormat',0
EXEC @result = sp_OASetProperty @object, 'BodyFormat',0
--Send Email using Send method, if @result <> 0, there is an error.
PRINT 'Sending the message using the Send method'
set @from = 'My Newsletter <newsletter@abc.com>'
set @to = 'test@abc.com'
set @subject = 'My Newsletter'
-- *************************** EMAIL CONTENT ***************************
--the store procedure compiles and works -> Problem is the HTML code
--in @body gets cut off (around 15,000 characters)
set @body = '<HTML><HEAD>Whole bunch of HTML here (over 20,000 characters)</HEAD><BODY>'
-- *************************** END OF EMAIL CONTENT *********************************
EXEC @result = sp_OAMethod @object, 'Send', NULL, @from, @to, @subject, @body, 0
IF @result <> 0
BEGIN
PRINT 'sp_OAMethod Failed'
RETURN @result
END
--Destroy the object, if @result <> 0, there is an error.
PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object
IF @result <> 0
BEGIN
PRINT 'sp_OADestroy Failed'
RETURN @result
END
Print 'Sending Test Newletter to 1 Email Address - DONE!'
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
When I look at the email in my mailbox, the email gets cut off around 15,000 characters (I have over 20,000 characters in my HTML code).
I tried using "DECLARE @body TEXT" instead of "DECLARE @body VARCHAR(8000)", but I get the error:
"The text, ntext, and image data types are invalid for local variables."
Please help. Any suggestions or information would be appreciated. Thank you in advance.
---------- MY CODE ---------------
CREATE PROCEDURE sp_SendNewsletter
AS
DECLARE @result INT
DECLARE @object INT
DECLARE @from VARCHAR(500)
DECLARE @to VARCHAR(500)
DECLARE @bcc VARCHAR(500)
DECLARE @subject VARCHAR(1000)
DECLARE @body VARCHAR(8000)
--The text, ntext, and image data types are invalid for local variables.
--DECLARE @body TEXT
BEGIN
Print 'Sending Test Newletter to 1 Email Address'
--Create the object, if @result <> 0, there is an error.
PRINT 'Creating the CDONTS.NewMail object'
EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT
IF @result <> 0
BEGIN
PRINT 'sp_OACreate Failed'
RETURN @result
END
--HTML email
EXEC @result = sp_OASetProperty @object, 'MailFormat',0
EXEC @result = sp_OASetProperty @object, 'BodyFormat',0
--Send Email using Send method, if @result <> 0, there is an error.
PRINT 'Sending the message using the Send method'
set @from = 'My Newsletter <newsletter@abc.com>'
set @to = 'test@abc.com'
set @subject = 'My Newsletter'
-- *************************** EMAIL CONTENT ***************************
--the store procedure compiles and works -> Problem is the HTML code
--in @body gets cut off (around 15,000 characters)
set @body = '<HTML><HEAD>Whole bunch of HTML here (over 20,000 characters)</HEAD><BODY>'
-- *************************** END OF EMAIL CONTENT *********************************
EXEC @result = sp_OAMethod @object, 'Send', NULL, @from, @to, @subject, @body, 0
IF @result <> 0
BEGIN
PRINT 'sp_OAMethod Failed'
RETURN @result
END
--Destroy the object, if @result <> 0, there is an error.
PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object
IF @result <> 0
BEGIN
PRINT 'sp_OADestroy Failed'
RETURN @result
END
Print 'Sending Test Newletter to 1 Email Address - DONE!'
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
