Mail merge problem

[atc]

Sorry but another question from a newbie.
I am trying to create a mail merge based on a query. I have a button on a form (form is based on query). I found this code in one of the discussions and tried to modify it for my needs.

Private Sub Command36_Click()
Set objWord = GetObject("C:\Documents and Settings\xxxx\My Documents\merge.doc", "Word.Document")
'turn off alerts
objWord.Application.DisplayAlerts = wdAlertsNone
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the OLF Front End database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\xxxx\My Documents\NewSystem.mdb", _
LinkToSource:=True, _
Connection:=strConnect
objWord.MailMerge.DataSource.QueryString
' Execute the mail merge.
objWord.MailMerge.Execute
End Sub
 

I get an error saying "Runtime error 13: type mismatch" and this code is highlighted:

objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\Ewan Farr\My Documents\NewSystem.mdb", _
LinkToSource:=True, _
Connection:=strConnect
 

Any ideas what is wrong or is the code right in the 1st place?
Thanks
ATC

[ADezii]

Sorry but another question from a newbie.
I am trying to create a mail merge based on a query. I have a button on a form (form is based on query). I found this code in one of the discussions and tried to modify it for my needs.

Private Sub Command36_Click()
Set objWord = GetObject("C:\Documents and Settings\xxxx\My Documents\merge.doc", "Word.Document")
'turn off alerts
objWord.Application.DisplayAlerts = wdAlertsNone
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the OLF Front End database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\xxxx\My Documents\NewSystem.mdb", _
LinkToSource:=True, _
Connection:=strConnect
objWord.MailMerge.DataSource.QueryString
' Execute the mail merge.
objWord.MailMerge.Execute
End Sub
 

I get an error saying "Runtime error 13: type mismatch" and this code is highlighted:

objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\Ewan Farr\My Documents\NewSystem.mdb", _
LinkToSource:=True, _
Connection:=strConnect
 

Any ideas what is wrong or is the code right in the 1st place?
Thanks
ATC

You haven't defined strConnect. It should look something like:

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0

[atc]

Thanks for your help.
Now I am getting an error for

objWord.MailMerge.DataSource.QueryString
 

The error is "QueryString is not a method"
Also when word is lanuched I have to select the query i want to merge. Is there any way I can get round this.
Thanks again
ATC

[ADezii]

Thanks for your help.
Now I am getting an error for

objWord.MailMerge.DataSource.QueryString
 

The error is "QueryString is not a method"
Also when word is lanuched I have to select the query i want to merge. Is there any way I can get round this.
Thanks again
ATC

I think your syntax is all wrong. This is not my Field, but it seems you need to Declare an Object as MailMergeDataSource and a Document Object in order to accomplish this, not a reference to a Word Object.

Dim Doc As Document
Dim MyDS As MailMergeDataSource
 
Set Doc = ????
Set MyDS = Doc.MailMerge.DataSource

[MMcCarthy]

John

There are a couple of things here.

1. Make sure the Office Word library is ticked in the Tools - References list.
2. I'm not sure if you've correctly declared your variables.
3. I'm assuming you are trying to automate Word from Access and not the other way around.

Try this code ...

Private Sub Command36_Click()
Dim WordApp As New Word.Application        
Dim WordDoc As Word.Document        
Dim MailMerge As Word.MailMerge           
 
WordApp.Visible = True
Set WordDoc = WordApp.Documents.Open("C:\Documents and Settings\xxxx\My Documents\merge.doc", True)        
 
Set MailMerge = WordDoc.MailMerge           
 
With MailMerge
   .OpenDataSource Name:="C:\Documents and Settings\xxxx\My Documents\NewSystem.mdb", _ 
      LinkToSource:=True, _
      Connection:="TABLE Name" ' put name of table instead of name here or
      ' if using a query put QUERY Name
    ' Execute the mail merge.
   .Execute
End With
 
End Sub
 

Mary

[atc]

Thanks for your help mary. I modified your code for my database. Word loads as expected but word brings up a message box called "select table". It has queries and tables in it. So I select the query I want. Then word loads up a new document called "Form Letters1". It is the same as my template. Is there any way to modify the code so the message box does not appear and the new document is not created.
Thanks again
ATC

[atc]

If I click cancel when the message box comes up. My templete does not merge but the new document does not appear.
ATC

[MMcCarthy]

John

Did you change this line of code to reflect your query name.

Connection:="TABLE Name" ' put name of table instead of name here or
      ' if using a query put QUERY Name
 

Assuming a query named qryDummy ...

Connection:="QUERY qryDummy" 
 

Mary

[atc]

Yes i changed the line of code so its revelvent to my project.
So i have no idea whats up with it.
Any more ideas?
john

[MMcCarthy]

Sorry John

Not at the moment but I'll see if I can find out anything else.

Mary