SQL Server Requires Chilkat v11.0.0+
SQL Server
SendGrid: Send a blind copy (BCC) to an archive mailbox
See more SendGrid Examples
Send the customer a confirmation and give the archive mailbox a blind copy. The customer does not see the BCC address. Put both recipients in the same personalization to share the message. Each address must be unique across To, CC, and BCC within that personalization. Use separate personalizations for messages with different recipient-specific values.
Use an API key with Mail Send permission and a verified sender identity. Replace the example addresses before sending.
SendGrid documentation: How personalizations work.
Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1
-- Supply a Mail Send API key from your application configuration. AuthToken adds the Bearer prefix.
EXEC sp_OASetProperty @http, 'AuthToken', 'SENDGRID_API_KEY'
DECLARE @message int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @message OUT
-- Replace the sender with a verified sender identity and the recipient with your destination.
DECLARE @success int
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'from.email', 'sender@example.com'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'personalizations[0].to[0].email', 'recipient@example.com'
-- Index 0 on both paths puts the visible recipient and blind copy on the same message.
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'personalizations[0].bcc[0].email', 'archive@example.com'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'subject', 'Order confirmation'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'content[0].type', 'text/plain'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'content[0].value', 'We received your order #1042.'
DECLARE @response int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @response OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://api.sendgrid.com/v3/mail/send', @message, 'application/json', @response
-- HttpJson also returns false for HTTP errors. A zero status means no HTTP response arrived.
EXEC sp_OAGetProperty @response, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @response
RETURN
END
EXEC sp_OAGetProperty @response, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 202
BEGIN
EXEC sp_OAGetProperty @response, 'StatusCode', @iTmp0 OUT
PRINT 'Mail Send returned HTTP ' + @iTmp0
-- For validation errors the response body identifies the offending field and explains why.
EXEC sp_OAGetProperty @response, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @response
RETURN
END
PRINT 'SendGrid accepted the email for delivery (HTTP 202).'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @response
END
GO