Custom Mail Merge App
Client Requirements: Word 2016 or greater
Note: It's not possible to do automation with the Click-To-Run Office editions. It's due to the virtualization process used. ref: https://stackoverflow.com/questions/3372394/is-it-possible-to-automate-a-click-to-run-application
- Dec 2018 - One click buttons can now be created
- July 2018 - Microsoft Excel Mail Merge Released - Details below
The mail merge application is available for download from the Accelerator page on My CRM.
This application only runs on windows and requires Microsoft word to be installed on the client.
It registers a URI (custommailmerge:)
This can be used from the BrowserX app or by coding its use into CRM (using client-side script or ASP code)
The Parameter is the full CRM URL
EG http://serverOrIP/CRM/eware.dll/Do?SID=9425127913187&Act=200&Mode=1&CLk=T&Key0=1&Key1=56&Key2=70&T=Company
Example of a full link custommailmerge:http://serverOrIP/CRM/eware.dll/Do?SID=9425127913187&Act=200&Mode=1&CLk=T&Key0=1&Key1=56&Key2=70&T=Company
One click buttons (available from Dec 2018 release 4.7.2.1)
To add a one click button you need to select the method and also get the document ID (Library id and not the name)
Methods
opt_MailMerge opt_EmailAtt opt_MailMergeCsv (excel) opt_DownloadData opt_EmailDocument opt_PrintDocument
opt_MailMerge = Mail Merge
opt_EmailAtt = Email with attachment
opt_MailMergeCsv (excel Merge)
opt_DownloadData = Download the data
opt_EmailDocument = Email with document embedded
opt_PrintDocument = Print the document
Here we have an example of a button for a custom entity being added
Container.AddButton(CRM.Button("Email Merge Doc","edit.gif","custommailmerge:http://crm.server.com/crm/CustomPages/Policy/PolicySummary.asp?mpol_PolicyID="+UseId+"&SID="+_sid+"&F=&J=Policy/PolicySummary.asp&Key0=58&ctfunc=opt_EmailAtt&ctdoc=10167", "Policy", "EDIT"));
You can see the
&ctfunc=opt_EmailAtt&ctdoc=10167
ctdoc=10167 has the document id
With the "Email with attachment" option you can specify a view that has the following fields aliased
customFileName customEmailTemplate customEmailTo customEmailCC customEmailBCC
- New Nov 2020
prependCustomFileName appendCustomFileName
These allow you to keep the file name but add something specific to them
EG
CREATE VIEW vCTProjectMailMerge AS select 'Project_'+Convert(nvarchar,ctpr_CTProjectID) as 'customFileName', 'Project Template' as customEmailTemplate, 'to@test.com' as 'customEmailTo', 'cc@test.com' as 'customEmailCC', 'bcc@test.com' as 'customEmailBCC', CTProject.*, vcommunication.Comm_Description, vcommunication.Comm_Note from CTProject left join vcommunication on ctpr_CTProjectID =Comm_CTProjectId
and these allow you to name the file your own custom name, use a CRM email template and also specify the recipient fields.
This application is the replacement for how you mail merge from the IE toolbar.
One difference is that you can also edit/merge an existing document and re-upload this (as a new record) into CRM.
Mail Merge Application incorporates several other features from the IE toolbar, such as:
- "Email Document" - Immediately process without the option to edit opening an email and embedding the document
- "Email Mail Merge Attachment" - Completes the mail merge and opens an email attaching a PDF of the merged document
- "Print Document" - Immediately print without the option to edit.
- "Download Merge Data" - Allows you to save the data from a view to use when building a new Mail Merge document
Demo video at
Released 31 January 2017 as part of 4.5.3
To add in a Mail Merge button on a custom entity you just add in something like the following to the summary page:
var xurl = "custommailmerge:http://144.76.43.47" + CRM.url("CTProjects/CTProjectsSummary.asp?J=CTProjects/CTProjectsFind.asp&E=CTProjects"); Container.AddButton(CRM.Button("Mail Merge","edit.gif",xurl, "CTProjects", "EDIT"));
see
[hhttps://vimeo.com/209222681 https://vimeo.com/209222681]
Microsoft Excel Mail Merge
So new to Accelerator for Sage CRM(Release 4.7.1.1c 24 July 2017) is the ability to use Microsoft Excel files as templates and merge CRM data to them
In the past Accelerator for Sage CRM has had client-side mail merge to Microsoft Word. Excel itself doesn’t have a mail merge function but the developer working on this was clever enough to use the #prefix_fieldname# (EG #oppo_description#, #comp_name#) to allow you create Excel files as templates and put these tags in which are then replaced with the actual data.
As with the Word merge you can specify any view in the CRM database to be used with a given template so this makes it really flexible.
I should also mention that with the mail merge app you can see existing documents (maybe previously merged and saved to Sage CRM) and get a copy of those and create a new version. So you don’t have to finish a document in one go and can use CRM to store versions of this document.
To merge to line items you create a line item row and then insert a row above and in the first cell enter
TableStart
This line below is used as a template and replicated for each item*.
- the view used against the document must return a line for each item
The code for the buttons is in a file called ct_browserx.js
located on the CRM server at
C:\Program Files (x86)\Sage\CRM\CRM\WWWRoot\js\custom
To uninstall the app delete this file.
Mail Merge on Quotes/Orders
The views used for the quote and order items are
vLineItemsQuote vLineItemsOrder
The item table should only have a header and a line that starts with with of the mail merge tags
<<TableStart:QuoteItems>> or <<TableStart:OrderItems>>
DO not create a 3rd line for the totals as this will break the merge. Instead create a separate tab.
How to iterate through the data rows (where you might be merging to something like a quote)
In the data that is downloaded there is a column called
CRMLASTROW
The last row has a value of 1
So within word you can use the rule
Next Record If
The reason you would need to do this is so you do not go past the last record and you can then display your totals for example.
Notes Form/Dialog
The defaults for this screen are set via the server web.config file
<add key="comm_action" value="LetterOut"/> <add key="comm_status" value="Complete"/> <add key="comm_priority" value="Normal"/>
and also via the specific template setting for
libr_category and libr_type
Coming Dec 2020
Reply/Reply All/Forward with Outlook from within CRM
This functionality requires the view "vCommunicationLibrary" which is in the metadata install. You can manually create this as follows:
CREATE VIEW [dbo].[vCommunicationLibrary] AS SELECT * FROM COMMUNICATION INNER JOIN Library ON libr_communicationid = Comm_CommunicationId WHERE comm_deleted IS NULL and libr_deleted IS NULL GO
and to copy the file
"WWWRoot\CustomPages\SageCRMWS\js\plugins"
to
"WWWRoot\js\custom"
Restarting IIS or recycling the CRM app pool is required for this to be picked up
