(SQL Server) Example to Add Several TO Email Recipients

See more Email Object Examples

Demonstrates how to call AddTo once per email recipient.

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)
    DECLARE @email int
    -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0
    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 email subject'
    EXEC sp_OASetProperty @email, 'Body', 'Test email body'

    EXEC sp_OASetProperty @email, 'From', 'Test <joe@example.com>'

    -- Add 3 TO recipients.
    DECLARE @success int
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Jerry', 'jerry@example.com'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Mira', 'mira@example.com'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Alexander', 'alex@example.com'

    -- You can see the resultant email:

    EXEC sp_OAMethod @email, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- MIME-Version: 1.0
    -- Date: Sat, 30 Oct 2021 07:32:18 -0500
    -- Message-ID: <842BF50054133D93F84848CA3CED74DD8C7E5DA2@SLICE>
    -- Content-Type: text/plain; charset=us-ascii; format=flowed
    -- Content-Transfer-Encoding: 7bit
    -- X-Priority: 3 (Normal)
    -- Subject: Test email subject
    -- From: Test <joe@example.com>
    -- CKX-Bounce-Address: joe@example.com
    -- To: Jerry <jerry@example.com>, Mira <mira@example.com>,
    --  Alexander <alex@example.com>
    -- 
    -- Test email body
    -- 

    -- Note: You may notice the "CKX-Bounce-Address" header.  This is a header that appears in the MIME
    -- but is automatically removed when a Chilkat method to send email is called.
    -- Chilkat automatically removes all headers beginning with "CKX-" when sending email.

    EXEC @hr = sp_OADestroy @email


END
GO