locked
Import data from SQL Server into MS Word document for Mail Merge purpose ? RRS feed

  • Question

  • Hi,

    Is it possible to import contacts from SQL Server into MS Word for mail merge purpose or if retrieving data from MS Excel can we update the data in MS Excel sheet without opening it ?

    Note: Remember when you open a word document already set up for mail merge, asks you to run the query to return all records from the excel sheet it is connected to.


    Khurram
    • Edited by khum Sunday, January 1, 2012 11:34 AM
    Sunday, January 1, 2012 11:30 AM

Answers

  • You can directly use SQL server as data source. When you select the recipients by "use existing list", the dialog box has a button "New Source". There you can select "SQL Server" and follow the Wizard.
    Monday, January 2, 2012 1:58 PM

All replies

  • You can directly use SQL server as data source. When you select the recipients by "use existing list", the dialog box has a button "New Source". There you can select "SQL Server" and follow the Wizard.
    Monday, January 2, 2012 1:58 PM
  • it is long back your last reply even though I feel this the right context for getting answer. I followed your way to get data from SQL server and was successful. But I want to know how to select data from more than one table because I didn't find any way to provide SQL query or Select multiple table.. 

    Please help me

    Tuesday, April 1, 2014 7:55 AM
  • Word and the current data source dialog do not really give you any help with that.

    You either have to be able to create a View in SQL Server that performs the query you need, then connect to that, or you have to be able to create the correct query manually (or perhaps using some other query tool that can help you), then use VBA to connect using that query. 

    For example, if you have been through the connection process once (connecting to a single table) then you will have a .odc (Office Data Connection file) which has the info. needed to connect to the correct server and database. It's a text file with some HTML and XML inside. You can copy/rename it. Let's say it is called "c:\a\myodc.odc" Then in VBA you can use something like

    ActiveDocument.OpenDataSource Name:="c:\a\myodc.odc, _
    SQLStatement:="put your SQL statement in here, and if it is long,...", _
    SQLStatement1:="put the second part in here"

    You get a maximum of either 255 or around 511 characters in the SQL statement, and Word tends to impose some syntax requirements that Transact-SQL does not, so e.g. you may need to quote all your table names.

    You can also se an empty .odc file and provide connection info. in the COnnection:= parameter in OpenDataSource.

    As background, until Word 2000, by default you would use MS Query to create your SQL query, and MS Query does have facilities that can help you build your query (a bit like the ones in MS Access). That may still be possible (it is a bit harder to find the MS Query option now, and I am not sure it works with the latest versions of Word). MS Query only works for ODBC queries, and they do not always work correctly when you actually issue the query using ODBC from Word, because of a Word problem to do with Unicode fields in SQL Server. But you could probably still use MS Query to help you construct your SQL. (It's probably easier to do that in Excel, though).


    Peter Jamieson

    • Proposed as answer by Aaron R Bailey Tuesday, March 29, 2016 10:40 PM
    Tuesday, April 1, 2014 9:07 AM
  • Thanks for the reply, Peter

    I used the SQL View to join two table and working fine. But now I have issue that my table is parent-child and in my SQL view it will return table like 

    Group1    Child 1-Group1

    Group1    Child 2-Group1

    Group1    Child 3-Group1

    Group2    Child 1-Group2

    Group2    Child 2-Group2

    so on

    So here in word I put blank table having two rows and in first row I put merge field Group Name and second row I put Child name and finished the mail merge(mail merge type: Directory). In that case, Im getting Group name repeated for each Child in that group. Actually the Group should not be repeated like this. How can I achieve this?

    Tuesday, April 1, 2014 12:13 PM
  • Word doesn't do "one-many" merges out-of-the-box, but several approaches are listed in macropod's reply in the following conversation:

    http://social.technet.microsoft.com/Forums/office/en-US/00cdb9fe-0593-44ac-bd76-5e19e8452b78/mail-merge-with-expandable-table?forum=word

    There are other approaches. In some cases it makes sense to set up the parent table as the mailmerge data source, then include the relevant child records using a DATABASE field. In that case, you usually have to apply formatting to the resulting table post-merge.

     


    Peter Jamieson

    Tuesday, April 1, 2014 4:16 PM
  • I'm running into a similar problem and would like your feedback if possible.

    You can edit the .odc file and change the command type from Table to SQL and then put an SQL select in the command text and oddly enough that works fine for excel, however in a mail merge all word seems to care about is selecting an entire table which is really dumb.

    Specifically it ignore the command type and just seems to look at the <meta name=Table content="table"> meta tag and then just selects everything from that table.

    Does anyone know a good way around this? What I'm trying to achieve is giving people the ability to mail merge emails from our MySQL database, it needs to be as simple for the staff as merging with an excel file except they select the .odc file. That means it needs to have the custom query, I can't expect them to be editing VBA and whatnot.

    I guess the best solution so far is set up a view on the database that does the custom query and point the odc file at that instead?

    Thursday, November 5, 2015 12:02 PM
  • I think you have pretty much got it right.

    AFAICR Word only recognises the older .odc format, not the current one, and does not interpret its contents correctly - as you say, it does not recognise a query in there, and wrongly uses the meta name as the source of the table name.

    Just to make things even worse, the .odc does not even really provide any form of useful indirection. So for example, suppose your Word document has connected to the data source using a .odc called my.odc, and you have a .odc called user1.odc that connects to table1 and a .odc called user2.odc that connects to table2. Then you might hope that you could copy user1.odc to my.odc, open Word, and have it connect to table1, then copy user2.odc to my.odc, reopen the document, and it would be connected to table2. It just doesn't work that way. The first time you connect via thr .odc, Word stores the complete query it needs, which obviously specifis the table name. You actually have to go through the connectionprocess again to get it to "see" what is in the ..odc.

    So in essence, you either have to do what you suggest and point the .odc at a suitable View, or you have to be in a position to modify the mail merge's query string at some point in the process (e.g. either using VBA, or perhaps using .NET and the OpenXML library to modify the .docx outside Word).

    Peter Jamieson

    Thursday, November 5, 2015 6:34 PM