SQL Server
Send HTML Email with Embedded Image Loaded from Memory

Demonstrates how to compose an HTML email with an embedded image where the image data is loaded directly from memory.
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
    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

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

    -- First load image data into memory.
    -- This example loads image data from a file.
    -- However, your application could get the image data from any other source, such as
    -- from a database.
    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @fac, 'ReadEntireFile', @imageBytes OUT, 'qa_data/jpg/starfish.jpg'

    -- The mailman object is used for sending and receiving email.
    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT

    -- Use your SMTP server hostname.  This example uses office365, but it could be any SMTP server..
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'outlook.office365.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 587
    EXEC sp_OASetProperty @mailman, 'StartTLS', 1

    -- Set the SMTP login/password
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'OFFICE365-SMTP-LOGIN'
    EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'OFFICE365-SMTP-PASSWORD'

    -- Create a new email object
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OASetProperty @email, 'Subject', 'HTML Email with Image Loaded from In-Memory Bytes'
    EXEC sp_OASetProperty @email, 'From', 'Chilkat Support <my-office365-user@mydomain.com>'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Chilkat Support', 'support@chilkatsoft.com'

    DECLARE @contentIdStarfish nvarchar(4000)
    EXEC sp_OAMethod @email, 'AddRelatedData', @contentIdStarfish OUT, 'starfish.jpg', @imageBytes
    EXEC sp_OAGetProperty @email, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- The src attribute for the image tag is set to the contentIdStarfish:
    DECLARE @sbHtml int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHtml OUT

    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '<html><body><p>This is an HTML email with an embedded image.</p>'
    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '<p><img src="cid:CONTENT_ID_STARFISH" /></p></body></html>'
    DECLARE @numReplacements int
    EXEC sp_OAMethod @sbHtml, 'Replace', @numReplacements OUT, 'CONTENT_ID_STARFISH', @contentIdStarfish

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

    EXEC sp_OAMethod @mailman, 'SendEmail', @success OUT, @email
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END
    ELSE
      BEGIN

        PRINT 'Mail Sent!'
      END

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


END
GO