Automation Mail merge from Access2000 for Office 97 and 2000 users

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 2000 database that currently uses Automation to open word,
and then inserts text at preset bookmarks. It currently causes an illegal
operation error when used with Word 97 though.

Is there a way to mail merge from Access 2000 so that it will work for 2000
and 97 users?

The existing code (working with word 2000) is ugly and consists of a hugh
case statement switched on users choice of template with all of the bookmarks
for each template hard coded into each case block.

I want to change this so that users can add and configure their own
templates. This part I have sorted but when I try and use the same variable
declarations, objects, Set and create statements that have been used in the
code already working, I get error messages saying that the method is not
available.

Why would methods and properties of an object available in one part of the
code not be available to new objects of the same type in another part of the
code?

The code that follows is currently failing at : WordObj1.Documents.Add
template:="c:\Program Files\StandardLetters\" & strTemplate & ".dot",
newtemplate:=False

Error: Object doesn't support this property or method

Any Help would be much appreciated.
Anna

Private Sub MergeLetterButton_Click()
'Merges patient data to the Word letter template specified on the
PatientLetters Form
On Error GoTo Err_MergeLetterButton_Click

Dim rstBookmarks As Recordset 'The recordset for the PatientLetters form
contains the names of
'letter templates, their associated bookmark
names, the combobox
'name containing the merge text and the column
number of the
'merge text in the combobox.
Dim i As Integer 'For loop variable.
Dim strBookmark As String 'The name of the bookmark in Word Template.
Dim strWord As String 'The text to merge to Word Template.
Dim strQuery As String 'The name of the combobox on the LettersForm
holding text to merge
Dim intColumn As Integer 'Column number in the combobox where text is
located.
Dim strTemplate As String 'Name of the Word Template

Dim dbThisDatabase As Database
Dim WordObj1 As Word.Document

Set rstBookmarks = Forms!frm_PatientLetters.Recordset
strTemplate = Me!TemplateCombo.Column(1)
If rstBookmarks.RecordCount = 0 Then
MsgBox ("No Bookmarks defined for this template!")
Else

'Open a new Word document for merging


Set dbThisDatabase = CurrentDb()
DoCmd.Hourglass True
Set WordObj1 = GetObject("C:\Program
Files\Genesis\StandardLetters\AppointmentLetter.dot", "word.document")
If Err.Number <> 0 Then
Set WordObj1 = CreateObject("word.application")
End If

WordObj1.Application.Visible = True
WordObj1.Documents.Add template:="c:\Program Files\StandardLetters\" &
strTemplate & ".dot", newtemplate:=False

strBookmark = ""
strWord = ""
strQuery = ""
intColumn = 0
'Loop through the bookmarks retrieving the bookmark name, and its
corresponding
'merge string.
With rstBookmarks
.MoveFirst
For i = 1 To .RecordCount
strBookmark = !BookmarkName
intColumn = !ColumnNumber

If IsNull(!QueryName) Then
MsgBox ("Null Value")
Else
strQuery = !QueryName
Select Case strQuery
Case "PatientAddressCombo"
strWord = Me!PatientAddressCombo.Column(intColumn)
Case "PatientCombo"
strWord = Me!PatientCombo.Column(intColumn)
Case Else
MsgBox "Can't find the specified control"
End Select
'Insert the text into the open work document at the bookmark
MsgBox ("Bookmark is : " & strBookmark & " Value is : " &
strWord)
WordObj1.Goto what:=wdgotobookmark, Name:=strBookmark
'WordObj1.Selection.TypeText Text:= strWord
WordObj1.Selection.TypeText Text:="Type in this text"

.MoveNext

End If
Next i
End With
WordObj1.Activate
WordObj1.Selection.moveup wdline, 40
' Set the Word Object to nothing to free resources
Set WordObj1 = Nothing
DoCmd.Hourglass False

End If

Exit_MergeLetterButton_Click:
Exit Sub

Err_MergeLetterButton_Click:
MsgBox Err.Description
Resume Exit_MergeLetterButton_Click

End Sub
 
I am going to suggest you give my word merge a try.

It has many good things going for it:

-- it words with word 97, word 2000, word2003
-- it even works when the version of word is NOT the same as the
ms-access version
-- it eliminates the need for those HORRID and HARD to use bookmarks
-- since I don't use book marks, then END USERS and people with no
training can EASLEY use the mail merge, and creating THEIR OWN templates.
-- since I don't use book marks, then you no change in code is required
(this cod works for all tables..and each time you need
a mail merge..you don't have to change the code!!!!).

You can read up on the notes as to why, and how my code works will all
versions for word.

You can find the example here:
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

Please Spend some time reading on how to use my system with YOUR
application.However, to use it is easy, you simply place a button on form
and then use ONE lone of code behind that button. The marvelous thing here
is that you can use a one line of code to now enable ANY of your forms to
merge the one record you are looking at.
 
I'm using the code to create a mail merge from Albert D. Kallal
([email protected]) on
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html. This code is
FANTASTIC and I give praise to the author.

I've noticed that if you link a datasource directly from Microsoft Word the
merge is relatively quick. Unfortunately, using automation from Access with
large recordsets in excell of 1000 records can take a while to open Word and
start merging the records. It may have to do with Spell-check or other
various tools that are running in Word? Is there a way to speed up the merge
process using automation from Access?
 
Back
Top