SQL Server

Unpack HTML Email to Files

Unpacks an HTML email into an HTML file and related files (images and style sheets). The links within the HTML are updated to point to the files unpacked and saved to disk. SQL Server

-- 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

    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/happyHour.eml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- Is this an HTML email?
    EXEC sp_OAMethod @email, 'HasHtmlBody', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN

        -- Unpack the HTML to files.  The image and css URLs
        -- in the HTML are modified to point to the files extracted to disk.
        DECLARE @unpackDir nvarchar(4000)
        SELECT @unpackDir = 'qa_output/emails'
        DECLARE @partsSubdir nvarchar(4000)
        SELECT @partsSubdir = 'images'
        DECLARE @htmlFilename nvarchar(4000)
        SELECT @htmlFilename = 'happyHour.html'
        EXEC sp_OAMethod @email, 'UnpackHtml', @success OUT, @unpackDir, @htmlFilename, @partsSubdir
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @email
            RETURN
          END


        PRINT 'Success.'
      END

    EXEC @hr = sp_OADestroy @email


END
GO