Word Mailmerge automation

[CJ UK]

Access to Word Mail Merge Automation

Hi all.

I was wondering if anyone can help?

I have been trying to automate a mailmerge from Access 2002 to Word 2002 (xp) but with no luck. I have a query that I wish to use as the datasource. I have found some code for the 97 version but can't get it to work in xp. I have tried the knowledge-base and have gone through this forum but am still struggling. I want to use a button to launch the mailmerge.

Does anyone have an example? Threads I have read seem to suggest that you can come across many problems trying to make the automation work completely without any additional input? I want the resulting letter to print off automatically and I do not need MSWord to be visible. The code I have managed to put together so far is below. How do I refer to a document set up with the merge fields and get the query results to go in there?

Any Help would be greatlly appreciated.

Private Sub runword_Click()
On Error GoTo Err_runword_Click

Dim oApp As Object

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

Set oMainDoc = oApp.Documents.Add

With oMainDoc.MailMerge

.MainDocumentType = wdFormLetter

sDBPath = "C:\Documents and Settings\Carl\Desktop\Database Research\CarlClientdbAppt\Copy of ClientdbAppt.mdb"
.OpenDataSource Name:=sDBPath, _
Connection:="QUERY queryapptletter", _
SQLStatement:="SELECT * FROM [queryapptletter]"


Exit_runword_Click:
Exit Sub

Err_runword_Click:
MsgBox Err.Description
Resume Exit_runword_Click


End With
End Sub


Thanks in Advance.

 

[Dembrey]

Any reason why you can't just create your letter as a report and print it directly from Access?

 

[CJ UK]

Thanks for your reply.

I have considered using a Report but have encountered a few problems with the layout. I am sure that these could be overcome with a little patience.

The main reason that I wanted to automate the process was so that the letter would print automatically (x2) as soon as the "Confirm Appointment" button was pressed. I want to remove as much as possible the risk of admin staff not completing the whole process as we have recently had problems with letters not being sent.

Is there a way to do this with reports?

In addition to this, to be completely honest, I haven't had any experience of using reports, but also little experience using VB.

I am learning as I go! So far I have learnt to create the records and included input masks, combo boxes, check boxes etc. I have written a query, created a one-to-many relationship and my biggest achievement to-date, an appointment booking system.

This is all thanks to members of this forum.

I will try using a report and let you know how I get on.
I am still interested though in learning how to automate mailmerge.

Thank you. (all)

 

[Fizzio]

I use this code

Public Sub MergeToWord(strDocName As String)
Dim objApp As Object, MyDb As DAO.Database

DoCmd.Hourglass True

'Open Mailmerge Document
Set objApp = CreateObject("Word.Application")
With objApp
    .Visible = True
    .Documents.Open strDocName
    .ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:="QUERY MyQuery"
End With


'Create, print and close Document
With objApp
    .ActiveDocument.MailMerge.Execute 'execute mailmerge
    .ActiveDocument.PrintOut Background:=False, Copies:=1 'print out 1 copy
    .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
    .Quit SaveChanges:=wdDoNotSaveChanges 'close all documents
End With


Set objApp = Nothing
Set MyDb = Nothing

ErrorHandler:
Select Case Err.Number
Case 4157
End Select

DoCmd.Hourglass False
End Sub

Just use by

Call MergeToWord(DocumentName)

 

[Dembrey]

Hi CJ,

If Fizzio's code works for you, you're all set.

However as for reports they are easily printed out using the 'OpenReport' method with the view argument set to acViewNormal (or left blank). See the Help for more details.

HTH

 

[CJ UK]

Hi Fizzio

Thanks for the code.

Me being a newbie, I'm having a little trouble on how to implement the code. Lots of reference to .ActiveDocument, does this mean that the document I want to merge to needs to be open?

What do I click on, select, run etc to implement the code, no reference to a button? Do I create this as a module and then run the module from a cmdbutton?

I assume that the following line is to make sure that the document containing the body text is not over-written, where is the reference to this doc? is there one?
.Quit SaveChanges:=wdDoNotSaveChanges 'close all documents

