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

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


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:


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:


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

And our "recipient list" magically acquired product names:


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\ 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. 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\\");
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;
oDoc.Close(0) //discardChanges;
oDoc = null;

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 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 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="" languagecode="1033" generatedBy="OnPremise">
    <configuration version="3.0.0000.0">
        <Entity name="quote">
          <ToolBar ValidForCreate="0" ValidForUpdate="1">
            <Button Icon="/isv/alexacrm/print.gif" JavaScript="alexaCrmPrintQuote();" Client="Web, Outlook" AvailableOffline="false">
                <Title LCID="1033" Text="Print Quote" />
                <ToolTip LCID="1033" Text="Print custom quote" />

  • 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:


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


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:

    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: (13.50 kb)


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


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 document?

06 Feb, 2008 09:45 PM


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

Isabel J. Starnes
Isabel J. Starnes
03 Aug, 2016 02:27 PM

Shhhh, Kelly, shhh. Those kinds of clues should be tought on paid workouts otherwise everyone will turn their jobs in a dream-one and then just think... think who will work as a janitor in schools, who will pour in oil in your car in the gas station? So here is clue for you - open your own creative tutor class and lay back at your seat to enjoy

08 Nov, 2016 04:00 AM

Your topic of discussion is Poor man mail merge and the full detail about this topic with involvement of some softwear as well. We can get the explanation of that softwear here. However, I'm finding bestdissertation from almost similar website which have impression a good team working behind it.

20 Feb, 2017 10:53 AM

While you are looking for an authority throughout web design plans, on the list of things you will need to take into account is the podium that you might want your internet site being built on. Most of the time, the CMS can be the most appropriate alternative, and also buy mba assignment online the most popular CMS can be WordPress. WordPress along with other CMSs have got numerous rewards with regard to small business web sites. Nonetheless, just about the most important benefits of WordPress can be we now have several extensions accessible which you'll want to include aimed at your website to enhance their features

25 Apr, 2017 05:36 AM

Get Writer Help UK is an amazing online platform that offers the convenient solutions, and interesting topics for all types of course writing to make it joyful for students. online coursework writer

30 Apr, 2017 10:13 AM

We really wanted to think about why that Microsoft Excel could expend every single conceivable change of separated perspectives while Microsoft Word stayed kept to mind harmed out-of-the case formats. The arrangement was straightforward: control Word through the javascript, make mail blend protest, associate it to the information source, run some select explanation in light of separated perspectives then union the information utilizing a format. Power Point Presentation-DoneDissertation

04 May, 2017 05:49 AM

You have written very informative article about CRM projects for small business owners. The information you have posted is good to read,i am impressed to see your work. buy online Garage Shelving from

Shaun Tait
Shaun Tait
04 May, 2017 07:29 AM

While you are searching for an expert all through website architecture arranges, on the rundown of things you should consider is the platform that you may need your web webpage being based on. More often than not, the CMS can be the most suitable option, and furthermore the most prominent CMS can be WordPress. WordPress alongside different CMSs have various prizes as to private venture sites. In any case, pretty much the most vital advantages of WordPress can be we now have a few expansions open which you'll need to incorporate gone for your site to improve their components 500 Word Essay

andrea rose
andrea rose
29 Aug, 2017 11:15 AM

You have written very informative article about CRM projects for small business owners. The information you have posted is good to read,i am impressed to see your work. buy online Garage Shelving from essay writing

rose sandra
rose sandra
30 Aug, 2017 11:21 AM

You have written very informative article about CRM projects for small business owners. The information you have posted is good to read,i am impressed to see your work. buy online Garage Shelving from writing paper

Lisa J. Carbone
Lisa J. Carbone
25 Sep, 2017 02:15 PM

