Comment unsuccessful. Please correct the errors below.

Poor man mail merge

Some CRM projects are small and people, small business owners in particular,  are very cautious about the costs. In one of our CRM 3 projects, all the customer wanted was customised quote and order documents, nothing more nothing less. While installed templates are available for customisation, the most common complaint in the newsgroups is inability to add new attributes to the data. With GST legislation in place in Australia, we had to have additional attributes for the quotes and orders to look professional and be legitimate documents at the same time.

CRM 4 seems to resolve the issue, however, there is no simple alternative for CRM 3. If you need a full blown custom mail merge system, look no further than mscrm-addon.com WordMailMerge product. If you're already running Office 2007, Michael Höhne, the unofficial Microsoft CRM "da man", is offering soon-to-be-released document generator based on OpenXML format.

We could not help but wonder how come that Microsoft Excel could consume all possible permutations of filtered views while Microsoft Word remained confined to brain-damaged out-of-the box templates. The plan was simple: control Word through the javascript, create mail merge object, connect it to the data source, run some select statement based on filtered views then merge the data using a template. I knew there were some obstacles to overcome I just did not realise that there will be so many. [more]

Accessing the data

Before even trying to run anything automatically we need to make sure that we can get the data and run mail merge manually. Our aim is to create a merge document for individual entity record (quote in our case) so it's a good idea to prepare some basic parameterised SQL. We are going to use stored procedures though in SQL Server 2005 inline table-valued function is a slightly better choice. Let's start with a very simple select returning list of all quoted product descriptions and some amount values. To keep things simple we use CRM database for now but for customizations to be supported, the final code will have to reside elsewhere. More on that later.


create procedure usp_PrintQuote
(   
    @quoteid uniqueidentifier = null
)
as
    select
    case isproductoverridden
        when 1 then productdescription
        else productidname
    end as productname, priceperunit, quantity, baseamount, tax, extendedamount
    from filteredquotedetail
    where @quoteid is null or quoteid=@quoteid
go

-- quickly test it (quote id is data-specific, use your own)

exec usp_PrintQuote '10368ee8-b5c5-dc11-b7ec-000c296bc4f4'

-- The expected output (your mileage will vary, of course):
-- Toasted Turkish bread w sesame hummus dip    6.00    2.0000000000    12.00    1.20    13.20
-- Smoked salmon feta,capers and sour cream pizza    14.00    1.0000000000    14.00    1.40    15.40

The next step is to  use the above function as a data source for mail merge in Word. Unfortunately, if we run mail merge wizard,  only tables and views are available for selection.

image

To work around this issue, let's use MS Query (c:\Program Files\Microsoft Office\Office12\MSQRY32.EXE for Office 2007). MS Query needs a data source so let's create new data source (say, system one) connected to our CRM database and imaginatively called crm. Then run MS Query and execute the following SQL via File->Execute SQL command against our new data source:


exec usp_PrintQuote '10368ee8-b5c5-dc11-b7ec-000c296bc4f4'

The output should look like the following:

image

Of course, quoteid that we used a parameter is hard-coded and is not very useful but all we are trying to do right now is to get data directly from SQL Server into Word so bear with me for just a few moments.

Let's save the query somewhere, say c:\temp\quote.qry, run mail merge wizard in Word again and select saved query as our mail merge data source:

image 

The good news is that we are getting some data but the bad news is that we lost productname?! WTF?! The reason, as it turns out, is very simple (not counting many hours spent tracking it down, of course). Seems that mail merge in Word, when working with ODBC data sources, is not Unicode-aware. Let's change our SQL as following:


alter procedure usp_PrintQuote
(   
    @quoteid uniqueidentifier = null
)
as
    select
    cast(case isproductoverridden
        when 1 then productdescription
        else productidname
    end as varchar(255)) as productname, priceperunit, quantity, baseamount, tax, extendedamount
    from filteredquotedetail
    where @quoteid is null or quoteid=@quoteid
go

And our "recipient list" magically acquired product names:

image

Now we can insert fields into our template and format the output as required. For testing purposes we can save this template, say, as c:\temp\quote.dot. Don't forget to switch document type to normal Word document prior to saving so that template does not prompt for a data source to run when opened.

