SQL Server

Create Complex Email

This is an example showing how to create an email that has file attachments, contains both plain-text and HTML alternative bodies, and has embedded images within the HTML. The email is saved to a .eml file, which is a text file and can be viewed in any text editor. EML is nothing more than the MIME source of an email. In this example, you'll see that the MIME is structured in three layers of multipart content types. The outermost layer is multipart/mixed. This is typically where you'll find file attachments and attached messages. Underneath the multipart/mixed is a multipart/related envelope. Within this you'll find the "related" items -- which are the items belonging to the HTML body (images and style sheets). Underneath the multipart/related you'll find the multipart/alternative, and this contains the text/plain and text/html alternative bodies.

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
    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.

    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Adding attachments, HTML/plain-text bodies, etc can be done
    -- in any order:

    -- Add an attachment
    DECLARE @contentType nvarchar(4000)
    EXEC sp_OAMethod @email, 'AddFileAttachment', @contentType OUT, 'hamlet.zip'
    EXEC sp_OAGetProperty @email, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- Add some headers:
    EXEC sp_OASetProperty @email, 'Subject', 'This is a complex email'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Chilkat Support', 'support@chilkatsoft.com'
    EXEC sp_OASetProperty @email, 'From', 'Matt <matt@chilkatsoft.com>'

    -- Add a plain-text body:
    EXEC sp_OAMethod @email, 'AddPlainTextAlternativeBody', @success OUT, 'This is the plain-text body'

    -- Add an image that will be embedded in the HTML body.
    DECLARE @contentIdDude nvarchar(4000)
    EXEC sp_OAMethod @email, 'AddRelatedFile', @contentIdDude OUT, 'dude.gif'
    EXEC sp_OAGetProperty @email, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END
    -- Update the CONTENT_ID_DUDE to the actual content ID when sending the email.
    EXEC sp_OAMethod @email, 'SetReplacePattern', @success OUT, 'CONTENT_ID_DUDE', @contentIdDude

    -- Add an HTML body:
    DECLARE @html nvarchar(4000)

    SELECT @html = '<html><body><b>This is the HTML body</b><br><img src="cid:CONTENT_ID_DUDE"></body></html>'

    EXEC sp_OAMethod @email, 'AddHtmlAlternativeBody', @success OUT, @html

    -- Save the email as a .eml

    EXEC sp_OAMethod @email, 'SaveEml', @success OUT, 'complex.eml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END
    ELSE
      BEGIN

        PRINT 'Created email!'
      END

    EXEC @hr = sp_OADestroy @email


END
GO