Custom Mail Merge App: Difference between revisions

From Accelerator for SageCRM
No edit summary
No edit summary
(21 intermediate revisions by the same user not shown)
Line 1: Line 1:
'''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.
[[File:crmtogetherbutton2022.png]]
----
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
** Dec 2018 -  One click buttons can now be created
Line 4: Line 24:


----
----
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
[[File:custommailmerge download.png]]
If you do not have permissions and try install you will see an error like this
[[File:custommailmerge install error.png]]
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
[[File:custommailmerge no.png]]
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.
[[File:Mailmergeupdateavaila1.png]]


Click on the text to open the download dialog
[[File:Mailmergeupdateavaila2.png]]
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.
The mail merge application is available for download from the Accelerator page on My CRM.
Line 22: Line 76:
   custommailmerge:http://serverOrIP/CRM/eware.dll/Do?SID=9425127913187&Act=200&Mode=1&CLk=T&Key0=1&Key1=56&Key2=70&T=Company
   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)
One click buttons (available from Dec 2018 release 4.7.2.1)
Line 52: Line 108:


ctdoc=10167 has the document id
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.


----
----
Line 112: Line 195:


----
----
'''Version 5.0 and earlier'''
The code for the buttons is in a file called ct_browserx.js
The code for the buttons is in a file called ct_browserx.js


Line 118: Line 204:
   C:\Program Files (x86)\Sage\CRM\CRM\WWWRoot\js\custom
   C:\Program Files (x86)\Sage\CRM\CRM\WWWRoot\js\custom


'''To uninstall the app delete this file.'''


----
----
Line 128: Line 215:
   vLineItemsOrder
   vLineItemsOrder


The item table should only have a header and a line that starts with with of the mail merge tags
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>>
   <<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.
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
[[File:next_record_if.png]]
[[File:next_record_if2.png]]
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
[[File:notesform.png]]
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
[[File:emailbuttons.jpg]]
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
[[File:word toggle codes.png]]
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

Revision as of 14:20, 7 March 2023

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