Automating mail merge into a Word document

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

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?

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.

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

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

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?

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.

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.

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

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?

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.

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?

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.

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.

Bonus ... glad i could help.

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

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.

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

No problem...