I need to print two letters but I think I can work that one out for myself! lol

Also, as I'm replying to someone who has already proved to be exceptionally helpful in the creation of my little project....(that's me brown-nosing)!.......how do I make sure I only have one result in my query, rather than them stacking up in the table each time I run the query? - I envisage that my mail merge would merge the same query result everytime, so Joe Bloggs would be quite
annoyed with all the appointments I kept sending him.

Again, thanks in advance for any help, guidance or wake-up calls you an offer.

Carl.

 

[Fizzio]

This may make it more helpful. Paste this code into its own module.

Public Sub MergeToWord(strDocName As String, MyQuery as String)
Dim objApp As Object

'[COLOR=red]Change cursor to hourglass[/COLOR]
DoCmd.Hourglass True  

'Open Mailmerge Document
'[COLOR=red]Start Word[/COLOR]
Set objApp = CreateObject("Word.Application")
With objApp
    .Visible = True '[COLOR=red]Make it visible[/COLOR]
    .Documents.Open strDocName '[COLOR=red]Open the Mailmerge Document[/COLOR]
    '[COLOR=red]Use the Query defined in the arguments as the datasource[/COLOR]
    .ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:="QUERY " & MyQuery 

End With 

'Create, print and close Document
With objApp
    .ActiveDocument.MailMerge.Execute 'execute mailmerge
    .ActiveDocument.PrintOut Background:=False, Copies:=1 'print out 1 copy
    .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges '[COLOR=red]Avoid Saving over your template[/COLOR]
    .Quit SaveChanges:=wdDoNotSaveChanges 'close all documents
End With


Set objApp = Nothing

ErrorHandler:
Select Case Err.Number
Case 4157
End Select

DoCmd.Hourglass False '[COLOR=red]Cursor back to normal[/COLOR]
End Sub

To use the code, eg behind the event of the button

Private Sub YourButton_OnClick()
Call MergeToWord("C:\Documents\Test.doc", "qryClientLetter") 'For example
End Sub

If you want the code to only return one result, in the query "qryClientLetter" set the criteria in the field you want to filter on (typically the ID) to the ID on the form ie

=Forms!NameofForm!IDField

does this help?

You have to already set up a word mailmerge template with the merge fields in (but with the datasource removed as Access sets this dynamically in the code)

 

[CJ UK]

Hi Fizzio,

Thanks for the reply.

I have created the module and altered the query reference to match my query (qryapptletter). Still not sure what I need to do to my query to not accumulate results.

Also not sure, now that I have got the mailmerge module, how to run the code through the onclick command on my appointment form?

Thanks.

 

[Fizzio]

Look at the post above for the code that you put into the OnClick event of the button, I've used YourButton as the button Name.

To limit the recordset, look at my previous post at the bottom to see how to restrict the mailmerge to only one record by using the criteria inn one of the fields in your qryapptletter

 

[CJ UK]

Nearly there!

Hi all.

I have implemented the code as suggested by Fizzio. I have got it up and running but I'm having a few minor problems.

When I hit my "Create Letter" button, word is starting, opening up my merge doc but it is bringing up this list of data sources for me to choose from, I'm not sure why this is as I have specified the source already in the code. I have added Miscrosoft Word 10.0 Object Library to my references. (when I select the apporpriate query, it continues to operate as expected and prints out the merged document). Do I remove the datasource in word by changing it to a 'Normal Document' through the 'Main Document Properties' button? - is this where I've gone wrong?

Also, still not sure how to merge on the most recent query result instead of the first. My query contians:

From tblClients : Title, Surname, Address1, Address2, AddressTown, AddressCounty, Postcode

From tblAppointment: AppointmentTime, AppointmentDate, AppointmentLocation, AppointmentAdviser, AppointmentType.

The two tables are connected by [ClientID] in a one-to-many relationship.

I'm not sure what criteria to enter and where?

Thank you in advance for any help.

Carl.

 

[Fizzio]

You are correct about removing the datasource. In the options in the mailmerge helper, behind the Create button select restore to normal word document. You will see that your merge fields have remained on the page.

