(SQL Server) Send GMail with BCC Recipients using REST API

See more GMail REST API Examples

Demonstrates how to send an email with BCC recipients using the GMail REST API.

Note: This example requires Chilkat v9.5.0.91 or greater.

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
    DECLARE @iTmp0 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 @success int

    -- Use the Chilkat Email API to create or load an email.
    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

    -- The From name/address don't need to be specified. 
    -- GMail will automatically use your Gmail address if the following 2 lines are omitted.
    EXEC sp_OASetProperty @email, 'FromName', 'My Name'
    EXEC sp_OASetProperty @email, 'FromAddress', 'my_account_name@gmail.com'

    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'John Smith', 'somebody@somewhere.com'
    -- To add more recipients, make additional calls to AddTo or AddCC, one per recipient...

    -- -----------------------------------------------------------------------------------------
    -- This section of this example demonstrates how to add BCC recipients.
    -- 
    -- Technically, an email should never contain a "Bcc" MIME header because it would
    -- effectively nullify the purpose of BCC -- which means "Blind Carbon Copy".
    -- Email recipients should not be able to see the BCC recipients.
    -- Chilkat never adds a "Bcc" header field.
    -- However.. in this case, the GMail REST server will look for the Bcc header field,
    -- and will include those email addresses when sending, and also removes the Bcc header
    -- from the email that is sent.
    -- 
    -- Starting in Chilkat v9.5.0.91, we can tell the email object to add the Bcc header
    -- using the "UseBccHeader" UncommonOptions.  For example:
    EXEC sp_OASetProperty @email, 'UncommonOptions', 'UseBccHeader'
    EXEC sp_OAMethod @email, 'AddBcc', @success OUT, 'Joe', 'joe@example.com'
    EXEC sp_OAMethod @email, 'AddBcc', @success OUT, 'Dave', 'dave@example.com'
    -- ...
    -- -----------------------------------------------------------------------------------------

    EXEC sp_OASetProperty @email, 'Subject', 'This is a test GMail email created using Chilkat.'
    EXEC sp_OASetProperty @email, 'Charset', 'utf-8'
    EXEC sp_OAMethod @email, 'AddPlainTextAlternativeBody', @success OUT, 'This is a test'

    -- Create an HTML email body with an embedded image.
    DECLARE @cid nvarchar(4000)
    EXEC sp_OAMethod @email, 'AddRelatedFile', @cid OUT, 'qa_data/jpg/starfish.jpg'
    DECLARE @sbHtml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHtml OUT

    EXEC sp_OAMethod @sbHtml, 'Append', @success OUT, '<html><body>This is a <b>test</b><br><img src="cid:STARFISH_CID"/></body></html>'
    DECLARE @numReplacements int
    EXEC sp_OAMethod @sbHtml, 'Replace', @numReplacements OUT, 'STARFISH_CID', @cid

    EXEC sp_OAMethod @sbHtml, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @email, 'AddHtmlAlternativeBody', @success OUT, @sTmp0

    -- OK.. we now have an HTML email with an embedded JPG image
    -- We'll need to get the full MIME of the email encoded to a base64url string.
    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 @email, 'GetMimeSb', @success OUT, @sbMime
    EXEC sp_OAMethod @sbMime, 'Encode', @success OUT, 'base64url', 'utf-8'

    -- The body of the HTTP request will be JSON..
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateSb', @success OUT, 'raw', @sbMime

    -- Send the email...
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    EXEC sp_OASetProperty @http, 'AuthToken', 'GMAIL-ACCESS-TOKEN'

    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://www.googleapis.com/gmail/v1/users/me/messages/send'
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, @url, 'application/json', @json
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @sbHtml
        EXEC @hr = sp_OADestroy @sbMime
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- A status code of 200 indicates success.

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response status code: ' + @iTmp0

    PRINT 'Response body:'
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0
    EXEC @hr = sp_OADestroy @resp

    -- The response body contains JSON.
    -- Use the online tool at Generate JSON Parsing Code
    -- to generate JSON parsing code.

    -- A sample successful JSON response:

    -- {
    --  "id": "166f0d4ac39e50bf",
    --  "threadId": "166f0d4ac39e50bf",
    --  "labelIds": [
    --   "SENT"
    --  ]
    -- }

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @sbHtml
    EXEC @hr = sp_OADestroy @sbMime
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @http


END
GO