SQL Server

Add Image to HTML Email

See more Email Object Examples

Demonstrates how to add an image to an HTML email. 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 @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