Custom Mail Merge App

From Accelerator for SageCRM
Revision as of 13:03, 28 March 2023 by Sagecrmw (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Upgrading to 5.1?

The files for the previous version must be manually deleted. These files are installed in the "...\WWWRoot\js\custom" folder here are 2 files 

 ct_browserx.css 
 ct_browserx.js

Delete these files and restart IIS or recycle the CRM app pool.

The new file is called ct_init.js

A single button now appears and you click this to see the options.

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

Install the client

  • The client install requires that the user has admin access. This is needed as we register a URI (custommailmerge) that opens the windows app.

Download the app

If you do not have permissions and try install you will see an error like this

If you see this cancel out of the install and open the folder for the installer and right click on the exe and select "Run as Administrator"

Then select "No" from the prompt

Complete the install

2021 Auto updates From version 5004 on updates to the client app will happen just like on your phone apps.

When a user opens the app they will see an "Update Available" message.

Click on the text to open the download dialog

Click download and Install and follow on screen instructions to update your client.

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


Version 5.0 and earlier

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 either 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.

Also see https://public.3.basecamp.com/p/DxDbEJVKJgiegUGBtxzX9vvi

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

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\ct_sendWithOutlook.js"

to 

 "WWWRoot\js\custom"

Restarting IIS or recycling the CRM app pool is required for this to be picked up

Quotes items not merging - toggle codes

This can be due to how the codes display

For example if they look like this 

  «quit_status»

then check the box in the "Options" area in Word

and close Word and try again

From April 2023 release of the client the mail merge log fies will be 

  C:\Users\USERNAME\AppData\Roaming\CRM Together\CustomMailMerge\logs

where USERNAME is the name of the windows user

From April 2023 release

Open 

 files://

links.

If you have a "file://" link in your system you may notice that this only works with IE (which is retired). So as a work-around for this we added in the ability to cope with 

 files://

(note the "s" as "file:" is reserved)

Retrieved from "https://accelerator.crmtogether.com/index.php?title=Custom_Mail_Merge_App&oldid=2137"