SQL Server

Example to Add Several BCC Email Recipients

See more Email Object Examples

Demonstrates how to call AddBCC once per email recipient. 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    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 email subject'
    EXEC sp_OASetProperty @email, 'Body', 'Test email body'

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

    -- Add a primary TO recipient
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Elizabeth', 'elizabeth@example.com'

    -- Add a CC recipient.
    EXEC sp_OAMethod @email, 'AddCC', @success OUT, 'Jerry', 'jerry@example.com'

    -- Add 2 BCC recipients.
    EXEC sp_OAMethod @email, 'AddBcc', @success OUT, 'Mira', 'mira@example.com'
    EXEC sp_OAMethod @email, 'AddBcc', @success OUT, 'Alexander', 'alex@example.com'

    -- BCC recipients will not be present in the MIME of the email that is sent.
    -- However, the BCC recipients are included in the recipients provided to the SMTP server
    -- during the SMTP protocol conversation. This will be shown below.

    -- First, let's look at the MIME.
    EXEC sp_OAMethod @email, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- MIME-Version: 1.0
    -- Date: Sat, 30 Oct 2021 07:58:32 -0500
    -- Message-ID: <183D5F51163BE4374B494D2ED067DE2783CC761B@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>
    -- To: Elizabeth <elizabeth@example.com>
    -- Cc: Jerry <jerry@example.com>
    -- 
    -- Test email body

    -- Notice that the BCC recipients do not appear in the MIME.  But don't worry, they are still present within the email object.
    -- The difference between CC and BCC recipients is that CC recipients appear in the MIME.  Thus, the recipient of the email
    -- can see the CC recipients, but cannot see and is unaware of the BCC recipients.

    -- ----------------------------------------------------------------------------------------------
    -- Let's send the above email and examine the SMTP session log to show that the BCC recipients also receive the email.

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

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

    EXEC sp_OAMethod @mailman, 'SendEmail', @success OUT, @email
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @mailman
        RETURN
      END

    EXEC sp_OAMethod @mailman, 'CloseSmtpConnection', @success OUT
    IF @success <> 1
      BEGIN

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


    PRINT 'Mail Sent!'

    -- Examine the SMTP session log.
    EXEC sp_OAGetProperty @mailman, 'SmtpSessionLog', @sTmp0 OUT
    PRINT @sTmp0

    -- Here you can see the session log and the BCC email addresses included in the "RCPT TO" commands.

    -- 220 smtp.example.com ESMTP Example SMTP Service
    -- EHLO SLICE<CRLF>
    -- 250-smtp.example.com
    -- 250-8BITMIME
    -- 250-AUTH PLAIN LOGIN
    -- 250 Ok
    -- AUTH LOGIN<CRLF>
    -- 334 VXNlcm5hbWU6
    -- ***<CRLF>
    -- 334 UGFzc3dvcmQ6
    -- {PasswordOrCredentials}
    -- 235 Authentication successful.
    -- MAIL FROM:<joe@example.com><CRLF>
    -- 250 Ok
    -- RCPT TO:<elizabeth@example.com><CRLF>
    -- 250 Ok
    -- RCPT TO:<jerry@example.com><CRLF>
    -- 250 Ok
    -- RCPT TO:<mira@example.com><CRLF>
    -- 250 Ok
    -- RCPT TO:<alex@example.com><CRLF>
    -- 250 Ok
    -- DATA<CRLF>
    -- 354 End data with <CR><LF>.<CR><LF>
    -- {388 bytes}
    -- <CRLF>.<CRLF>
    -- 250 Ok
    -- QUIT<CRLF>
    -- 221 Bye

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


END
GO