SQL Server
SQL Server
Using Replace Patterns in Email
See more Email Object Examples
Demonstrates how to use the replace patterns (mail-merge) feature in Chilkat MailMan.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- ---------------------------------------------------------------------
-- Create an email template for sending.
DECLARE @emailTemplate int
EXEC @hr = sp_OACreate 'Chilkat.Email', @emailTemplate OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- We're going to replace "FIRST_NAME" with an actual name.
-- We arbitrarily chose "FIRST_NAME". We can choose anything, such as "CUSTOMER_NAME" or "THE_RECIPIENT_NAME"...
EXEC sp_OASetProperty @emailTemplate, 'Subject', 'Hello FIRST_NAME,'
EXEC sp_OASetProperty @emailTemplate, 'From', 'john@example.com'
EXEC sp_OAMethod @emailTemplate, 'AddTo', @success OUT, 'FIRST_NAME', 'RECIPIENT_EMAIL'
EXEC sp_OAMethod @emailTemplate, 'SetHtmlBody', NULL, '<html><body><h2>Hello FIRST_NAME,</h2><p>Your order for PRODUCT_NAME has been shipped.</p></body></html>'
-- If the email is saved to a file, we can see what it contains:
EXEC sp_OAMethod @emailTemplate, 'SaveEml', @success OUT, 'qa_output/emailTemplate.eml'
-- For example:
-- MIME-Version: 1.0
-- Date: Tue, 26 Apr 2022 07:10:52 -0500
-- Message-ID: <715CF231A9F07B0B9FDB073518CD94138D791866@XYZ>
-- Content-Type: text/html; charset=us-ascii
-- Content-Transfer-Encoding: 7bit
-- X-Priority: 3 (Normal)
-- Subject: Hello FIRST_NAME,
-- From: john@example.com
-- To: FIRST_NAME <RECIPIENT_EMAIL>
--
-- <html><body><h2>Hello FIRST_NAME,</h2><p>Your order for PRODUCT_NAME has been shipped.</p></body></html>
-- ---------------------------------------------------------------------
-- Demonstrate replace patterns by setting and then rendering to MIME.
DECLARE @mailman int
EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
EXEC sp_OAMethod @emailTemplate, 'SetReplacePattern', @success OUT, 'FIRST_NAME', 'Elon'
EXEC sp_OAMethod @emailTemplate, 'SetReplacePattern', @success OUT, 'RECIPIENT_EMAIL', 'elon.musk@example.com'
EXEC sp_OAMethod @emailTemplate, 'SetReplacePattern', @success OUT, 'PRODUCT_NAME', 'Twitter Corporation'
-- Render to MIME to see what we get.
-- Note: When the MailMan sends an email, it renders the email to MIME and then sends.
-- The rendering process is to do replacements, or possibly sign, encrypt, etc.
-- When MailMan.SendEmail is called, internally the email is rendered, and the rendered email is sent.
-- The equivalent to MailMan.Send email is to call email.RenderToMime followed by MailMan.SendMime.
DECLARE @mime nvarchar(4000)
EXEC sp_OAMethod @mailman, 'RenderToMime', @mime OUT, @emailTemplate
PRINT @mime
-- This is the rendered MIME:
-- MIME-Version: 1.0
-- Date: Tue, 26 Apr 2022 07:25:49 -0500
-- Message-ID: <750582BCDC891C67B48CEE2293C08B902C3891E9@XYZ>
-- Content-Type: text/html; charset=us-ascii
-- Content-Transfer-Encoding: 7bit
-- X-Priority: 3 (Normal)
-- Subject: Hello Elon,
-- From: john@example.com
-- To: Elon <elon.musk@example.com>
--
-- <html><body><h2>Hello Elon,</h2><p>Your order for Twitter Corporation has been shipped.</p></body></html>
-- Note: When rendering, the Date and Message-ID headers are automatically regenerated.
-- ---------------------------------------------------------------------
-- An application can see what replacement patterns it previously set by calling SetReplacePattern multiple times.
DECLARE @count int
EXEC sp_OAGetProperty @emailTemplate, 'NumReplacePatterns', @count OUT
PRINT 'Number of replace patterns: ' + @count
DECLARE @i int
SELECT @i = 0
WHILE @i < @count
BEGIN
-- Note: The GetReplaceString method was found to not be working correctly. It was returning the same value as GetReplacePattern.
-- It is fixed in Chilkat v9.5.0.91
EXEC sp_OAMethod @emailTemplate, 'GetReplacePattern', @sTmp0 OUT, @i
EXEC sp_OAMethod @emailTemplate, 'GetReplaceString', @sTmp1 OUT, @i
PRINT @sTmp0 + ': ' + @sTmp1
SELECT @i = @i + 1
END
-- Or lookup a replacement pattern by name:
DECLARE @name nvarchar(4000)
SELECT @name = 'FIRST_NAME'
EXEC sp_OAMethod @emailTemplate, 'GetReplaceString2', @sTmp0 OUT, @name
PRINT @name + ' = ' + @sTmp0
-- Sample output:
-- Number of replace patterns: 3
-- FIRST_NAME: Elon
-- RECIPIENT_EMAIL: elon.musk@example.com
-- PRODUCT_NAME: Twitter Corporation
-- FIRST_NAME = Elon
-- ---------------------------------------------------------------------
-- Finally... demonstrate sending emails using the replacement patterns.
--
-- Set our mail server settings..
EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.mail.us-west-2.awsapps.com'
EXEC sp_OASetProperty @mailman, 'SmtpSsl', 1
EXEC sp_OASetProperty @mailman, 'SmtpPort', 465
EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'john@example.com'
EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'the_password'
-- Imagine we have data in JSON format, and we wish to send the templated email to each recipient...
--
-- {
-- "mail_merge" : [
-- {
-- "to": "mary@example.com",
-- "name": "Mary",
-- "product": "Widget 1"
-- },
-- {
-- "to": "robert@example.com",
-- "name": "Robert",
-- "product": "Widget 2"
-- }
-- ...
-- ]
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/mail_merge.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @json, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @emailTemplate
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @json
RETURN
END
DECLARE @emailAddr nvarchar(4000)
DECLARE @firstName nvarchar(4000)
DECLARE @product nvarchar(4000)
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count OUT, 'mail_merge'
WHILE @i < @count
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @emailAddr OUT, 'mail_merge[i].to'
EXEC sp_OAMethod @json, 'StringOf', @firstName OUT, 'mail_merge[i].name'
EXEC sp_OAMethod @json, 'StringOf', @product OUT, 'mail_merge[i].product'
EXEC sp_OAMethod @emailTemplate, 'SetReplacePattern', @success OUT, 'FIRST_NAME', @firstName
EXEC sp_OAMethod @emailTemplate, 'SetReplacePattern', @success OUT, 'RECIPIENT_EMAIL', @emailAddr
EXEC sp_OAMethod @emailTemplate, 'SetReplacePattern', @success OUT, 'PRODUCT_NAME', @product
EXEC sp_OAMethod @mailman, 'SendEmail', @success OUT, @emailTemplate
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @emailTemplate
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @json
RETURN
END
PRINT 'Send email to ' + @emailAddr
SELECT @i = @i + 1
END
PRINT 'Success.'
EXEC @hr = sp_OADestroy @emailTemplate
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @json
END
GO