SQL Server Requires Chilkat v11.0.0+
SQL Server
SendGrid: Embed an inline image in an HTML email
See more SendGrid Examples
Read inspection.jpg from the working directory and send it with an HTML inspection report. The image travels with the message: its content_id matches the cid: reference in the HTML. Supply plain text first and include useful alt text so the result is understandable even if the mail client hides images.
Use an API key with Mail Send permission and a verified sender identity. Replace the example addresses before sending.
SendGrid documentation: Mail Send attachment fields.
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'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'subject', 'Inspection photo'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'content[0].type', 'text/plain'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'content[0].value', 'The inspection is complete. The photo shows the repaired hinge.'
-- SendGrid expects the plain text alternative before HTML.
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'content[1].type', 'text/html'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'content[1].value', '<p>The inspection is complete. The photo shows the repaired hinge.</p><img src="cid:hinge-photo" alt="Repaired hinge">'
DECLARE @fileData int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @fileData OUT
DECLARE @loaded int
EXEC sp_OAMethod @fileData, 'LoadFile', @loaded OUT, 'inspection.jpg'
IF @loaded <> 1
BEGIN
PRINT 'Could not read inspection.jpg. Check the path and file permissions.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @fileData
RETURN
END
EXEC sp_OAGetProperty @fileData, 'NumBytes', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'inspection.jpg is empty.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @fileData
RETURN
END
-- Encode the file bytes once. The content field contains only Base64, without a data URI prefix.
EXEC sp_OAMethod @fileData, 'GetEncoded', @sTmp0 OUT, 'base64'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'attachments[0].content', @sTmp0
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'attachments[0].type', 'image/jpeg'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'attachments[0].filename', 'inspection.jpg'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'attachments[0].disposition', 'inline'
EXEC sp_OAMethod @message, 'UpdateString', @success OUT, 'attachments[0].content_id', 'hinge-photo'
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 @fileData
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 @fileData
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 @fileData
EXEC @hr = sp_OADestroy @response
END
GO