|
Chilkat • HOME • .NET Core C# • Android™ • AutoIt • C • C# • C++ • Chilkat2-Python • CkPython • Classic ASP • DataFlex • Delphi ActiveX • Delphi DLL • Go • Java • Lianja • Mono C# • Node.js • Objective-C • PHP ActiveX • PHP Extension • Perl • PowerBuilder • PowerShell • PureBasic • Ruby • SQL Server • Swift 2 • Swift 3,4,5... • Tcl • Unicode C • Unicode C++ • VB.NET • VBScript • Visual Basic 6.0 • Visual FoxPro • Xojo Plugin
(SQL Server) Using Replace Patterns in EmailSee more Email Object ExamplesDemonstrates how to use the replace patterns (mail-merge) feature in Chilkat MailMan.
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls. -- CREATE PROCEDURE ChilkatSample AS BEGIN DECLARE @hr int -- Important: Do not use nvarchar(max). See the warning about using nvarchar(max). DECLARE @sTmp0 nvarchar(4000) DECLARE @sTmp1 nvarchar(4000) -- This example requires the Chilkat API to have been previously unlocked. -- See Global Unlock Sample for sample code. DECLARE @success int -- --------------------------------------------------------------------- -- Create an email template for sending. DECLARE @emailTemplate int -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0 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 -- CKX-Bounce-Address: 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> -- Note: Ignore the CKX-Bounce-Address header. It is automatically removed by Chilkat prior to sending. -- All "CKX-" headers are automatically removed prior to sending. -- --------------------------------------------------------------------- -- Demonstrate replace patterns by setting and then rendering to MIME. DECLARE @mailman int -- Use "Chilkat_9_5_0.MailMan" for versions of Chilkat < 10.0.0 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 -- 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, '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 |
||||
© 2000-2024 Chilkat Software, Inc. All Rights Reserved.