A customer had a requirement to merge data from Sage CRM to Excel. Currently, Sage CRM only supports mail merges to Word - they were wondering if this is possible with a custom ASP page.


There are a number of ways of generating XLS files from a classic ASP page. Here's a couple of examples.

The nicest way of doing this would is to create a new spreadsheet using Excel.Application, but this would require a bit of reading, and you'll need to have Excel installed on the server. It provides an awful lot of control over what goes into the spreadsheet however. The last reply here gives a quick example:

A simpler way of doing this would be to create an ASP page that returns a table, and tells the client that the content type is an Excel file. When the client's copy of Excel opens the file, it will be rendered as an Excel spreadsheet. Examples are here:

I've put a basic example together using the second link. It will export some company details to an Excel page, from a button on the Company summary screen. First create a custom ASP page with the following:

<!-- #include file ="sagecrm.js" -->
      var strXLS = "";

      var compRec = CRM.FindRecord("Company", "comp_companyid="+CRM.GetContextInfo("company", "comp_companyid"));

      if (!compRec.Eof) {

            strXLS += "<table>"
                + "<tr><th colspan='2'>This is an example Excel export</th></tr>"
                + "<tr>"
                  + "<th>Attribute</th>"
                  + "<th>Value</th>"
                + "</tr>"
                + "<tr>"
                  + "<td>" + CRM.GetTrans("ColName", "Comp_Name")
                  + "</td><td>" + compRec.item("comp_name") + "</td>"
                + "</tr>"
                + "<tr>"
                  + "<td>" + CRM.GetTrans("ColName", "Comp_Type")
                  + "</td><td>" + compRec.item("comp_type") + "</td>"
                + "</tr>"
                + "<tr>"
                  + "<td>" + CRM.GetTrans("ColName", "Comp_Website")
                  + "</td><td>" + compRec.item("comp_website") + "</td>"
                + "</tr>"
                + "<tr>"
                  + "<td>" + CRM.GetTrans("ColName", "Comp_Sector")
                  + "</td><td>" + compRec.item("comp_sector") + "</td>"
                + "</tr>"
              + "</table>";

      Response.ContentType = "application/";
      Response.AddHeader("content-disposition", "filename="+compRec.item("comp_name")+".xls");

Next, you add a Create script to any field on CompanyBoxLong. The idea here is that we can generate a URL, as button groups don't give us an option to open a CRM page in a new window by default:

var exportUrlScript = "<scr"
      +"ipt>var exportPageUrl=\""+CRM.Url("company-to-excel.asp")+"\";</scr"

We'll need a custom function to launch the popup window; we can put this in as a Custom Content script on CompanyBoxLong:

function DoCustomExport() {, "_blank");

Finally, you create a button group on Company Summary that calls our custom function. The URL target is javascript: x=DoCustomExport(); void(x);.

Button group screenshot 


Clicking your new button will give a security warning, as the file contents themselves are just a bunch of HTML tags.

IE warning 
However,  if you click Yes, Excel can open it successfully.

Excel screenshot 
If you don't want the security warning to appear, you can either use the same technique to export to a CSV file, or install Excel on the server and use the Excel.Application example from the first link.

There's also ready-made components available (I believe Greytrix do one) that will do mail merges directly to Excel.