At the point when multi-record format (e.g. is opened, it might look somewhat unusual until the point that you squeeze Alt-F9 to uncover field codes. The trap in these layouts is sifting information for the header. New Assignment Writing service Bradford with skilled writers and techniques.

Online thesis writer
Online thesis writer
21 Oct, 2017 08:33 AM

You have composed exceptionally useful article about CRM ventures for entrepreneurs. The data you have presented is great on read,i am inspired to see your work. purchase online Garage Shelving fromOnline thesis writer

Doyle Norman
Doyle Norman
13 Nov, 2017 07:59 AM

Your topic of debate is Poor man mail merge and the whole element about this subject matter with involvement of some softwear as properly. We can get the rationale of that softwear right here. However, I'm finding UK Essay Writers from nearly comparable website that have impression an amazing crew working behind it.

Roger Smith
Roger Smith
17 Nov, 2017 10:12 AM

I guide the students that how they can pass the exams and how can they get the certification for the latest knowledge this certification exam students click at microsoft exam 70-347 study guide or visit its certification its better for your bright future and will helpful to attain the IT certification

Kenneth Gidley
Kenneth Gidley
01 Jan, 2018 06:46 AM

You have composed extremely instructive article about CRM ventures for entrepreneurs. The data you have presented is great on read,i am inspired to see your work. purchase online Garage Shelving from Custom Essay Help UK.

Morgan Bruce
Morgan Bruce
01 Jan, 2018 06:48 AM

Custom Essay Help UK is an astonishing on the web stage that offers the advantageous arrangements, and fascinating subjects for a wide range obviously writing to make it upbeat for understudies. online coursework author

22 Jan, 2018 10:31 AM

Hey admin your sharing way is unique. This is fantastic site.I enjoyed reading your articles. This is truly a great read for me. 1z0-348 exam

12 Feb, 2018 06:38 AM

Its a good post..keep posting and update the information.selfie stick.... routing number on check

19 Feb, 2018 08:55 AM

cara menjadi agen qnc jelly gamat

03 Mar, 2018 10:33 AM

Thanx for sharing such useful post keep it up :) Take my online Exam

12 Apr, 2018 11:32 AM

I will share to your post different website or friendz.this post rightly helpful for business work.continue

19 Apr, 2018 10:24 AM

To download Gamecih Android Download, one needs to fetch the app from its apk file. Apk is the Android application package and file format to download apps on the Android operating system.

25 May, 2018 07:47 AM

For each situation, the substance of the mail was a one-time the thing, i.e. I expected to send this one mail to five individuals today, and at that point one week from now I may have another email to send to six individuals, yet that would be an alternate email. So for my situation, it didn't generally make sense to make an email layout. I likewise would not like to utilize Word and complete an official mail blend since I needed a down to business approach to spare time, and I would not like to venture through the 6-page mail combine wizard where you select the record, the contacts organizer, and so on and so forth. This is where British Custom Essay Writers UK can really assist you. So if that is what you need, contact Professional Essays Writers for help now!

29 May, 2018 10:16 AM

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. MB6-897 braindumps

04 Jun, 2018 02:40 AM

Allassignmenthelp is first-class academic writing consultancy issuer. We've got a thousand's of happy college students from worldwide.

04 Jun, 2018 03:34 PM

This is kind of how I write for my [blog][ ]. I’ve been thinking of ways of putting up more content during the week, though, and I think the idea of delayed posts could work great. Great post, thanks for the tips!

05 Jun, 2018 09:01 AM

Thanks for the information

08 Jun, 2018 06:15 AM

Thanks a lot for the post. It has helped me get some nice ideas. I hope I will see some really good result soon.

08 Jun, 2018 07:48 AM

Nice post, all issue related with your computer Antivirus, mail and security. We provide online solution. Call us toll free AVG contact number +1-877-2498558. For more information visit here:- AVG contact number,

13 Jun, 2018 06:40 AM

I Must Say We Should Have An Online Discussion On This.

wolfberry side effects
wolfberry side effects
15 Jun, 2018 07:25 PM

Water down to today, when there is a superabundance of anti aging bully rent safe keeping of to products like creams, serums, gels and powders that all superintend to be this well-head of youth. Some thing their anti aging obeahism via ingredients that attired in b be committed to a ton of systemized inspection and dissection on how incrustation ages to assign on a obtain up as a replacement for them up and some are pasteurized hype.

02 Jul, 2018 12:03 PM

Thanks for another wonderful post. Where else could anybody get that type of info in such an ideal way of writing? yello

24 Jul, 2018 06:09 AM

This the best option to track dtdc courier track dtdc tracking services.

01 Aug, 2018 12:01 PM

Create Online Exam and Online Test Using Online Exam software. Conduct Exam Provide Online Exam Software and Offline Exam Software to Create Online Exam and Online Test, Web Based Exam Software and Computer Based Exam Software

01 Aug, 2018 10:16 PM

I was looking for this post for long time ago, thank you so much for sharing your knowledge with us, thanks Happy Diwali Messages

Sophie Miller
Sophie Miller
08 Aug, 2018 08:37 AM

I thank you for the information! I was looking for and could not find. You helped me! boost mobile for live person

stephen hawking
stephen hawking
13 Aug, 2018 10:40 AM

Our prime focus remains on researching and writing unique, plagiarism-free content with all your requirements incorporated for a top grade.

Write my assignment

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.