locked
Word MAil Merge Automation RRS feed

  • Question

  • I am trying to implement mail merge into our application using .NET as mentioned by c_shah. But the problem is, I am not sure what format the word document needs to be in. (ie., how to create mydoc.dot that is used in that example)

    I tried word's inbuilt mail merge functionality to create a template document that can be used from our application. But every time I try to do that, word requires me to enter the datafile. If I enter the datafile with dummy data, it immediately substitutes the data in its appropriate location. How do I save it as a template with no data in it so that I can later use it from my code to do mail merge?

    Your help is greatly appreciated.

    Saturday, February 17, 2007 1:45 AM

Answers

  •  etrast wrote:

    I have a few of more questions regarding the same issue. As I mentioned the openDataSource works great. I am using the following code,

    ApplicationClass myWordApp = new ApplicationClass();
    Document myWordDoc = new Document();

    You shouldn't use the "new" keyword with the Office object models except for instantiating the application. If you can't assign the document object when declaring it (using the Add or Open method of the Documents collection, for example) then use:

    Word.Document myWordDoc = nothing;

     etrast wrote:
    object nothing = "";
    object missing = System.Reflection.Missing.Value;

    object isTrue = true
    ;
    object notTrue = false
    ;
    object mySource = @"C:\MyWordTemplate.dot"
    ;

    myWordDoc = myWordApp.Documents.Open(
    ref mySource,ref missing, ref missing, ref missing,ref missing, ref missing, ref missing,ref missing, ref missing, ref missing,ref missing, ref missing, ref missing, ref missing,ref missing, ref missing);

    object format = WdOpenFormat.wdOpenFormatAuto;
    object connection = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=USER;Data Source=SOURCE;Extended Properties=''"
    ;

    object sql = "Select emp_id, emp_name from employee'";
    object subType = WdMergeSubType
    .wdMergeSubTypeOther;

    myWordDoc.MailMerge.OpenDataSource(@"C:\Employee.odc", ref format, ref notTrue, ref notTrue, ref isTrue, ref notTrue, ref nothing, ref nothing, ref notTrue, ref nothing, ref nothing, ref connection, ref sql, ref nothing, ref notTrue, ref subType);

    I recommend you to use "missing" for all parameters you don't need to set explicitly. OpenDataSource inherits from the OpenDocument interface (thus all the parameters), but most are irrelevant. I think you need only the first parameter, the connection, the sql and the subType

     etrast wrote:
    myWordDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocument;
    myWordDoc.MailMerge.Execute(
    ref
    notTrue);
    myWordDoc.Close(ref notTrue, ref missing, ref
    missing);
    myWordApp.Application.Quit(
    ref notTrue, ref missing, ref missing);

    When I run it, I dont get any errors and everything runs fine. But where are the merged documents? Say I have 10 different records and I expected to see 10 different documents to be created, with each sql result row substituted in the merge field. Should I specify a destination folder somewhere?

    This is because you're closing the application. If you do that, then there's no interface to display the document in - you're closing it. You can close myWordDoc, but don't touch myWordApp if you want to see the result.

    Otherwise, you need to close the result. After you close myWordDoc the result should be myWordApp.ActiveDocument. There is no guarantee, however, and the Execute method does not, unfortunately, return the resulting document. If you're sure that nothing and no one else is using myWordApp then you should be OK. Otherwise, you'll want to do something like test the file name. Or use the MailMergeAfterMerge event, which does provide you with an object for the resulting document.

     etrast wrote:
    Also, everytime I run this code, it keeps asking me for the password for DB I am connecting to. Is there a way to store the password? If there is, will it be secure to store a DB password?

    You need to ask this in the mailmerge.fields newsgroup. Peter Jamieson is the expert on connections. Be sure to supply information about the type of data source you're using, including all connection string information (including the contents of the odc file).

     etrast wrote:
    PS: I also wrote an alternate code. This works great and it creates the 10 different documents for me. But I am not sure which method would be more faster and correct.

    That's hard to say; you'd have to test on the target systems. You may certainly go this route, but then I recommend the "main merge document" NOT have a data source connected, as this will slow down opening the document. One usually would use bookmark targets, rather than mergefields for this scenario. Writing to a bookmark range is probably faster then finding/selecting merge fields.

    Generally, I tend to only use mail merge if the end user is involved in setting up the documents. Mail merge does have a lot of overhead.

     etrast wrote:
    ApplicationClass myWordApp = new ApplicationClass(); // our application
    object missing = System.Reflection.Missing.Value;
    // our 'void' value
    object notTrue = false;
    // our boolean false
    object filename = @"C:\MyWordTemplate.dot"; // our word template

    myWordApp.Visible = false; // tell word not to show itself

    //Create connection object
    OracleConnection con = new OracleConnection
    ();
    // Specify connection string
    con.ConnectionString = "Data Source=SOURCE;User ID=USER;Password=**********;";

    try{
    // Open the connection
    con.Open();
    }
    catch (Exception
    ex)
    {
    Response.Write(ex.Message);
    }

    string cmdQuery = @"select emp_id, emp_name from employee";

    // Create command object
    OracleCommand cmd = new OracleCommand
    (cmdQuery);
    cmd.Connection = con;
    cmd.CommandType =
    CommandType
    .Text;

    try{
    OracleDataReader
    reader = cmd.ExecuteReader();
    int i = 1;

    while (reader.Read())
    {
    Document myWordDoc = new Document();
    // our document
    object destination = @"C:\Inetpub\wwwroot\MailMerge\MailMerge\MyNewDocument" + i.ToString() + ".doc"; // our target filename

    System.Collections.Generic.Dictionary<string, string> row = new System.Collections.Generic.Dictionary<string, string>();
    row.Add(
    "asgne_id"
    , reader.GetDecimal(0).ToString());
    row.Add(
    "selected_surname"
    , reader.GetString(1));

    myWordDoc = myWordApp.Documents.Add( ref filename, ref missing,ref missing,ref missing);

    foreach (MailMergeField myField in myWordDoc.MailMerge.Fields)
    {
    myField.Select();
    string
    key = myWordApp.Selection.Text;
    key = key.Replace(
    "»", "").Replace("«", ""
    );

    if (row.ContainsKey(key))
    {
    string
    text = row[key];
    myWordApp.Selection.TypeText(text);
    }
    }

    myWordDoc.SaveAs(ref destination,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing);

    myWordDoc.Close(ref notTrue, ref missing, ref missing);

    i++;

    }
    }
    catch (Exception ex)
    {
    Response.Write(ex.Message);
    }
    finally
    {
    cmd.Dispose();
    con.Close();
    con.Dispose();
    myWordApp.Application.Quit(
    ref notTrue,ref missing,ref
    missing);
    }

    -etrast-

    Thursday, March 1, 2007 4:21 PM

