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