Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Automating mail merge into a Word document

Posted on 2005-05-06
20
Medium Priority
?
431 Views
Last Modified: 2007-12-19
I need to create a button on a form displaying the current record and when this button is pressed, the pertinent data from the form is then merged with a Word template. This Word template is a summary form. The form is only based on one table. I have tried looking in using macros to automate the processes but have not made any headway. This is probably really easy for the experts out there.
0
Comment
Question by:LLWIT
  • 10
  • 10
20 Comments
 
LVL 13

Expert Comment

by:Lucas
ID: 13947640
Try this code:  Put it on the onclick event for your button and change some variables around...

---------------------------------------------------------
Private Sub cmdLetter_Click()
Dim myword As Object
Dim file As String
Dim sql As String

file = "C:\NAME OF YOUR FILE.DOC"  '<----CHANGE THE FILE NAME HERE
sql = "Select * YOURTABBLE"  '<---REPLACE THIS WITH YOUR SQL STATEMENT

Set myword = CreateObject("Word.Application")

myword.Documents.Open file, , True
myword.Application.DisplayAlerts = wdAlertsNone
With myword.ActiveDocument.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sql
    .ViewMailMergeFieldCodes = False
    '.Execute True    '<---UNCOMMENT THIS LINE IF YOU WANT IT TO AUTOMATICALLY MERGE YOUR DATA
End With

'myword.Documents("Document1").Close SaveChanges:=wdDoNotSaveChanges
     
myword.Application.Visible = True
myword.Application.WindowState = wdWindowStateMaximize
   
'myword.ActiveDocument.PageSetup.FirstPageTray = wdPrinterLowerBin
'myword.ActiveDocument.PageSetup.OtherPagesTray = wdPrinterDefaultBin

Set myword = Nothing

END SUB
--------------------------------------------------------------
This will work with Access 2k and up
0
 

Author Comment

by:LLWIT
ID: 13959034
Thanks for the code lucas911. I'm now getting compile errors for the line:
.OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sql
The code will bring up the Word template but no further. Also, I can't see where the code specifies the record currently in use....

I also need to specify certain fields from the table not all fields. Can you please assist?
0
 
LVL 13

Expert Comment

by:Lucas
ID: 13959782
Okay.  What version of word are you using?  To be on the safe side add a reference to the Microsoft Word Object Library XX (xx is your version).  In your code, click on TOOLS -> REFERENCES and choose the word object library.

If you want to only select certain records, you can modify the SQL statement like so:

Sql = "Select * from yourTable where lastname = '" & Smith & "'"
or if you're looking for an id number (a number not a string) you can use:
sql = "Select * from yourTable where id = " & myID & ""

Notice i didn't put single quotes in the last one because i'm looking for a number.  Then just let the code run.  Replace "yourTable" with the table you want to pull the data from also.

Let me know if you need more help.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:LLWIT
ID: 13960428
I've referenced the object libary as suggested Word 10....but I think if I elaborate on the problem you might be able to help further.

In the database I have created a form which contains several fields - underlying table is called Clients. The Primary Key is 'ID' and is an autonumber. When the user click the button 'Summaryform',  a word document will be generated with the Client's name, Address, tel no etc. This is how I think the system would need to be set up.

1) a Word Template set up to pick up the fields required.
2) code in Access to open up Word and an instance of this template
3) insert the required field data by the ID selected e.g. if John Smith's ID = 1 the system would need to filter the table 'Clients' for the record ID = 1.
4) After the merge the user is able to choose where to save it so he/she can email the document on or just to print it out.

I can set up a Word Template with bookmarks as placeholders for the relevant fields and I have been successful in getting Access to automatically call up the Template but no success in the merge details.

I hope this put you more into the picture.

Thanks
0
 
LVL 13

Expert Comment

by:Lucas
ID: 13960899
Ok here is what you should do.  

Setup your word template with Mergefield codes.  Where ever you want the database to dump the value put your cursor there, then go to INSERT -> choose MAIL MERGE under categories -> choose MERGEFIELD under field names.  In the text box below, after it says MERGEFIELD type in the field that you want to appear in there from the database i.e. if in your database you have a field for first name called fname your mail merge field would be MERGEFIELD fname.  Do this for all the fields you want to merge in your word document changing the fname to the rest of your fields.

Then save it as a template, .dot or .doc.  Now your document should show something like this <<fname>>, <<lname>>.

Now go back into access and into that code that i gave you.

