(SQL Server) Controlling Charset of HTML Email

This example demonstrates how to change the charset of an HTML email by setting the Charset property.

Chilkat ActiveX Downloads

ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    DECLARE @email int
    -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Tell Chilkat you want the email to be utf-8.
    EXEC sp_OASetProperty @email, 'Charset', 'utf-8'

    EXEC sp_OASetProperty @email, 'From', 'somebody@gmail.com'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Joe', 'joe_somebody@gmail.com'
    EXEC sp_OASetProperty @email, 'Subject', 'This is a test'

    -- Load a utf-8 HTML file that will be the body of this email.
    -- The HTML contains this line to specify the charset:
    -- 
    --     <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    -- 
    -- The sample HTML is available here:  https://chilkatsoft.com/exampleData/sample_utf8.html
    -- 
    DECLARE @sbHtml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHtml OUT

    EXEC sp_OAMethod @sbHtml, 'LoadFile', @success OUT, 'qa_data/html/sample_utf8.html', 'utf-8'

    EXEC sp_OAMethod @sbHtml, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @email, 'SetHtmlBody', NULL, @sTmp0

    -- If this email was sent as-is, it would look like this:
    DECLARE @mailman int
    -- Use "Chilkat_9_5_0.MailMan" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT

    EXEC sp_OAMethod @mailman, 'RenderToMime', @sTmp0 OUT, @email
    PRINT @sTmp0

    -- The MIME that would be sent is this:

    -- 	MIME-Version: 1.0
    -- 	Date: Wed, 08 Mar 2017 09:39:06 -0600
    -- 	Message-ID: <53930EB7EFA37B121FB2CC0E57E9FA4D02AD86A3@CHILKAT13>
    -- 	Content-Type: text/html; charset=utf-8
    -- 	Content-Transfer-Encoding: quoted-printable
    -- 	X-Priority: 3 (Normal)
    -- 	From: somebody@gmail.com
    -- 	To: Joe <joe_somebody@gmail.com>
    -- 	Subject: This is a test
    -- 
    -- 	<html>
    -- 	<head>
    -- 	<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dutf-8" />
    -- 	</head>
    -- 	<body>
    -- 	<p>=C3=A0=C3=A8=C3=AC=C3=B2=C3=B9=C3=80=C3=88=C3=8C=C3=92=C3=99=C3=A1=C3=A9=
    -- 	=C3=AD=C3=B3=C3=BA=C3=BD=C3=81=C3=89=C3=8D=C3=93=C3=9A=C3=9D=C3=A2=C3=AA=C3=
    -- 	=AE=C3=B4=C3=BB=C3=82=C3=8A=C3=8E=C3=94=C3=9B=C3=A3=C3=B1=C3=B5=C3=83=C3=91=
    -- 	=C3=95=C3=A4=C3=AB=C3=AF=C3=B6=C3=BC=C3=BF=C3=84=C3=8B=C3=8F=C3=96=C3=9C=C5=
    -- 	=B8=C3=A5=C3=85</p>
    -- 	</body>
    -- 	</html>
    -- 

    -- -----------------------------------------
    -- Let's say we want to send iso-8859-1 instead...
    -- Just set the email object's Charset property, like this:
    EXEC sp_OASetProperty @email, 'Charset', 'iso-8859-1'

    -- Now see what would be sent:
    EXEC sp_OAMethod @mailman, 'RenderToMime', @sTmp0 OUT, @email
    PRINT @sTmp0

    -- 	MIME-Version: 1.0
    -- 	Date: Wed, 08 Mar 2017 09:45:03 -0600
    -- 	Message-ID: <DD08A5B56532AF11505ACF21A1338E0A3024D34A@CHILKAT13>
    -- 	Content-Type: text/html; charset=iso-8859-1
    -- 	Content-Transfer-Encoding: quoted-printable
    -- 	X-Priority: 3 (Normal)
    -- 	From: somebody@gmail.com
    -- 	To: Joe <joe_somebody@gmail.com>
    -- 	Subject: This is a test
    -- 
    -- 	<html>
    -- 	<head><META http-equiv=3D"Content-Type" content=3D"text/html;charset=3Diso-=
    -- 	8859-1">
    -- 
    -- 	</head>
    -- 	<body>
    -- 	<p>=E0=E8=EC=F2=F9=C0=C8=CC=D2=D9=E1=E9=ED=F3=FA=FD=C1=C9=CD=D3=DA=DD=E2=EA=
    -- 	=EE=F4=FB=C2=CA=CE=D4=DB=E3=F1=F5=C3=D1=D5=E4=EB=EF=F6=FC=FF=C4=CB=CF=D6=DC=
    -- 	=9F=E5=C5</p>
    -- 	</body>
    -- 	</html>

    -- -----------------------------------------
    -- Let's say we don't want quoted-printable, but instead want 8bit.
    -- The AddHeaderField method replaces the header field if it already exists.
    -- Do this to get 8bit encoding instead of quoted-printable.
    EXEC sp_OAMethod @email, 'AddHeaderField', NULL, 'Content-Transfer-Encoding', '8bit'
    -- Now see what would be sent:
    -- (Note: Because RenderToMime returns a string, it generally not possible to render MIME to a string
    -- IF the MIME contains binary data and uses the 8bit encoding (for example, if a JPEG image was attached).  
    -- However, the email would still be sent correctly.)
    EXEC sp_OAMethod @mailman, 'RenderToMime', @sTmp0 OUT, @email
    PRINT @sTmp0

    -- 	MIME-Version: 1.0
    -- 	Date: Wed, 08 Mar 2017 09:51:33 -0600
    -- 	Message-ID: <F38B4A8D33C111057E901782305E01AC5BE31B98@CHILKAT13>
    -- 	Content-Type: text/html; charset=iso-8859-1
    -- 	Content-Transfer-Encoding: 8bit
    -- 	X-Priority: 3 (Normal)
    -- 	From: somebody@gmail.com
    -- 	To: Joe <joe_somebody@gmail.com>
    -- 	Subject: This is a test
    -- 
    -- 	<html>
    -- 	<head><META http-equiv="Content-Type" content="text/html;charset=iso-8859-1">
    -- 
    -- 	</head>
    -- 	<body>
    -- 	<p>àèìòùÀÈÌÒÙáéíóúýÁÉÍÓÚÝâêîôûÂÊÎÔÛãñõÃÑÕäëïöüÿÄËÏÖÜŸåÅ</p>
    -- 	</body>
    -- 	</html>
    -- 

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @sbHtml
    EXEC @hr = sp_OADestroy @mailman


END
GO