Mail merge docmunets not loaded up correctly by VBA

[mistercormell]

I have created a variety of mail merged documents that are opened by a visual basic procedure using VBA from within my database system. e.g. the document is selected in Access from a drop down box in a dialogue box. The 'OK' button is clicked and visual basic loads up the document selected, my code is below:

Option Compare Database
Dim objWord As Word.Application


Private Sub cmdGOletter_Click()
Forms!frmLetter.cmbLetter.SetFocus
Set objWord = CreateObject("word.Application")
objWord.Visible = True
Select Case cmbLetter.Text
Case "Clients"
objWord.Documents.Open "d:\computing project\Letters\Clients.doc", , , False
Case "Funders"
objWord.Documents.Open "d:\computing project\Letters\Funders.doc", , , False
Case "Volunteers and Staff"
objWord.Documents.Open "d:\computing project\Letters\Workers.doc", , , False
Case "Everybody"
objWord.Documents.Open "d:\computing project\Letters\Everybody.doc", , , False
End Select

End Sub


**This code loads the correct document fine, but however disables the mail merge top toolbar on load - something that does not happen when the document that is being opened is opened directly from file.

It is important that my user can navigate between the records of the mail merged document otherwise they will only ever be able to do a personalised letter for the first person in the list!

Read only is not an issue as I have tried it with and without. at the moment I have set them to edit protection, so that the user can open in read only to print off, and a technical analyst can use the password to alter it.

Any help would be appreciated

 

[mistercormell]

has anyone got any light to shed on this problem?

 

[dcobau]

I do a similar process, using 1 doc at the time, without problems, the following is some of the code in the procedure:

objWord.Documents.Open "G:\IPLTemplates\OtherDoc\AcknowledgmentCanb.doc"

objWord.WindowState = wdWindowStateMaximize
objWord.ActiveWindow.WindowState = wdWindowStateMaximize
objWord.Activate
objWord.ActiveDocument.MailMerge.OpenDataSource "c:\temp\acknowledgment.txt"
objWord.ActiveDocument.MailMerge.Destination = wdSendToNewDocument

objWord.ActiveDocument.MailMerge.Execute
objWord.ActiveDocument.SaveAs "c:\temp\AckAttach.doc"

objWord.ActiveDocument.Close
objWord.ActiveDocument.Close , originalformat
objWord.Application.Quit

Set objWord = Nothing

Might be of help

Dave

 

[mistercormell]

I have got a little further, but have not yet found a solution to this problem. In my code, I only want it to load up a mail merged document as part of the coding. What is happenning when it is loaded up by VBA, is the data source is not opened and so that is why most of the mail merge toolbar is greyed out - as it thinks there is no source for this document when loaded up by VBA.

So I used the line of code from dcobau :

objWord.ActiveDocument.MailMerge.OpenDataSource "c:\temp\acknowledgment.txt"

and replaced it with:

objWord.ActiveDocument.MailMerge.OpenDataSource "d:\computing project\system\the base project.mdb"

This I have added below my original line:

objWord.Documents.Open "d:\computing project\Letters\Clients.doc", , , False

*Now what happens when the procedure is run, it loads up the document but prompts for the table/qry from this database to be selected. For the time being I selected this manually and everything was how it should be. However, how do I get VBA to select the appropriate table in my specified database (the data source)? - What code would I use here?

Your help is appreciated, Thanks

 

[mistercormell]

Problem solved:

I had a look on Microsoft's site, and found out that I just needed to add the line:

SQLStatement:= "SELECT * FROM [name of query or table]