I recommend taking a close look at Word templates installed by default as part of the CRM client; they are located in C:\Documents and Settings\<username>\Application Data\Microsoft\Templates\Mail Merge folder. When multi-record template (e.g. quote.dot) is opened, it may look rather strange until you press Alt-F9 to reveal field codes. The trick in these templates is filtering data for the header using built-in MERGESEQ field which equals 1 for the first record being merged and filtering footer using custom LastItem field which would have to come out of the data source as Word does not have built-in support for detecting the last item being merged. We don't have LastItem field yet but it's not important at this stage - we are still trying to make the whole thing work.

Controlling Word

Now that we have Word directly accessing our CRM data, we need to figure out how to perform mail merge programmatically using javascript (after all, we intend to run the code from a CRM form).

The code to make Word to perform mail merge, as it turns out, is not that difficult to create. We simply used macro recorder while performing mail merge and then used generated code as a base. The only difficult part was to figure out correct data source to use when executing MailMerge.OpenDataSource method. Initially I thought that we'd be able to create data source "on the fly" but after numerous "trial & error" code variations and re-using some crumbs of wisdom from the net I've come to the conclusion that the only data source capable of accessing SQL Server data and controllable via code must be DSN-based.

The base code that we can save into a file and use cscript for command-line testing looks like following:


var oWord = new ActiveXObject("Word.Application");
oWord.Visible = true;
var oDoc = oWord.Documents.Add("c:\\temp\\quote.dot");
var conn = "DSN=crm";
var sql = "exec usp_PrintQuote '10368ee8-b5c5-dc11-b7ec-000c296bc4f4'";
var merger = oDoc.MailMerge;
merger.MainDocumentType = 3; //wdCatalog
merger.OpenDataSource("", 0, false, true ,false, false, "", "", true, "", "", conn, sql);
merger.Destination = 0; //wdSendToNewDocument;
merger.Execute(false);
oDoc.Close(0) //discardChanges;
oDoc = null;
oWord.Activate();

It's obvious now why we created stored procedure to pull out data in the first place. SQLStatement parameter of OpenDataSource method is limited to 255 characters and, no matter how hard we tried, we would not be able to extract more than couple fields from CRM.

Putting it all together

Finally, let's put it all together. I assume that reader has some basic knowledge of CRM customizations. Not only it saves me some time because I can skip obvious steps and instructions but it also reduces the risk that someone will apply the code without understanding of underlying mechanics and renders their CRM installation inoperative.

WARNING! The customizations below are unsupported (mostly due to direct modifications of CRM database, see Q&A section below) so use them at your own peril.

  • Download printquote.zip archive containing relevant code and sample template.
  • Locate web directory on your CRM server (default is c:\program files\microsoft crm\crmweb) and create  subfolder ISV\alexacrm. ISV subfolder is used simply to make it compatible with CRM 4 where, according to SDK documentation, all ISV customizations are supposed to reside in this subdirectory. Copy print.qif and quote.dot from the archive into this folder.
  • Create stored procedure by connecting to your CRM database and executing SQL contained in the quote.sql.
  • Open quote entity for customizations and add code from quoteform.js file to onLoad event for quote form.
  • Export ISV.config and add the following Button node for quote entity (you can copy code from isv.config.xml file located in the archive):

<ImportExportXml version="4.0.0.0" languagecode="1033" generatedBy="OnPremise">
  <IsvConfig>
    <configuration version="3.0.0000.0">
      <Entities>
        <Entity name="quote">
          <ToolBar ValidForCreate="0" ValidForUpdate="1">
            <Button Icon="/isv/alexacrm/print.gif" JavaScript="alexaCrmPrintQuote();" Client="Web, Outlook" AvailableOffline="false">
              <Titles>
                <Title LCID="1033" Text="Print Quote" />
              </Titles>
              <ToolTips>
                <ToolTip LCID="1033" Text="Print custom quote" />
              </ToolTips>
            </Button>
          </ToolBar>
        </Entity>
      </Entities>
    </configuration>
  </IsvConfig>
