A
Anna S
I have created a mail merge for my users which works well for 2000 users but
causes an illegal operation error when 97 users try to mail merge.
Users set can define a word document name and set of bookmarks in Access and
associate each bookmark with data from the database. Access then opens the
Word Document and inserts the data at each bookmark. I thought that
automating in this way would work for both 2000 and 97 users but I am getting
the illegal operation error and the application gets terminated in 97.
I have packaged the application and installed the Access Runtime on the 97
PCs. Can anyone tell me whether there is anything i mus include in the
package so that Access can merge for Office 97 users. Do I need a separate
runtime for Word?
The program code is included below, but shortened for ease of reading. Any
Advice would be greatly appreciated.
Thanks
Anna
Public Function MergeToWordTemplate(strTemplate As String, intTemplate As
Integer, rstBookmarks As Recordset) As Boolean
'This method creates a new document in MS Word using Automation and
inserts patient
'data at predefined bookmarks. Accepts a Word template name, ID and
recordset containing
'bookmark information. Returns False if an error occurred otherwise
returns True.
On Error GoTo HandleErr
On Error Resume Next
Dim dbDatabase1 As Database 'This database
Dim qryDefPatient As QueryDef 'Query definitions for each data source
query
Dim rstPatient As Recordset 'Recordset variables for each data
source query
Dim strBookmark As String 'Bookmark name
Dim strFieldName As String 'Field name in data source query
Dim strQuery As String 'Data source query name
Dim i As Integer 'For loop variable
Dim boolSuccess As Boolean
Dim strWord As String
Dim strPath As String
Dim strDocumentName As String
Dim strSavedLetterPath As String
Dim intSaveResponse As Integer
Dim intReferrerID As Integer
Dim strFooter As String
boolSuccess = True
strFooter = ""
Dim WordObj1 As Word.Application
Dim WordDoc As Word.Document
Set dbDatabase1 = CurrentDb()
Set qryDefPatient = dbDatabase1.QueryDefs("qry_LetterData_Patient")
qryDefPatient("Enter the ID number") = Form_frm_gene.ID
Set rstPatient = qryDefPatient.OpenRecordset()
'Other query defs also defined here but removed
DoCmd.Hourglass True
'Path to word document template
strPath = Form_frm_GS!LetterTemplatePath
Set WordObj1 = GetObject(, "word.application")
If Err.Number <> 0 Then
Set WordObj1 = CreateObject("word.application")
End If
WordObj1.Visible = True
WordObj1.Documents.Add Template:=strPath & strTemplate & ".dot",
NewTemplate:=False
'loop through bookmarks. Lookup the MergeField name, Query name and
switch on query name
With rstBookmarks
.MoveFirst
For i = 1 To .RecordCount
If intTemplate = !TemplateID Then
strWord = "String not found"
strBookmark = !BookmarkName
strFieldName = !MergeFieldName
If IsNull(!QueryName) Then
MsgBox ("Error: Null value for Query Name.")
Else
strQuery = !QueryName
Select Case strQuery
Case "qry_LetterData_Patient"
If strFieldName = "CCToPatient" Then
With rstPatient 'Patient's address for cc
list
strWord = !PatientTitle & " " &
!PatientFirstName & " " & !PatientSurname & ", "
strWord = strWord &
!PatientStreetAddress & " "
strWord = strWord & !PatientSuburb & " "
& !PatientState & " " & !PatientPostcode
End With
Else
strWord = rstPatient(strFieldName)
End if
'Other cases here but removed for brevity
Case Else
MsgBox ("Can't find the specified data source")
End Select
WordObj1.Selection.Goto what:=wdgotobookmark,
Name:=strBookmark
WordObj1.Selection.TypeText strWord
End If
End If
.MoveNext
Next i
'Add patient's surname to the word document footer in 6 point
text.
strFooter = rstPatient!PatientSurname
WordObj1.ActiveWindow.ActivePane.View.SeekView =
wdSeekCurrentPageFooter
WordObj1.Selection.EndKey unit:=wdLine
WordObj1.Selection.Font.Size = 6
WordObj1.Selection.TypeText Text:=strFooter
WordObj1.ActiveWindow.ActivePane.View.SeekView =
wdSeekMainDocument
End With
strSavedLetterPath = Form_frm_GS!SavedLettersPath
strDocumentName = Form_frm_PatientLetters!LetterName
intSaveResponse = MsgBox("Save this letter as " & strDocumentName & "?",
vbYesNo, "Save Document")
WordObj1.Activate
WordObj1.Selection.moveup wdLine, 40
If intSaveResponse = vbYes Then
'Set WordDoc = WordObj1.ActiveDocument
strSavedLetterPath = Form_frm_GS!SavedLettersPath
strDocumentName = Form_frm_PatientLetters!LetterName
'Save the word document
'Save record of this correspondence in the database for this patient
WordObj1.ActiveDocument.SaveAs strSavedLetterPath & strDocumentName
'WordDoc.SaveAs strSavedLetterPath & strDocumentName
DoCmd.OpenQuery ("qry_AddSavedLetter")
End If
' Set the Word Object to nothing to free resources
Set WordObj1 = Nothing
rstPatient.Close
If boolSuccess Then
MergeToWordTemplate = True
Else
MergeToWordTemplate = False
End If
DoCmd.Hourglass False
Exit Function
causes an illegal operation error when 97 users try to mail merge.
Users set can define a word document name and set of bookmarks in Access and
associate each bookmark with data from the database. Access then opens the
Word Document and inserts the data at each bookmark. I thought that
automating in this way would work for both 2000 and 97 users but I am getting
the illegal operation error and the application gets terminated in 97.
I have packaged the application and installed the Access Runtime on the 97
PCs. Can anyone tell me whether there is anything i mus include in the
package so that Access can merge for Office 97 users. Do I need a separate
runtime for Word?
The program code is included below, but shortened for ease of reading. Any
Advice would be greatly appreciated.
Thanks
Anna
Public Function MergeToWordTemplate(strTemplate As String, intTemplate As
Integer, rstBookmarks As Recordset) As Boolean
'This method creates a new document in MS Word using Automation and
inserts patient
'data at predefined bookmarks. Accepts a Word template name, ID and
recordset containing
'bookmark information. Returns False if an error occurred otherwise
returns True.
On Error GoTo HandleErr
On Error Resume Next
Dim dbDatabase1 As Database 'This database
Dim qryDefPatient As QueryDef 'Query definitions for each data source
query
Dim rstPatient As Recordset 'Recordset variables for each data
source query
Dim strBookmark As String 'Bookmark name
Dim strFieldName As String 'Field name in data source query
Dim strQuery As String 'Data source query name
Dim i As Integer 'For loop variable
Dim boolSuccess As Boolean
Dim strWord As String
Dim strPath As String
Dim strDocumentName As String
Dim strSavedLetterPath As String
Dim intSaveResponse As Integer
Dim intReferrerID As Integer
Dim strFooter As String
boolSuccess = True
strFooter = ""
Dim WordObj1 As Word.Application
Dim WordDoc As Word.Document
Set dbDatabase1 = CurrentDb()
Set qryDefPatient = dbDatabase1.QueryDefs("qry_LetterData_Patient")
qryDefPatient("Enter the ID number") = Form_frm_gene.ID
Set rstPatient = qryDefPatient.OpenRecordset()
'Other query defs also defined here but removed
DoCmd.Hourglass True
'Path to word document template
strPath = Form_frm_GS!LetterTemplatePath
Set WordObj1 = GetObject(, "word.application")
If Err.Number <> 0 Then
Set WordObj1 = CreateObject("word.application")
End If
WordObj1.Visible = True
WordObj1.Documents.Add Template:=strPath & strTemplate & ".dot",
NewTemplate:=False
'loop through bookmarks. Lookup the MergeField name, Query name and
switch on query name
With rstBookmarks
.MoveFirst
For i = 1 To .RecordCount
If intTemplate = !TemplateID Then
strWord = "String not found"
strBookmark = !BookmarkName
strFieldName = !MergeFieldName
If IsNull(!QueryName) Then
MsgBox ("Error: Null value for Query Name.")
Else
strQuery = !QueryName
Select Case strQuery
Case "qry_LetterData_Patient"
If strFieldName = "CCToPatient" Then
With rstPatient 'Patient's address for cc
list
strWord = !PatientTitle & " " &
!PatientFirstName & " " & !PatientSurname & ", "
strWord = strWord &
!PatientStreetAddress & " "
strWord = strWord & !PatientSuburb & " "
& !PatientState & " " & !PatientPostcode
End With
Else
strWord = rstPatient(strFieldName)
End if
'Other cases here but removed for brevity
Case Else
MsgBox ("Can't find the specified data source")
End Select
WordObj1.Selection.Goto what:=wdgotobookmark,
Name:=strBookmark
WordObj1.Selection.TypeText strWord
End If
End If
.MoveNext
Next i
'Add patient's surname to the word document footer in 6 point
text.
strFooter = rstPatient!PatientSurname
WordObj1.ActiveWindow.ActivePane.View.SeekView =
wdSeekCurrentPageFooter
WordObj1.Selection.EndKey unit:=wdLine
WordObj1.Selection.Font.Size = 6
WordObj1.Selection.TypeText Text:=strFooter
WordObj1.ActiveWindow.ActivePane.View.SeekView =
wdSeekMainDocument
End With
strSavedLetterPath = Form_frm_GS!SavedLettersPath
strDocumentName = Form_frm_PatientLetters!LetterName
intSaveResponse = MsgBox("Save this letter as " & strDocumentName & "?",
vbYesNo, "Save Document")
WordObj1.Activate
WordObj1.Selection.moveup wdLine, 40
If intSaveResponse = vbYes Then
'Set WordDoc = WordObj1.ActiveDocument
strSavedLetterPath = Form_frm_GS!SavedLettersPath
strDocumentName = Form_frm_PatientLetters!LetterName
'Save the word document
'Save record of this correspondence in the database for this patient
WordObj1.ActiveDocument.SaveAs strSavedLetterPath & strDocumentName
'WordDoc.SaveAs strSavedLetterPath & strDocumentName
DoCmd.OpenQuery ("qry_AddSavedLetter")
End If
' Set the Word Object to nothing to free resources
Set WordObj1 = Nothing
rstPatient.Close
If boolSuccess Then
MergeToWordTemplate = True
Else
MergeToWordTemplate = False
End If
DoCmd.Hourglass False
Exit Function