By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Mail merge problem

P: 28
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command36_Click()
  2. Set objWord = GetObject("C:\Documents and Settings\xxxx\My Documents\merge.doc", "Word.Document")
  3. 'turn off alerts
  4. objWord.Application.DisplayAlerts = wdAlertsNone
  5. ' Make Word visible.
  6. objWord.Application.Visible = True
  7. ' Set the mail merge data source as the OLF Front End database.
  8. objWord.MailMerge.OpenDataSource _
  9. Name:="C:\Documents and Settings\xxxx\My Documents\NewSystem.mdb", _
  10. LinkToSource:=True, _
  11. Connection:=strConnect
  12. objWord.MailMerge.DataSource.QueryString
  13. ' Execute the mail merge.
  14. objWord.MailMerge.Execute
  15. End Sub
  16.  
I get an error saying "Runtime error 13: type mismatch" and this code is highlighted:
Expand|Select|Wrap|Line Numbers
  1. objWord.MailMerge.OpenDataSource _
  2. Name:="C:\Documents and Settings\Ewan Farr\My Documents\NewSystem.mdb", _
  3. LinkToSource:=True, _
  4. Connection:=strConnect
  5.  
Any ideas what is wrong or is the code right in the 1st place?
Thanks
ATC
Mar 5 '07 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,607
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command36_Click()
  2. Set objWord = GetObject("C:\Documents and Settings\xxxx\My Documents\merge.doc", "Word.Document")
  3. 'turn off alerts
  4. objWord.Application.DisplayAlerts = wdAlertsNone
  5. ' Make Word visible.
  6. objWord.Application.Visible = True
  7. ' Set the mail merge data source as the OLF Front End database.
  8. objWord.MailMerge.OpenDataSource _
  9. Name:="C:\Documents and Settings\xxxx\My Documents\NewSystem.mdb", _
  10. LinkToSource:=True, _
  11. Connection:=strConnect
  12. objWord.MailMerge.DataSource.QueryString
  13. ' Execute the mail merge.
  14. objWord.MailMerge.Execute
  15. End Sub
  16.  
I get an error saying "Runtime error 13: type mismatch" and this code is highlighted:
Expand|Select|Wrap|Line Numbers
  1. objWord.MailMerge.OpenDataSource _
  2. Name:="C:\Documents and Settings\Ewan Farr\My Documents\NewSystem.mdb", _
  3. LinkToSource:=True, _
  4. Connection:=strConnect
  5.  
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:
Expand|Select|Wrap|Line Numbers
  1. strConnect = "Provider=Microsoft.Jet.OLEDB.4.0
Mar 5 '07 #2

P: 28
atc
Thanks for your help.
Now I am getting an error for
Expand|Select|Wrap|Line Numbers
  1. objWord.MailMerge.DataSource.QueryString
  2.  
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
Mar 6 '07 #3

ADezii
Expert 5K+
P: 8,607
Thanks for your help.
Now I am getting an error for
Expand|Select|Wrap|Line Numbers
  1. objWord.MailMerge.DataSource.QueryString
  2.  
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.
Expand|Select|Wrap|Line Numbers
  1. Dim Doc As Document
  2. Dim MyDS As MailMergeDataSource
  3.  
  4. Set Doc = ????
  5. Set MyDS = Doc.MailMerge.DataSource
Mar 6 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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 ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command36_Click()
  2. Dim WordApp As New Word.Application        
  3. Dim WordDoc As Word.Document        
  4. Dim MailMerge As Word.MailMerge           
  5.  
  6. WordApp.Visible = True
  7. Set WordDoc = WordApp.Documents.Open("C:\Documents and Settings\xxxx\My Documents\merge.doc", True)        
  8.  
  9. Set MailMerge = WordDoc.MailMerge           
  10.  
  11. With MailMerge
  12.    .OpenDataSource Name:="C:\Documents and Settings\xxxx\My Documents\NewSystem.mdb", _ 
  13.       LinkToSource:=True, _
  14.       Connection:="TABLE Name" ' put name of table instead of name here or
  15.       ' if using a query put QUERY Name
  16.     ' Execute the mail merge.
  17.    .Execute
  18. End With
  19.  
  20. End Sub
  21.  
Mary
Mar 6 '07 #5

P: 28
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
Mar 7 '07 #6

P: 28
atc
If I click cancel when the message box comes up. My templete does not merge but the new document does not appear.
ATC
Mar 7 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
John

Did you change this line of code to reflect your query name.
Expand|Select|Wrap|Line Numbers
  1. Connection:="TABLE Name" ' put name of table instead of name here or
  2.       ' if using a query put QUERY Name
  3.  
Assuming a query named qryDummy ...
Expand|Select|Wrap|Line Numbers
  1. Connection:="QUERY qryDummy" 
  2.  
Mary
Mar 9 '07 #8

P: 28
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
Mar 10 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry John

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

Mary
Mar 11 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.