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

https://vimeo.com/201688906

---

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.

Excel Mail Merge Demo Video

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"