Mail Merge document locked when opened from Access

[retro]

I have a Mail Merge letter in Word that gets its information from an Access database.

If I open the Word document manually, I get a message box which says:

Opening this document will run the following SQL command:

SELECT etc...

Data from your database will be placed in the document. Do you want to continue?

[Yes] / [No]

When I select yes, the document opens up on the last record, and I can browse through the records using the navigation controls on the Mail Merge toolbar. This is how I want it to be.

However, I have a form which opens the document using the following code:

Dim LWorddoc As String
Dim oApp As Object

'Path to the word document
LWorddoc = "C:\folderpath\docname.doc"

If Dir(LWorddoc) = "" Then
    MsgBox "Document not found."
Else
    'Create an instance of MS Word
    Set oApp = CreateObject(Class:="Word.Application")
    oApp.Visible = True

    'Open the Document
    oApp.Documents.Open filename:=LWorddoc
End If

This opens the Word document successfully, again showing the last record. However, the navigation controls (in fact, everything bar Main document setup and Open Data Source) are greyed out. I noticed that it did not pop up the message box about running the SQL command.

How can I get around this?

Thanks in advance for any help offered!

 

[S1ip9]

Hi,

I have the same problem you do. Doing almost the same exact thing my self and getting the grayed out Mail Merge tool bar.

The best I have come up with is that we would have to recreate the document every time a user opens it. Seems like a lot of coding work just to have a Mail Merge document update every time it is opened.

Regards,
Keith

 

[GaryPanic]

No idea how to help on this one- but have a look at super easy word fuction in the samples

 

[retro]

Despite having tackled it a different way, I'm still having problems with this!

I have rewritten my Word template letter from scratch. When linking to the db, I used the DDE connection option (as some of my queries weren't showing up).

I'm trying to open the Word document from a form in Access 2003. There is an option group to determine which of two documents is opened (an initial letter or a follow-up). This uses Access-created button code, as follows:

Private Sub cmdLaunchWord_Click()
Dim MailShotFile As String
Dim MailShotPath As String
Dim LWorddoc As String
Dim oApp As Object

If grpMailShotType.Value = 1 Then
    MailShotFile = "pld mailmerge.doc"
Else
    MailShotFile = "pld mailmerge2.doc"
End If

'Path to the word document
MailShotPath = GetMyDocPath(Me)
LWorddoc = MailShotPath & MailShotFile

If Dir(LWorddoc) = "" Then
    MsgBox "Document not found."
Else
    'Create an instance of MS Word
    Set oApp = CreateObject(Class:="Word.Application")
    oApp.Visible = True

    'Open the Document
    oApp.Documents.Open filename:=LWorddoc
End If

MsgBox MailShotPath
MsgBox LWorddoc
MsgBox LWorddoc


End Sub

The Word documents are linked to a query in the database:

SELECT photolifedata.FirstName, photolifedata.Address, photolifedata.Address2, photolifedata.Address3, photolifedata.Address4, photolifedata.Town, photolifedata.County, photolifedata.PostCode, photolifedata.Title, photolifedata.LastName
FROM photolifedata
WHERE (((Forms!frmMailShotPLD!grpMailShotType)=1) And ((photolifedata.mergeno)=0)) Or (((Forms!frmMailShotPLD!grpMailShotType)=2) And ((photolifedata.mergeno)=1) And ((photolifedata.response)=0));

This is basically checking to see whether the leads are new (mergeno=0) or have already been mailed once before (mergeno=1) and haven't replied (response=0) and filtering accordingly. I use the same query on both letters.

In theory, as this is all done within Office, it should work! However, it doesn't.

If I use the form to open the letter, the mailmerge bar in Word is all greyed out and I can't navigate through the records.

If I close down Access and open the letter manually, it asks if I want to run the command:

SELECT * FROM [qryMailShotPLD]

It then opens Access and asks me to enter a parameter value (as I'm not running the form). I put 1.

The mailmerge bar is active and I can navigate freely!

If I have the database open in Access with the form open, then manually open the Word document, it again asks if I want to run the SQL command, and successfully opens the document and I can navigate the records.

Note that it doesn't ask to run the SQL command when I open the document from within Access. However, Access comes up with a number of dialog boxes:

F:\My Documents\

F:\My Documents\pld mailmerge.doc

F:\My Documents\pld mailmerge.doc

(yes, the last one comes up twice!)

I don't want the dialog boxes to appear at all, so that's another issue to rectify!

As I'm using Office's built-in wizards for opening a document and mail merging, I would have thought this should have been easy and worked out the box!

Does anyone have a solution?

I looked at the Super Easy document, but I couldn't get it to do anything near what I wanted - it really doesn't seem like a super easy answer!