(SQL Server) Send Email with Multiple Reply-To Addresses

Send email with multiple repy-to addresses.

Note: Some mail servers will remove the extra email addresses from the Reply-To header. Even if you provide multiple reply-to addresses, the email may arrive with only the 1st.

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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @mailman int
    -- Use "Chilkat_9_5_0.MailMan" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.my_mail_server.com'
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'myUsername'
    EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'myPassword'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 465
    EXEC sp_OASetProperty @mailman, 'SmtpSsl', 1

    -- Create a new email object
    DECLARE @email int
    -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OASetProperty @email, 'Subject', 'This is a test'
    EXEC sp_OASetProperty @email, 'Body', 'This is a test'
    EXEC sp_OASetProperty @email, 'From', 'Joe <joe@example.com>'
    DECLARE @success int
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Mary', 'mary@example2.com'

    -- Specify a single reply-to address, which will get replace with a list of addresses..
    EXEC sp_OASetProperty @email, 'ReplyTo', 'placeholder@example.com'

    -- Get the email as MIME.
    DECLARE @sbMime int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbMime OUT

    EXEC sp_OAMethod @mailman, 'RenderToMimeSb', @success OUT, @email, @sbMime

    -- Update the Reply-To MIME header with a list of email addresses.
    DECLARE @replyToAddrs nvarchar(4000)
    SELECT @replyToAddrs = 'joe@example.com, mike@example.com'
    EXEC sp_OAMethod @sbMime, 'ReplaceAllBetween', @success OUT, 'Reply-To: ', CHAR(13) + CHAR(10), @replyToAddrs, 0

    -- Examine the MIME to be sent:
    EXEC sp_OAMethod @sbMime, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Here's the MIME:

    -- MIME-Version: 1.0
    -- Date: Tue, 03 Sep 2024 08:18:12 -0500
    -- Message-ID: <D892B0E563A7A13B1F499530DE21529714EA479A@SLICE>
    -- Content-Type: text/plain; charset=us-ascii; format=flowed
    -- Content-Transfer-Encoding: 7bit
    -- X-Priority: 3 (Normal)
    -- Subject: This is a test
    -- From: Joe <joe@example.com>
    -- To: Mary <mary@example2.com>
    -- Reply-To: joe@example.com, mike@example.com
    -- 
    -- This is a test

    -- ---------
    -- Send the MIME...
    EXEC sp_OAMethod @sbMime, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @mailman, 'SendMime', @success OUT, 'joe@example.com', 'mary@example2.com', @sTmp0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @sbMime
        RETURN
      END

    EXEC sp_OAMethod @mailman, 'CloseSmtpConnection', @success OUT
    IF @success = 0
      BEGIN

        PRINT 'Connection to SMTP server not closed cleanly.'
      END


    PRINT 'Mail Sent!'

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


END
GO