(SQL Server) Get Email HTML Body with Images as Base64 Inline Data

Demonstrates how to get the email's HTML body with referenced MIME parts (i.e. related images) embedded in the HTML as base64 data.

Note: This example requires Chilkat v10.1.2 or later.

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)
    -- ---------------------------------------------------------------------------------------------------------------------------------
    -- Prerequisite Knowledge:

    -- In HTML emails, CID (Content-ID) image references allow embedding images directly into the email as part of the message body, 
    -- instead of linking to external images. 

    -- * Each embedded image is assigned a unique Content-ID (CID) in the email�s MIME structure.
    -- * The CID is a unique identifier, such as image001@domain.com.

    -- For example: <img src="cid:image001@domain.com" alt="Embedded Image">
    -- ---------------------------------------------------------------------------------------------------------------------------------

    -- However, in HTML, you can embed image data directly into an <img> tag by using Base64 encoding. 
    -- This method eliminates the need for external image files by encoding the image as a string and including it within the HTML.

    -- For example:
    -- <img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAUA .... RU5ErkJggg==" alt="Embedded Image">

    -- Chilkat added the GetHtmlBodySb method in version 10.1.2 which provides an option for gathering the 
    -- image data from the mulitpart/related MIME parts of the email, and replaces the "cid" img references with
    -- the base64 data.

    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

    DECLARE @success int
    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/sample.eml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    DECLARE @sbHtml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHtml OUT

    DECLARE @inlineImageData int
    SELECT @inlineImageData = 1
    EXEC sp_OAMethod @email, 'GetHtmlBodySb', @success OUT, @inlineImageData, @sbHtml
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @sbHtml
        RETURN
      END

    -- The HTML with inlined base64 image data is now contained in the StringBuilder.
    DECLARE @htmlStr nvarchar(4000)
    EXEC sp_OAMethod @sbHtml, 'GetAsString', @htmlStr OUT

    EXEC sp_OAMethod @sbHtml, 'WriteFile', @success OUT, 'c:/temp/qa_output/sample.html', 'utf-8', 0


    PRINT 'Success.'

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


END
GO