(SQL Server) Send HTML Email with Image Downloaded from URL

Demonstrates how to compose an HTML email with an embedded image where the image data is downloaded from a URL.

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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- First download the image we'll be adding to the HTML "img" tag.
    DECLARE @bdJpg int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdJpg OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetBd', @success OUT, 'https://www.chilkatsoft.com/images/starfish.jpg', @bdJpg
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @bdJpg
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

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

    -- Use your SMTP server..
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.yourserver.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 587
    EXEC sp_OASetProperty @mailman, 'StartTLS', 1

    -- Set the SMTP login/password
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'my_login'
    EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'my_password'

    -- Create an HTML email.
    DECLARE @email int
    -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OASetProperty @email, 'Subject', 'HTML Email with Image'
    EXEC sp_OASetProperty @email, 'From', 'Dave <somebody@mydomain.com>'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Chilkat', 'info@chilkatsoft.com'

    DECLARE @html nvarchar(4000)
    SELECT @html = '<html><body><p>This is an HTML email with an embedded image.</p><p><img src="starfish.jpg" /></p></body></html>'
    EXEC sp_OAMethod @email, 'SetHtmlBody', NULL, @html

    -- Note: The "starfish.jpg" here must match the name in the "img" tag's "src" attribute in the HTML above.
    EXEC sp_OAMethod @email, 'AddRelatedBd2', @success OUT, @bdJpg, 'starfish.jpg'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @bdJpg
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

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

    -- success = mailman.SendEmail(email);
    -- if (success == ckfalse) {
    --     println mailman.LastErrorText;
    --     return;
    -- }
    -- 
    -- ignore = mailman.CloseSmtpConnection();


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @bdJpg
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @email


END
GO