SQL Server
SQL Server
Create HTML Email Reply
See more Email Object Examples
The goal of this example is to create a new HTML body where the new reply HTML is at the top, and the original HTML body is below.The .eml files for this example are available at HTML Reply Email Samples (.eml)
Note: This is only one of a million ways to do it..
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
DECLARE @email int
EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- To create this example, I used Mozilla Thunderbird (with a GMail accont)
-- to first send a simple HTML email to my chilkatsoft.com email address. I then
-- used Thunderbird to reply with more HTML.
-- I then saved both emails as .eml files. These are available at
-- http://chilkatdownload.com/example_data/htmlreplyemail.zip
-- Load the .eml for the 1st email that was received. This is the original
-- HTML email before the reply HTML is added.
EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/receivedHtmlEmail.eml'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @email
RETURN
END
-- If this email doesn't have an HTML body, then do something else...
-- (that's for another example maybe?)
EXEC sp_OAMethod @email, 'HasHtmlBody', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Email does not have an HTML body..'
EXEC @hr = sp_OADestroy @email
RETURN
END
-- Get the HTML body in a StringBuilder..
DECLARE @sbHtml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHtml OUT
EXEC sp_OAMethod @email, 'GetHtmlBody', @sTmp0 OUT
EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, @sTmp0
-- The HTML body contains this:
-- <html><head>
-- <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
-- </head>
-- <body bgcolor="#FFFFFF" text="#000000">
-- <font face="Calibri"><br>
-- This is a test <font color="#3366ff"><i>HTML email</i></font>...<br>
-- <br>
-- <br>
-- </font>
-- </body>
-- </html>
-- Make sure the BODY tags are lowercase.
DECLARE @numReplaced int
EXEC sp_OAMethod @sbHtml, 'Replace', @numReplaced OUT, '<BODY', '<body'
EXEC sp_OAMethod @sbHtml, 'Replace', @numReplaced OUT, '</BODY', '</body'
-- Get the HTML within the "body" open/close tags
DECLARE @sbBodyContent int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbBodyContent OUT
EXEC sp_OAMethod @sbHtml, 'GetAfterBetween', @sTmp0 OUT, '<body', '>', '</body>'
EXEC sp_OAMethod @sbBodyContent, 'Append', @success OUT, @sTmp0
-- Replace what we just extracted with a marker..
EXEC sp_OAMethod @sbBodyContent, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sbHtml, 'Replace', @numReplaced OUT, @sTmp0, 'TO_BE_UPDATED'
-- Create a template for the new body:
DECLARE @sbTemplate int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbTemplate OUT
DECLARE @bCrlf int
SELECT @bCrlf = 1
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, 'REPLY_HTML_CONTENT', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, '<br>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, '<div class="moz-signature">Best Regards,<br>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, ' John Smith<br>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, ' Chilkat Software, Inc.<br>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, ' <p>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, ' <a href="https://twitter.com/chilkatsoft">Follow Chilkat on', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, ' Twitter</a>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, ' </p>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, '</div>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, '<div class="moz-cite-prefix">On 2/8/2017 4:25 PM, Chilkat Support', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, ' wrote:<br>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, '</div>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, '<blockquote>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, 'ORIGINAL_BODY_CONTENT', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, '</blockquote>', @bCrlf
EXEC sp_OAMethod @sbTemplate, 'AppendLine', @success OUT, '<br>', @bCrlf
-- Replace the markers in the template with actual HTML.
-- First our reply HTML:
DECLARE @myHtmlReplyBody nvarchar(4000)
SELECT @myHtmlReplyBody = 'This is my <font color="#6600cc"><b>reply HTML</b></font><br>'
EXEC sp_OAMethod @sbTemplate, 'Replace', @numReplaced OUT, 'REPLY_HTML_CONTENT', @myHtmlReplyBody
-- Now for the original HTML body content:
EXEC sp_OAMethod @sbBodyContent, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sbTemplate, 'Replace', @numReplaced OUT, 'ORIGINAL_BODY_CONTENT', @sTmp0
-- Insert into sbHtml:
EXEC sp_OAMethod @sbTemplate, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sbHtml, 'Replace', @numReplaced OUT, 'TO_BE_UPDATED', @sTmp0
-- Use sbHtml as the new HTML body..
EXEC sp_OAMethod @sbHtml, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @email, 'SetHtmlBody', NULL, @sTmp0
-- Examine the result HTML.
EXEC sp_OAMethod @sbHtml, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
-- Save the email.
EXEC sp_OAMethod @email, 'SaveEml', @success OUT, 'qa_output/emailWithUpdatedHtmlBody.eml'
-- Note: To finish creating the reply email, modify the To/From/CC headers,
-- the subject, etc. You may also with to iterate over the headers (from 1 to NumHeaderFields)
-- and selectively remove all except the particular headers you wish to keep.
-- You should keep: MIME-Version, Content-Type, Message-ID. Chilkat will automaticaly
-- generate and replace the Message-ID at the time of sending. You'll want to replace
-- the Date header with the current date/time.
--
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @sbHtml
EXEC @hr = sp_OADestroy @sbBodyContent
EXEC @hr = sp_OADestroy @sbTemplate
END
GO