Launching MS Word mail merge from an Access form

[Rob Sang]

I have a form in to enter a new record into a table. I also have a mail merge document in Word based on that same table. What I would like is if the button for adding a record also opened the mail merge document and merged it with the data from that record.

I'm not afraid to get my hands dirty code-wise but I don't have a lot of experience with coding in Access specifically. I read a knowledge base article (209976) which seemed to relate to this but I didn't really understand how to integrate the code they suggested into mine, in particular how to link it to the form button.

Can anybody out there help me?

 

[omoregbe]

Hi,,,,

Hope this answers your question. All you need to do is to incorporate the code you found in article (209976) behind the button you crerated, modify some of the syntax to suite your task and see what happens.

Good Luck

Nick

 

[Rob Sang]

I tried that but I get an error telling me that a user defined type has not been defined. It seems to be referring to Word.Document. I think this is because the code has to be defined in a module but if that's so I don't know how to execute it from the form. I also don't know how to refer to fields in the form if the code is in a module.

 

[sparklegrrl]

Rob -

I am working on EXACTLY the same thing and having a similar problem.

Did you get this figured out?

Here is the module I created:

Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("f:\DatabaseFiles\GATX INSPECTION FORMS.doc", "Word.Document")
' Make Word Visible.
objWord.Application.Visible = True
' Set the mail merge data source as the newchandler database.
objWord.MailMerge.OpenDataSource _
Name:="f:\newchandler.mdb", _
LinkToSource:=True, _
Connection:="QUERY GATXINSPECTIONWORDquery ", _
SQLStatement:="Select * FROM [GATXINSPECTIONWORDquery]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

 

[Rob Sang]

Actually, yes I did figure it out. I didn't use a module in the end, I adjusted the code so that it fit into the button click subroutine. The code from the microsoft site (the module you've based your code on) seems to be flawed also, as the line

Dim objWord As Word.Document

results in a compile error. Here is the code I wrote in the end, it works like a charm (although very slowly on some of the older machines I've installed it on).

Private Sub merge_Click()
On Error GoTo Err_merge_Click
'Developed by Robert Sang for Connect Learning Centres 2003

'Load the mail merge document as an object
Dim objWord As Object
Set objWord = GetObject("C:\Documents and Settings\Lib Staff\Desktop\newdb\merge2.doc", "Word.Document")
'Make Word visible
objWord.Application.Visible = True

'Open the mail merge data source (this database and record)
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\Lib Staff\Desktop\newdb\newdb.mdb", _
LinkToSource:=True, _
Connection:="TABLE Customers", _
SQLStatement:="SELECT * FROM [Customers] WHERE [MailingListID] = " & Forms!learners!MailingListID
'Execute the mail merge
objWord.MailMerge.Execute

Exit_merge_Click:
Exit Sub

Err_merge_Click:
MsgBox Err.Description
Resume Exit_merge_Click

End Sub

 

[Fornatian]

If you only merge one record and don't actually need to create a merged document is to export the single record as delimited file called mysource.txt which is the word documents record source.

Then open the word document with Docmd.FollowHyperlink "PathToWordDoc"

This will automatically cause word to poll the text file you just created for its field values.

This approach doesn't work in all situations but does offer an easy alternative if you are confident that users arent going to mess with your template, otherwise the application object method is the route to follow.

 

[skiefer]

I know this post is a little old, but i've used it to setup a mail merge. I'm hoping someone can answer this question for me.

When I try to run this mail merge I get an error opening up the data source from the word document because the database has a lock on it. The database will always be open when I run this command because I'm running it from a form.

How can I force the word document to open the .mdb in read only?

HELP!!

Thanks in advance for any advice,
Steph

 

[HeatherO]

I am trying to add a button to my form that will run a mail merge on just the current record.... not sure how to do this and cannot find anything online that is working. I am not very familiar with VBA and would appreciate any advice. Any ideas? Below is what I have so far. It works, but is merging all of the records instead of just the current record.


Private Sub Command154_Click()
Dim objWord As Word.Document
Set objWord = GetObject("Y:\Waiting List\Word Docs\Guaranty FormsTest.docx", "Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource _
Name:="Y:\Databases\Residential.accdb", _
LinkToSource:=True, _
Connection:="QUERY Lease Details", _
SQLStatement:="SELECT * FROM [Lease Details]"

objWord.MailMerge.Execute
End Sub

 

[Gasman]

This thread is almost 14 years old

You would need a WHERE clause in the SQLStatement

something long the lines of

SQLStatement:="SELECT * FROM [Lease Details] WHERE ID = " & Me.ID

ID will be the name of the key in Lease Details and Me.ID the key of the record on the form. Adjust names to suit, but you have to identify which record(s) you want for the merge.

HTH

 

[HeatherO]

I know... but desperate times call for desperate measures! This worked almost perfectly... but now I have a whole other issue. One of the fields I'm trying to merge is a lookup field. The merge is populating with the primary key value instead of the data from the field....?? Thanks in advance for any assitance!!

 

[Gasman]

I know... but desperate times call for desperate measures! This worked almost perfectly... but now I have a whole other issue. One of the fields I'm trying to merge is a lookup field. The merge is populating with the primary key value instead of the data from the field....?? Thanks in advance for any assitance!!

You would need to link the lookup table with your table to get the actual value.

If I was doing this, I would do the query in the query designer then amend the sql for vba, as my sql from scratch is not that good.
So in the query designer I would enter a value that would produce a record and then amend that for the control value as I mentioned previously.

Get the query working as you need it, then modify for vba.

There is a program that will do a lot of this work for you, you just need to build the form to support it.
Might not be needed for this query as it is quite simple, but handy for the future.?

http://allenbrowne.com/ser-71.html

FWIW there would be nothing wrong with you starting your own thread and linking to this one for background info.