</ImportExportXml>

  • Import modified isv.config xml.
  • Publish customizations.
  • On the client machine create system DSN named crm and point it to your CRM database.
  • In Internet Explorer open Tools->Internet Options->Security. Select zone of your CRM server (usually it's Local intranet or Trusted sites). Click Custom level.
  • Under ActiveX controls and plug-ins section set the sub-section Initialize and script ActiveX controls not marked as safe for scripting to either Enabled or, at very least, Prompt.

Now, when you load quote form in CRM you should see additional Print Quote button:

image

When button is clicked, the quote document will be generated:

image 

Q & A

The most common questions about custom mail merge are below.

What version of XYZ is supported?

Pretty much anything:

  • CRM 3 and CRM 4
  • Word 2003 and 2007
  • SQL Server 2000 and 2005

What about my Unicode?

This is the only area I feel absolutely hapless about. No matter what I've tried, I was not able to pass Unicode data from SQL Server to Word mail merge via ODBC data source. Data in Western European languages seem to be OK as they do not require Unicode but if you happen to use Chinese/Russian/Turkish/<insert your favourite language here>, look elsewhere for your mail merge needs.

CRM 4 is available so why bother at all?

Granted, CRM 4 already contains reasonable mail merge implementation that allows some customizations, however, the list of supported entities is extremely limited. Also, there are plenty CRM 3 implementations out there which are yet to be migrated.

The approach above also has one significant advantage over all other mail merge methods. Because the data for mail merge are coming from the stored procedure, we are not limited to the attributes of underlying entities. For example, it's possible to do some additional calculations, group quote lines based on the product type attribute (e.g. sales inventory and services separately), introduce subtotals, etc.

Is it possible to use it for hosted or Live CRM?

No, I don't think so since we need to be able to build DSN source connecting directly to SQL Server database which is not possible in a hosted solution.

Is it possible to make customizations above supported?

Absolutely. As published above, customizations are unsupported, however, it is not that difficult to make them comply with the supportability requirements.

  • Move stored procedure into a separate database. The only challenge with this move is to correctly configure permissions on this database to ensure that user is bound by the CRM permissions. Typical approach is to grant execute permissions on the stored procedure to ReportingGroup {guid} group and change from clause in select statement:

from
    OrgName_MSCRM..filteredquote q
    inner join OrgName_MSCRM..filteredquotedetail qd on q.quoteid=qd.quoteid

  • Change DSN on client's machine to point to the database above. 
  • Move button image and Word template(s) into either separate web site or virtual directory under CRM. The main idea is that custom files should not reside under physical structure of CRM server.

What about offline?

I don't think that it's that hard to make mail merge work in offline mode either. The outline of the approach is below, however, I have not tested it.

  • Distribute Word templates to end-users machines that have offline client installed.
  • On local SQL Server create additional database for stored procedure repository and re-create stored procedure(s) for printing using tables as data source instead of FilteredXXX views.
  • Create additional DSN data source pointing to the database above.
  • Adjust isv.config.xml to allow offline client by setting AvailableOffline attribute to "true".
  • Modify printing script to detect offline mode using IsOnline method.
  • Adjust Word template(s) location in script to point to a local template when client is offline. In CRM 4 it's possible to to use SERVER_URL global variable to make template location server-independent.
  • Adjust data source in script as required.

That's all. Enjoy. Feedback and comments are welcome.


Download: printquote.zip (13.50 kb)

 

Posted by: George Doubinski
Last revised: 05 Dec, 2012 05:26 PM

Comments

Faisal Fiaz
Faisal Fiaz
06 Feb, 2008 12:08 AM

A very good solution. I have tried and it worked perfectly. One question is that is it possible to have more than one child entity like quotedetails and use this mail merge? How we are going to make quote.dot document?

06 Feb, 2008 09:45 PM

Faisal,

not sure I fully understand the question. Of course, it's possible to include other 1-to-many views into the select statement. Keep in mind, however, that Word would like to have a single tabular data source which effectively limits you to a single resultset. If you want include "independent" tables, e.g. quotedetails and, say, activities and show them in a dopcument in two separate tables then the only solution would be to create a cross-join including all possible combinations of the rows, add some pseudo attributes helping tp identify the rows (similar to "last" attribute in a sample) and then be [b]very[/b] creative in a template using conditional fields. Sounds like more trouble than it's worth and I'd be seriously considering reporting services as an alternative in this scenario.

-- georged

Your Comments

Comment unsuccessful. Please correct the errors below.
Used for your gravatar. Not required. Will not be public.
Posting code? Indent it by four spaces to make it look nice. Learn more about Markdown.

Preview