Regarding the query, which record do you want to merge. Is it the currect record on the form or a particular client. If you want to filter the current client on the form, try this SQL.

SELECT Title, Surname, Address1, Address2, AddressTown, AddressCounty, Postcode, tblAppointment.AppointmentTime, tblAppointment.AppointmentDate, tblAppointment.AppointmentLocation, tblAppointment.AppointmentAdviser, tblAppointment.AppointmentType
FROM tblClients INNER JOIN tblAppointments ON tblClients.ClientID=tblAppointments.ClientID
WHERE (((tblClients.ClientID)=[Forms]![YourFormName]![ClientID]));

(I think the sytax is correct) YourFormName is the name of the form with the button on.

 

[CJ UK]

Hi Fizzio

I have put the SQL you suggested in the criteria box under [ClientID] in my query. I am getting message:

'The syntax of the subquery in this expression is incorrect'

not sure how to remedy this?

Did you notice in my previous post that I am having to select the appointment query from a list when word is launched? I don't understand why when I have already told it which query I want to use???

Thank you.

 

[Fizzio]

As I posted before, you need to strip the datasource from the word document by converting it to a normal document.

The SQL is not to go in the criteria box. Go into the Query Design Grid, select SQL view and then paste the code above there. SQL is the query.

 

[johnLH]

Hi all

I have a form to display selected records (eg by town, county, etc). I need to be able to setup a mailmerge to MS Word using the existing recordset. Does anyone know if this is possible? I've spent ages searching for a suitable solution without much success (I'm using Access/Word 2002).

The people who will be using the database won't have a clue about using Word mailmerge so it needs to happen without them having to select tables, queries, files etc.

I get the feeling Access/Word automation is an area Microsoft need to look at developing - you'd think it would be a basic requirement

Thanks

John

 

[johnLH]

Could this be used to mailmerge the current recordset rather than refer to a specific query? For example, a user on the main form filters the database on 'town' name then sends the filtered records for mailmerge?

On the current setup I converted the document to a normal doc but I still get asked to select the query from a list!

 

[Sergeant]

Did you see this post with Jon_K's attached sample database?
(I don't know much about it, I had that bookmarked from ages ago.)

 

[johnLH]

Yeh, thanks I had seen that. Jon_k's database mailmerges the current record but I need to mailmerge the existing filtered record set - which will be more than one record depending on the users filter criteria (eg town, county, date range etc.)

 

[Dan_T]

John I am in the same position as you are. How far have you got? I have tried by creating a template, then by creating a word doc that has been set up for mailmerge and now a new word document. However I am stuck here:

I open a document. Set the connection? (I think) then I am asked by word about the User and Password. I want to avoid this but how? Unfortunately I am at the stage where I have tried so many combinations I can't recal the best one so far.... I think I'll start over again.

 

[Dan_T]

When you say you have converted a mailmerge to a normal document does that mean you have to first create a mail merge document and then turn it into a normal document or can you just start with a blank document?

Many Thanks

Dan

 

[johnLH]

Hi Dan

For the time being I've opted for the easy option. I created a form based on a table with letter heading, body text and signature fields (memo). The user clicks a 'compose letter' button and types the letter info in the fields. Another button next to the compose button prints a report containing the contact and address details plus the above letter fields (letter table added to the query). The report is printed using the following code on the print button to restrict to the current recordset:

Private Sub cmdMailMergeLetter_Click()
On Error GoTo Err_cmdMailMergeLetter_Click

Dim stDocName As String

stDocName = "rptMailMergeLetter"
DoCmd.OpenReport stDocName, acPreview
With Reports(stDocName)
.Filter = Me.Filter
.FilterOn = True
End With

Exit_cmdMailMergeLetter_Click:
Exit Sub

Err_cmdMailMergeLetter_Click:
MsgBox Err.Description
Resume Exit_cmdMailMergeLetter_Click

End Sub


Not really the solution I was looking for but it will do for the time being. Think I need to sit down and get into some serious study on Access VBA

John