All replies

  • I just answered a similar question for another user ... there's lots of example source code in my message.

    Please refer to http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1234056&SiteID=1

     

    Monday, February 19, 2007 8:16 PM
  • Hi etrast

    I'm splitting your post off into a new thread to give it better visibility. The original thread, for those reading along:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1177765&SiteID=1

    What you want to do depends quite a bit on how you envision things ought to work. For example, would the data source be relatively static (always the same table or view)? Or are you thinking things should be more dynamic? Are you planning to let users create their own "templates", or would you be supplying a static set?

    The built-in mail merge functionality was designed to allow the end-user to connect to a data source. Originally (think twenty years ago), these were local and static. Delimited text files and spreadsheets. Later came smaller, local databases such as dBase and Access. Support for scalable, server-side connections was added only a few years ago. There's still no support for data sources over a URL or through streaming. The design remains targeted at the end-user...

    Monday, February 19, 2007 8:16 PM
  • Well, this is the general idea right now. Users can set up multiple word document templates and create SQL queries connecting to each template within our application. Whenever they need the mail merge done, they select the template and fire up the merge. The sql will return some rows based on the current data. I can write code in .Net to call the available mail-merge functions that will plug in the rows returned back from sql.

    My problem is, how do I set up the word document as a template to be re-used later and multiple times for mail merge? Thank you for all your help.

    Monday, February 19, 2007 8:44 PM
  • Hi etrast

    OK, I'm still not sure we're always talking about the same thing, here, so forgive me if I repeat something you already know :-) When a document (or template) is set up for mail merge it will have a link to a data source. This link is dynamic, so whenever the file is opened the current data will come through the link. This means a mail merge main document may show different data each time it is opened. The mail merge must actually be executed (to a new document or the printer) in order to get a static representation ("snapshot") of the data.

    So, there wouldn't be any need for your .NET application to do anything more than call the Execute method on the Word's mail merge object in order to "plug in the rows returned back from sql". But the user could also do this by clicking a button in Word's mail merge interface.

    Just making sure we're on the same wave-length :-) Please tell me if your idea or expectation differs from the above...

    <<My problem is, how do I set up the word document as a template to be re-used later and multiple times for mail merge?>>

    Two basic approaches are possible. (Please note that I may be misunderstanding what you mean by "set up the Word document as a template" - I'm interpreting the question literally. If you mean it otherwise, please be more explicit.)

    1. You create a "plain" Word document (*.doc) then just make a copy of it. The user can save it (or not) without affecting the "template". Your .NET code would manage the copying and opening; the original file would remain somewhere safe. You have the option of using the Documents.Open or the Documents.Add methods (in the latter case, Word will create the copy for you).

    2. you create the Word document, then save it explicitly as a Word template (*.dot). In this case, you'd have to use the Documents.Add method to generate a new document (a copy).

    Monday, February 19, 2007 8:58 PM
  • Hi Cindy,

    Thank you for your detailed explanation. Our application is going to do something similar. That is, open a copy of the template and then execute the mail merge. So far so good.

    But here is what I am trying to do. I need the initial template document so that, it can be re-used later from my .Net code. So I open up word and select a blank document. I go to Tools -> Letters and Mailings -> Mail merge wizard. In the document type, I select 'Letters' and 'Use the current document'. The next one is select recipient list. But, here is the problem. My recipient list is in an oracle database. All I am trying here is to set up the template with mail merge fields. So I decide to improvise, create a new list with dummy data. I also create new custom fields in there so that they will match the ones I actually have in my database. Now I write my letter, using my custom columns as mail merge fields. Once done, I do not proceed to the next step (Preview). Instead the just save the whole thing as a .dot or .doc.

    Am I doing this correct? Can this document be programmatically mail merged now? If this is not the correct way to do it, what am I doing wrong?

    Thank you so much for your help again Cindy and also for your timely replies.

    -etrast-

    Tuesday, February 20, 2007 2:37 AM
  • Hi etrast

    Ah, I think I'm following, now. You've inserted merge fields, but you're still connected to the "dummy" data source. The missing piece you're looking for is how to programmatically connect to the real data at run-time?

    The method you're looking for is Document.MailMerge.OpenDataSource. That's the easy part :-) Getting the proper connection string is trickier. The best way to proceed is to record a macro while linking to a representative source. I'm not famliar with how Oracle databases are structured, and you mentioned the user creating a query...

    Assuming this means you want to connect to a particular table, filtering the result, record the macro while connecting to this table. Stop the recorder and take a look at the result (Alt+F11, then look for the "New macros" module in the Normal project). You can use the SQLStatement + SQLStatement1 parameters to set the query string. Each parameter can handle a maximum of 255 characters, for a possible total of 510 characters for the query. If your filters are going to be more complex than this, you'll need to provide some kind of "primary filter" at the database end (for SQL Server you'd set up and manipulate a View).

    Hope this is enough to get you started :-)

    Tuesday, February 20, 2007 8:57 AM
  • Thank you very much Cindy. That worked. :-D

    -etrast-

    Tuesday, February 20, 2007 11:50 PM
  • Cindy,

    I have a few of more questions regarding the same issue. As I mentioned the openDataSource works great. I am using the following code,

    ApplicationClass myWordApp = new ApplicationClass();
    Document myWordDoc = new Document();

    object nothing = "";
    object missing = System.Reflection.Missing.Value;

    object isTrue = true
    ;
    object notTrue = false
    ;
    object mySource = @"C:\MyWordTemplate.dot"
    ;

    myWordDoc = myWordApp.Documents.Open(
    ref mySource,ref missing, ref missing, ref missing,ref missing, ref missing, ref missing,ref missing, ref missing, ref missing,ref missing, ref missing, ref missing, ref missing,ref missing, ref missing);

    object format = WdOpenFormat.wdOpenFormatAuto;
    object connection = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=USER;Data Source=SOURCE;Extended Properties=''"
    ;

    object sql = "Select emp_id, emp_name from employee'";
    object subType = WdMergeSubType
    .wdMergeSubTypeOther;

    myWordDoc.MailMerge.OpenDataSource(@"C:\Employee.odc", ref format, ref notTrue, ref notTrue, ref isTrue, ref notTrue, ref nothing, ref nothing, ref notTrue, ref nothing, ref nothing, ref connection, ref sql, ref nothing, ref notTrue, ref subType);

    myWordDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocument;
    myWordDoc.MailMerge.Execute(
    ref
    notTrue);
    myWordDoc.Close(ref notTrue, ref missing, ref
    missing);
    myWordApp.Application.Quit(
    ref notTrue, ref missing, ref missing);

    When I run it, I dont get any errors and everything runs fine. But where are the merged documents? Say I have 10 different records and I expected to see 10 different documents to be created, with each sql result row substituted in the merge field. Should I specify a destination folder somewhere?

    Also, everytime I run this code, it keeps asking me for the password for DB I am connecting to. Is there a way to store the password? If there is, will it be secure to store a DB password?

    PS: I also wrote an alternate code. This works great and it creates the 10 different documents for me. But I am not sure which method would be more faster and correct.

    ApplicationClass myWordApp = new ApplicationClass(); // our application
    object missing = System.Reflection.Missing.Value;
    // our 'void' value
    object notTrue = false;
    // our boolean false
    object filename = @"C:\MyWordTemplate.dot"; // our word template

    myWordApp.Visible = false; // tell word not to show itself

    //Create connection object
    OracleConnection con = new OracleConnection
    ();
    // Specify connection string
    con.ConnectionString = "Data Source=SOURCE;User ID=USER;Password=**********;";

    try{
    // Open the connection
    con.Open();
    }
    catch (Exception
    ex)
    {
    Response.Write(ex.Message);
    }

    string cmdQuery = @"select emp_id, emp_name from employee";

    // Create command object
    OracleCommand cmd = new OracleCommand
    (cmdQuery);
    cmd.Connection = con;
    cmd.CommandType =
    CommandType
    .Text;

    try{
    OracleDataReader
    reader = cmd.ExecuteReader();
    int i = 1;

    while (reader.Read())
    {
    Document myWordDoc = new Document();
    // our document
    object destination = @"C:\Inetpub\wwwroot\MailMerge\MailMerge\MyNewDocument" + i.ToString() + ".doc"; // our target filename

    System.Collections.Generic.Dictionary<string, string> row = new System.Collections.Generic.Dictionary<string, string>();
    row.Add(
    "asgne_id"
    , reader.GetDecimal(0).ToString());
    row.Add(
    "selected_surname"
    , reader.GetString(1));

    myWordDoc = myWordApp.Documents.Add( ref filename, ref missing,ref missing,ref missing);

    foreach (MailMergeField myField in myWordDoc.MailMerge.Fields)
    {
    myField.Select();
    string
    key = myWordApp.Selection.Text;
    key = key.Replace(
    "»", "").Replace("«", ""
    );

    if (row.ContainsKey(key))
    {
    string
    text = row[key];
    myWordApp.Selection.TypeText(text);
    }
    }

    myWordDoc.SaveAs(ref destination,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing);

    myWordDoc.Close(ref notTrue, ref missing, ref missing);

    i++;

    }
    }
    catch (Exception ex)
    {
    Response.Write(ex.Message);
    }
    finally
    {
    cmd.Dispose();
    con.Close();
    con.Dispose();
    myWordApp.Application.Quit(
    ref notTrue,ref missing,ref
    missing);
    }

    -etrast-

    Wednesday, February 28, 2007 10:38 PM
  •  etrast wrote:

    I have a few of more questions regarding the same issue. As I mentioned the openDataSource works great. I am using the following code,

    ApplicationClass myWordApp = new ApplicationClass();
    Document myWordDoc = new Document();

    You shouldn't use the "new" keyword with the Office object models except for instantiating the application. If you can't assign the document object when declaring it (using the Add or Open method of the Documents collection, for example) then use:

    Word.Document myWordDoc = nothing;

     etrast wrote:
    object nothing = "";
    object missing = System.Reflection.Missing.Value;

    object isTrue = true
    ;
    object notTrue = false
    ;
    object mySource = @"C:\MyWordTemplate.dot"
    ;

    myWordDoc = myWordApp.Documents.Open(
    ref mySource,ref missing, ref missing, ref missing,ref missing, ref missing, ref missing,ref missing, ref missing, ref missing,ref missing, ref missing, ref missing, ref missing,ref missing, ref missing);

    object format = WdOpenFormat.wdOpenFormatAuto;
    object connection = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=USER;Data Source=SOURCE;Extended Properties=''"
    ;

    object sql = "Select emp_id, emp_name from employee'";
    object subType = WdMergeSubType
    .wdMergeSubTypeOther;

    myWordDoc.MailMerge.OpenDataSource(@"C:\Employee.odc", ref format, ref notTrue, ref notTrue, ref isTrue, ref notTrue, ref nothing, ref nothing, ref notTrue, ref nothing, ref nothing, ref connection, ref sql, ref nothing, ref notTrue, ref subType);

    I recommend you to use "missing" for all parameters you don't need to set explicitly. OpenDataSource inherits from the OpenDocument interface (thus all the parameters), but most are irrelevant. I think you need only the first parameter, the connection, the sql and the subType

     etrast wrote:
    myWordDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocument;
    myWordDoc.MailMerge.Execute(
    ref
    notTrue);
    myWordDoc.Close(ref notTrue, ref missing, ref
    missing);
    myWordApp.Application.Quit(
    ref notTrue, ref missing, ref missing);

    When I run it, I dont get any errors and everything runs fine. But where are the merged documents? Say I have 10 different records and I expected to see 10 different documents to be created, with each sql result row substituted in the merge field. Should I specify a destination folder somewhere?

    This is because you're closing the application. If you do that, then there's no interface to display the document in - you're closing it. You can close myWordDoc, but don't touch myWordApp if you want to see the result.

    Otherwise, you need to close the result. After you close myWordDoc the result should be myWordApp.ActiveDocument. There is no guarantee, however, and the Execute method does not, unfortunately, return the resulting document. If you're sure that nothing and no one else is using myWordApp then you should be OK. Otherwise, you'll want to do something like test the file name. Or use the MailMergeAfterMerge event, which does provide you with an object for the resulting document.

     etrast wrote:
    Also, everytime I run this code, it keeps asking me for the password for DB I am connecting to. Is there a way to store the password? If there is, will it be secure to store a DB password?

    You need to ask this in the mailmerge.fields newsgroup. Peter Jamieson is the expert on connections. Be sure to supply information about the type of data source you're using, including all connection string information (including the contents of the odc file).

     etrast wrote:
    PS: I also wrote an alternate code. This works great and it creates the 10 different documents for me. But I am not sure which method would be more faster and correct.

    That's hard to say; you'd have to test on the target systems. You may certainly go this route, but then I recommend the "main merge document" NOT have a data source connected, as this will slow down opening the document. One usually would use bookmark targets, rather than mergefields for this scenario. Writing to a bookmark range is probably faster then finding/selecting merge fields.

    Generally, I tend to only use mail merge if the end user is involved in setting up the documents. Mail merge does have a lot of overhead.

     etrast wrote:
    ApplicationClass myWordApp = new ApplicationClass(); // our application
    object missing = System.Reflection.Missing.Value;
    // our 'void' value
    object notTrue = false;
    // our boolean false
    object filename = @"C:\MyWordTemplate.dot"; // our word template

    myWordApp.Visible = false; // tell word not to show itself

    //Create connection object
    OracleConnection con = new OracleConnection
    ();
    // Specify connection string
    con.ConnectionString = "Data Source=SOURCE;User ID=USER;Password=**********;";

    try{
    // Open the connection
    con.Open();
    }
    catch (Exception
    ex)
    {
    Response.Write(ex.Message);
    }

    string cmdQuery = @"select emp_id, emp_name from employee";

    // Create command object
    OracleCommand cmd = new OracleCommand
    (cmdQuery);
    cmd.Connection = con;
    cmd.CommandType =
    CommandType
    .Text;

    try{
    OracleDataReader
    reader = cmd.ExecuteReader();
    int i = 1;

    while (reader.Read())
    {
    Document myWordDoc = new Document();
    // our document
    object destination = @"C:\Inetpub\wwwroot\MailMerge\MailMerge\MyNewDocument" + i.ToString() + ".doc"; // our target filename

    System.Collections.Generic.Dictionary<string, string> row = new System.Collections.Generic.Dictionary<string, string>();
    row.Add(
    "asgne_id"
    , reader.GetDecimal(0).ToString());
    row.Add(
    "selected_surname"
    , reader.GetString(1));

    myWordDoc = myWordApp.Documents.Add( ref filename, ref missing,ref missing,ref missing);

    foreach (MailMergeField myField in myWordDoc.MailMerge.Fields)
    {
    myField.Select();
    string
    key = myWordApp.Selection.Text;
    key = key.Replace(
    "»", "").Replace("«", ""
    );

    if (row.ContainsKey(key))
    {
    string
    text = row[key];
    myWordApp.Selection.TypeText(text);
    }
    }

    myWordDoc.SaveAs(ref destination,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing,ref missing);

    myWordDoc.Close(ref notTrue, ref missing, ref missing);

    i++;

    }
    }
    catch (Exception ex)
    {
    Response.Write(ex.Message);
    }
    finally
    {
    cmd.Dispose();
    con.Close();
    con.Dispose();
    myWordApp.Application.Quit(
    ref notTrue,ref missing,ref
    missing);
    }

    -etrast-

    Thursday, March 1, 2007 4:21 PM