Change your sql query to pull up the right record.  I'm assuming you have the id field on your form, if not add it there change the name of it to txtID and change the visible property to false so that no one can see it.  If you already have it there then great.

Your sql therefore will be like this:  
sql = "Select * from clients where id = " & Me.txtid & ""

That line tells the database to look for a record that matches the id that's in the id textbox.  

So your final code should look like something like this:
----------------------------------------------------------------------
Private Sub cmdLetter_Click()
Dim myword As Object
Dim file As String
Dim sql As String

file = "C:\NAME OF YOUR FILE.DOC"  '<----CHANGE THE FILE NAME HERE

sql = "Select * from clients where id = " & Me.txtid & ""

Set myword = CreateObject("Word.Application")

'IF YOU SAVED YOUR TEMPLATE AS A DOC THEN USE THE LINE BELOW
myword.Documents.Open file, , True

'IF YOU SAVED YOUR TEMPLATE AS A DOT THEN COMMENT THE LINE ABOVE AND USE THE LINE BELOW
'myword.Documents.add file,,true

myword.Application.DisplayAlerts = wdAlertsNone
With myword.ActiveDocument.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sql
    .ViewMailMergeFieldCodes = False
    .Execute True    
End With

myword.Documents("Document1").Close SaveChanges:=wdDoNotSaveChanges
     
myword.Application.Visible = True
myword.Application.WindowState = wdWindowStateMaximize
   

Set myword = Nothing

End Sub

--------------------------------------------------------
Let me know if you need more help
0
 

Author Comment

by:LLWIT
ID: 13967920
Thanks again for the code. However it throws an error 5922 and on debugging stops on the following line:

 .OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sql

Do I need to amend this line in any way for it to work?
0
 
LVL 13

Expert Comment

by:Lucas
ID: 13968530
Try this:

Remove the ODBC Text Driver
This method allows Word to use its own text converter instead of the ODBC Text Driver to read the data file. Use the following steps to remove the ODBC Text driver:

1. Quit Microsoft Direct Mail Manager, Microsoft Word, and all Windows applications.  
2. Click the Windows Start menu, point to Settings, and click Control Panel.  
3. Double-click the 32bit ODBC icon.  
4. On the User DSN tab, select "Text Files Microsoft Text Driver (*.txt, *.csv)" and then click Remove.

NOTE: When you are prompted "Are you sure you want to remove the Text Files data source," click Yes.  
5. Click OK.  

and also show me your code.
0
 

Author Comment

by:LLWIT
ID: 13968766
Here my code...it's not much different to yours....

Private Sub Command578_Click()

Dim myword As Object
Dim file As String
Dim sql As String

file = "G:\Claims\PIsummarysheet1.DOC"  '<----CHANGE THE FILE NAME HERE

sql = "Select * from ClaimsSummaryQry where ID = " & Me.ID & ""

Set myword = CreateObject("Word.Application")

'IF YOU SAVED YOUR TEMPLATE AS A DOC THEN USE THE LINE BELOW
myword.Documents.Open file, , True

myword.Application.DisplayAlerts = wdAlertsNone
With myword.ActiveDocument.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource NAME:=CurrentDb.NAME, SQLStatement:=sql
    .ViewMailMergeFieldCodes = False
    .Execute True
End With

myword.Documents("Document1").Close SaveChanges:=wdDoNotSaveChanges
     
myword.Application.Visible = True
myword.Application.WindowState = wdWindowStateMaximize
 
Set myword = Nothing

End Sub

As for removing the ODBC Driver....will is have any detrimental effects. The database will be distributed in a client-server environment. This would suggest all the users will have to have their systems modified to enable the code the work.
0
 
LVL 13

Expert Comment

by:Lucas
ID: 13968833
Then i think it's the sql query.  Put a break point on the query itself:

sql = "Select * from ClaimsSummaryQry where ID = " & Me.ID & ""

then press CTRL-G to bring up the debugger.  In the debugger try:

?Me.id

if nothing shows then it's not reading the id value from your form.

Another test would be to put in an id instead of reading the id.

sql = "Select * from ClaimsSummaryQry where ID = 10"  '<<--try putting in a valid id
0
 

Author Comment

by:LLWIT
ID: 13975962
It still couldn't find the data source. I have tried amending the following line:

.OpenDataSource NAME:=C:\Clients_be.mdb, SQLStatement:=sql

This will bring in the data source however I'm finding that it opens another instance of the database and the Word document prompts to overwrite the existing Normal.dot.

