SQL Server

Auto-Generate or Explicitly Specify the Message-ID Email Header

Demonstrates how to auto-generate or explicitly specify the Message-ID header field. 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 @success int
    SELECT @success = 0

    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, 'Subject', 'test'
    EXEC sp_OASetProperty @email, 'Body', 'test'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Joe', 'joe@example1.com'
    EXEC sp_OASetProperty @email, 'From', 'mary@example2.com'

    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT

    -- When the mailman sends email, it renders the email to be sent,
    -- and then sends the rendered MIME.

    -- Instead of actually sending the email, this example will render
    -- to MIME so we can see what would be sent..

    -- We can explicitly set the Message-ID like this:
    EXEC sp_OASetProperty @mailman, 'AutoGenMessageId', 0
    EXEC sp_OAMethod @email, 'AddHeaderField', NULL, 'Message-ID', '<xyz123@abc>'

    DECLARE @mimeStr nvarchar(4000)
    EXEC sp_OAMethod @mailman, 'RenderToMime', @mimeStr OUT, @email

    PRINT @mimeStr

    -- This is what we get:

    -- MIME-Version: 1.0
    -- Date: Mon, 20 Jul 2020 17:20:09 -0500
    -- Message-ID: <xyz123@abc>
    -- Content-Type: text/plain; charset=us-ascii; format=flowed
    -- Content-Transfer-Encoding: 7bit
    -- X-Priority: 3 (Normal)
    -- Subject: test
    -- To: Joe <joe@example1.com>
    -- From: mary@example2.com
    -- 
    -- test


    PRINT '--------'

    -- If we auto-generate...
    EXEC sp_OASetProperty @mailman, 'AutoGenMessageId', 1
    EXEC sp_OAMethod @mailman, 'RenderToMime', @mimeStr OUT, @email

    PRINT @mimeStr

    -- We get this:

    -- MIME-Version: 1.0
    -- Date: Mon, 20 Jul 2020 17:21:20 -0500
    -- Message-ID: <04EDE096BBFE484967F6398A973B0FCF6EA09A44@CHILKATSLICE>
    -- Content-Type: text/plain; charset=us-ascii; format=flowed
    -- Content-Transfer-Encoding: 7bit
    -- X-Priority: 3 (Normal)
    -- Subject: test
    -- To: Joe <joe@example1.com>
    -- From: mary@example2.com
    -- 
    -- test

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @mailman


END
GO