SQL Server

Send HTML Email with CSS as Related Item using Content-Location

Demonstrates how to compose an HTML email with an external CSS file included as a related item and referenced by Content-Location.

Some email clients display embedded (related) content best using CID's (Content-IDs), whereas other email clients display related content best by Content-Location. The choice you make may depend on the software used by the intended recipient of your email. (Does' the recipient read email on an iPhone? Android? Outlook? GMail? Thunderbird? etc.)

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

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.

    -- The mailman object is used for sending and receiving email.
    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- 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 embedded CSS'
    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 @sbCss int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbCss OUT

    DECLARE @bCrlf int
    SELECT @bCrlf = 1
    EXEC sp_OAMethod @sbCss, 'AppendLine', @success OUT, 'body {', @bCrlf
    EXEC sp_OAMethod @sbCss, 'AppendLine', @success OUT, '    background-color: powderblue;', @bCrlf
    EXEC sp_OAMethod @sbCss, 'AppendLine', @success OUT, '}', @bCrlf
    EXEC sp_OAMethod @sbCss, 'AppendLine', @success OUT, 'h1 {', @bCrlf
    EXEC sp_OAMethod @sbCss, 'AppendLine', @success OUT, '    color: blue;', @bCrlf
    EXEC sp_OAMethod @sbCss, 'AppendLine', @success OUT, '}', @bCrlf
    EXEC sp_OAMethod @sbCss, 'AppendLine', @success OUT, 'p {', @bCrlf
    EXEC sp_OAMethod @sbCss, 'AppendLine', @success OUT, '    color: red;', @bCrlf
    EXEC sp_OAMethod @sbCss, 'AppendLine', @success OUT, '}', @bCrlf

    -- The filenameInHtml is what should exists within the HTML (in the href atribute)
    DECLARE @filenameInHtml nvarchar(4000)
    SELECT @filenameInHtml = 'styles.css'
    -- Call AddRelatedString2 to use Content-Location.
    EXEC sp_OAMethod @sbCss, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @email, 'AddRelatedString2', NULL, @filenameInHtml, @sTmp0, 'utf-8'

    DECLARE @sbHtml int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHtml OUT

    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '<!DOCTYPE html>', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '<html>', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '<head>', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '  <link rel="stylesheet" href="styles.css">', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '</head>', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '<body>', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '<h1>This is a heading</h1>', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '<p>This is a paragraph.</p>', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '</body>', @bCrlf
    EXEC sp_OAMethod @sbHtml, 'AppendLine', @success OUT, '</html>', @bCrlf

    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 @mailman
    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @sbCss
    EXEC @hr = sp_OADestroy @sbHtml


END
GO