SQL Server
Add Image to HTML Email

Demonstrates how to add an image to an HTML email.
Chilkat SQL Server Downloads

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    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_OASetProperty @email, 'From', 'Matt <matt@example.com>'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Joe', 'joe@example.com'
    EXEC sp_OASetProperty @email, 'Subject', 'This is a test'

    -- Add an HTML body with an image tag.
    DECLARE @html nvarchar(4000)
    SELECT @html = '<html><body><p>Test with image:</p><p><img src="/wildlife/starfish.jpg" /></p></body></html>'
    EXEC sp_OAMethod @email, 'SetHtmlBody', NULL, @html

    -- Add the image data from a file.  The pathInHtml must match the value of the img tag's src attribute.
    DECLARE @pathInHtml nvarchar(4000)
    SELECT @pathInHtml = '/wildlife/starfish.jpg'
    -- The actual path of the JPG file in the local filesystem:
    DECLARE @pathOnDisk nvarchar(4000)
    SELECT @pathOnDisk = 'qa_data/jpg/starfish20.jpg'

    EXEC sp_OAMethod @email, 'AddRelatedFile2', @success OUT, @pathOnDisk, @pathInHtml
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- The email is now properly constructed with an image that will be visible in the HTML.
    -- In this example, we'll save it to a .eml file and verify by loading the .eml in an email client such as Mozilla Thunderbird.

    EXEC sp_OAMethod @email, 'SaveEml', @success OUT, 'qa_output/starfish.eml'

    -- Also, we can examine the MIME to understand how the image data in the MIME is associated with the img tag in the HTML.
    -- It is the Content-Location header value that must match the value of the img tag's src attribute.

    EXEC sp_OAMethod @email, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- Here's the MIME:

    -- MIME-Version: 1.0
    -- Date: Thu, 26 May 2022 11:09:12 -0500
    -- Message-ID: <B1D499F74F533E287BFE04BBD79AAF1D973B61E3@SLICE>
    -- Content-Type: multipart/related; boundary="------------000205040704010904010706"
    -- X-Priority: 3 (Normal)
    -- From: Matt <matt@example.com>
    -- To: Joe <joe@example.com>
    -- Subject: This is a test
    -- 
    -- --------------000205040704010904010706
    -- Content-Type: text/html; charset=us-ascii
    -- Content-Transfer-Encoding: 7bit
    -- 
    -- <html><body><p>Test with image:</p><p><img src="/wildlife/starfish.jpg" /></p></body></html>
    -- --------------000205040704010904010706
    -- Content-Type: image/jpeg; name="/wildlife/starfish.jpg"
    -- Content-Transfer-Encoding: base64
    -- Content-Disposition: inline; filename="/wildlife/starfish.jpg"
    -- Content-Location: /wildlife/starfish.jpg
    -- 
    -- /9j/4AAQSkZJRgABAQEASABIAAD//gAmRmlsZSB3cml0dGVuIGJ5IEFkb2JlIFBob3Rvc2hvcD8g
    -- NC4w/9sAQwAQCwwODAoQDg0OEhEQExgoGhgWFhgxIyUdKDozPTw5Mzg3QEhcTkBEV0U3OFBtUVdf
    -- YmdoZz5NcXlwZHhcZWdj/9sAQwEREhIYFRgvGhovY0I4QmNjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj
    -- Y2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj/8IAEQgAFAAUAwERAAIRAQMRAf/EABcAAAMBAAAA
    -- AAAAAAAAAAAAAAIDBAX/xAAYAQADAQEAAAAAAAAAAAAAAAABAgMEAP/aAAwDAQACEAMQAAAB2kZY
    -- NNEijWKddfTmLgALWH//xAAbEAACAgMBAAAAAAAAAAAAAAABAgMRAAQSE//aAAgBAQABBQL0XqN+
    -- pM2aqJGMiqFFCyg7z//EABwRAAICAgMAAAAAAAAAAAAAAAERAAIQIQMSUf/aAAgBAwEBPwHqU5aq
    -- Axx+y1tMQl4elj//xAAcEQEAAQUBAQAAAAAAAAAAAAABEQACEBIhA1H/2gAIAQIBAT8B3Bhqy7Zc
    -- enyiwmGgDhiOzj//xAAdEAABAwUBAAAAAAAAAAAAAAABAAIREBIhIkFR/9oACAEBAAY/ArZyn+Cg
    -- xtxWuJaoCnqDuin/xAAcEAABBAMBAAAAAAAAAAAAAAABABEhYRAxQVH/2gAIAQEAAT8hkEwPUUR9
    -- DYfE4nxtRpIkBTsayuALIiuY/9oADAMBAAIAAwAAABDWPTsf/8QAGhEAAwADAQAAAAAAAAAAAAAA
    -- AAEREDFBIf/aAAgBAwEBPxC0DVPcWm+Ce4OesrkE6bjH/8QAGBEBAQEBAQAAAAAAAAAAAAAAAREA
    -- QRD/2gAIAQIBAT8QahMiOc8YgSrnTY3ELclHXn//xAAcEAEBAAIDAQEAAAAAAAAAAAABEQAhMUFx
    -- EFH/2gAIAQEAAT8Qn3igmSZSj+c4N4zapMy9IjFV98wncN2iuLFsCEbDGxQkI6RO/n//2Q==
    -- 
    -- --------------000205040704010904010706--

    EXEC @hr = sp_OADestroy @email


END
GO