Should this be happening?
0
 
LVL 13

Accepted Solution

by:
Lucas earned 2000 total points
ID: 13978004
Change this line:

myword.Documents("Document1").Close SaveChanges:=wdDoNotSaveChanges

To:

myword.Documents(file).close SaveChanges:=wdDoNotSaveChanges

Then go into your database Tools -> Start up and under application title make sure it's empty.  Then if that doesn't work, type in MS Access.

If that doesn't i can take a look at your database and template and see if i can get it to work .. my vitals are in the profile.
0
 

Author Comment

by:LLWIT
ID: 13986366
It looks like that did the trick. We did have to modify your code a little but it's all worked out. Thanks very much for your help.
0
 

Author Comment

by:LLWIT
ID: 13986710
Lucas

I have a question related to this project.

Can you tell me how to get Access to display the true value rather than the reference identifier?

For example, the clients table has a number of linked table holding the choices. These choices appear as e.g. 1, 2 once merged to Word instead of displaying Choice 1, Choice 2. I have seen (in the case of reports written in Access) you can write code to translate these values however the choices are dynamic i.e. will grow in time.

I will in due course post this question on the forum but just wondered if you know of a solution already?
0
 
LVL 13

Expert Comment

by:Lucas
ID: 13987479
I'm not sure if i understand your question but i think i do.

If you want to display something else other than choice 1 or choice 2, before you dump the data to Word, you can add an iif expresion in your query.  Example:

One of your fields should be something menaning full, i used choices and then i put the iif right after it.

Choices: iif([choice number]=1, "Nice choice", iif([choice number]=2,"Bad choice"))

Other than that you can also write a function and make it dynamic by passing in a choice number into it, then in the function you can use if statements or case statements and then return the appropriate choice value.

Then you can call the function in your query or sql statement.

Hope that helped.
0
 

Author Comment

by:LLWIT
ID: 13994987
I found a way around it using the Inner Join method in sql. It's meant a bit of a overhaul of the system but it's working now. Thanks for all your help.
0
 
LVL 13

Expert Comment

by:Lucas
ID: 13995975
Bonus ... glad i could help.
0
 

Author Comment

by:LLWIT
ID: 14411070
Lucas

Since implementing your solution we have found that there are certain problems with the data merged. Namely date fields are merging in US format i.e. mm/dd/yyyy if there has been input. When the field is blank the value of today's date is merged. To fix the date format I have used the picture switch method i.e. \@ "dd/mm/yyyy" which gives the correct date format. Time fields are merging with "12:00am" if left blank. I have found that it is to do with the OLEDB connection and DDE should used but the code I have seen deployed is very different to the solution you provided us. Can you perhaps help? This is the code we are currently using :

Private Sub Command659_Click()
Dim myword As Object
Dim file As String
Dim sql As String

Me.Refresh
DoCmd.SetWarnings (warningsoff)
DoCmd.OpenQuery "ClaimsSummaryQry"



file = "G:\Claims\DBdocs\AMCsummarysheet.doc"  '<----CHANGE THE FILE NAME HERE

sql = "Select * from claimsmerge"

Set myword = CreateObject("Word.Application")

'IF YOU SAVED YOUR TEMPLATE AS A DOC THEN USE THE LINE BELOW



myword.Documents.Open file, , True

myword.Application.DisplayAlerts = wdAlertsNone
With myword.ActiveDocument.MailMerge
    .MainDocumentType = wdFormLetters
    .ViewMailMergeFieldCodes = False
    .Execute True
End With

myword.Documents(file).Close SaveChanges:=wdDoNotSaveChanges
myword.Application.Visible = True
myword.Application.WindowState = wdWindowStateMaximize
 
Set myword = Nothing
End Sub

Thanks
0
 
LVL 13

Expert Comment

by:Lucas
ID: 14412727
I think you'll have to edit your query.  Add this iif statement to where your time field is:

=iif(isnull([yourtimefield]),format(now,"medium time"),[yourtimefield])

The above iif statement looks for a null time field and replaces it with the current time, otherwise it will display the time field stored already.

I'm not sure if that's what you meant.
0
 

Author Comment

by:LLWIT
ID: 14432476
We found out the merging problems we are experiencing is as a result of the OLEDB connection as opposed to the DDE connection which is deployed in Word 2000. Anyway, we found a different way to doing the merge but thanks for you suggestion.

Cheers
0
 
LVL 13

Expert Comment

by:Lucas
ID: 14432536
No problem...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question