Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : How do I do a mail merge using Microsoft Word 2003?Search Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC Frequently Asked Questions

 New Topic 
 
 Post Reply 
[1]  
 How do I do a mail merge using Microsoft Word 2003? 
 Author   Message 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-02-22 11:13:08

I am trying to do a mail merge between QuickBooks and a letter I have in Microsoft Word using QODBC. I have Microsoft Office 2003 and I don’t seem to be able to connect to the "QuickBooks Data" database. Could someone please send me a step by step procedure to accomplish this? I have no problems connecting to Excel but Word is a big problem!

Step 1 - Start Your Mail Merge
Start Microsoft Word and open the letter you wish to mail merge QuickBooks information into. From the Tools pulldown within the "Letters and Mailings" option, click on "Show Mail Merge Toolbar" to add the toolbar option to Word. Once that's done, click on "Mail Merge" to start the merge wizard.

Click on "Letters" and select Step 1 of 6 - "Next: Starting document".

Click on "Use the current document" and select Step 2 of 6 - "Next: Select recipients".

Click on "Use an existing list" and select "Browse...".

Normally you should be able to use the "New Source..." button ....

to select your ODBC DSN using the new "Data Connection Wizard" added to Office 2002 and 2003....

and then select the "QuickBooks Data" ODBC Data Source.....

but the following message gets displayed and Microsoft's "Data Connection Wizard" will fail!

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-02-22 11:18:57
 Step 2 - The solution is to use Microsoft Query (MSQRY32.EXE) to build your database query
What you might of not noticed is that Word 2002 and 2003 stills allows you to run mail merges against database query files created using MS Query. MS Query is used in Excel to get external data and providing the Microsoft add-in is already installed on your computer, you can also use it with Word!

To locate MS Query, open Windows Explorer and locate the C:\Program Files\Microsoft Office\OFFICE11 folder (Word 2002 users need to locate the C:\Program Files\Microsoft Office\OFFICE10 folder instead). Create a "MSQRY32.EXE" shortcut on your desktop for future use and open "MSQRY32.EXE".

Select File and "New..." to start a new database query.

Select "QuickBooks Data" to establish a connection to the current company file open in QuickBooks. Yes, this means that QuickBooks must already be running on your computer and your company file open before hitting the "OK" button. Click on "OK" to continue.

Select the "Customer" table to merge customer data into your letter. (I've used a letter about overdue payments, so I need customer addresses and balances). You can also select or de-select the columns to be used in your query here too! Click on "Next>" to continue.

As I only want to send letters to customers with an overdue balance, I used a "Filter" here that will only include customers where the "TotalBalance" outstanding is greater than zero.

You can also specify the order you want the letters printed. I've chosen "FullName" here but "BillAddressPostalCode" could be more useful for mail sorting. Click on "Finish" to generate the query.

Customers with a current TotalBalance greater than zero will then be displayed.

Save your database query as "Customer Query from QuickBooks Data.dqy", click on "Save" and close MS Query. You're now ready to use this query within your letter!

Step 3 - Using your Microsoft Query database query to do Mail Merges
Back in Word, re-select the Mail Merge "Use an existing list" option and select "Browse..." if you still have Word open from doing the steps above or simply click on the "Open Data Source" icon on the Mail Merge toolbar..

Your database query "Customer Query from QuickBooks Data.dqy" is now available to mail merge in your "My Data Sources" folder. Select "Customer Query from QuickBooks Data.dqy" and click on "Open".

Say "Yes" to allow Word to reopen your customer query every time Word needs to open your data source so that when you re-open your letter next month it will have next months outstanding customers (and not today's outstanding customers). Database queries are dynamic, so whenever they are refreshed they will contain up to date information. Database queries should not be thought of as being the same as the static exports that you are able to do from QuickBooks!

Word will now display a list of Mail Merge Recipients, if you don't want to send a letter to particular customers, simply de-select them along with any job payments if applicable. Click "OK" when done.

Move your cursor to where you would like the address information to appear in your letter and on the Mail Merge Toolbar select the "Insert Address Block" icon. Choose you desired format and click on "OK".

Now the special < <Address Block> > mail merge tag has been added to your document click on the "Match Fields" icon on the Mail Merge Toolbar to match the Word < <AddressBlock> > special tags to the QuickBooks Customer Bill Address as shown here. Click "OK" when done.

Using the "Insert Merge Fields" icon on the Mail Merge Toolbar you can quickly add additional columns such as < <FirstName> > and < <TotalBalance> > as shown here.

Using the "<<>>" icon on the Mail Merge Toolbar you can quickly see real live data from your QuickBooks company file in your letter. Use the |<, < and >, >| buttons to move through the rows in your query.

You're now ready to merge the letter to your printer, mapi email system, fax card or network machine.

 

Note: The technique and example shown here can be used with the FREE QODBC Basic Edition. You don't need to purchase QODBC Pro or Web Edition to query customer data. The Australian edition of QuickBooks is shown above. USA, Canadian and UK versions of QuickBooks do differ in appearance and operation, however, the technique shown here still applies.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to