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

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    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