Can MS Access 2000 execute MS Word 2000 mail merge?

[rlh145]

Here is my predicament: I have an Access Application that I support that was written by another programmer originally in MS Access 97. It has since been converted. There is logic in this application that will execute a query to define specific data and the program is supposed to execute MS Word to perform a mail merge with that data and a pre-defined letter.
I am wondering if MS Access 2000 can still perform this function?
I have gone back with one of the letters and re-set the letter to mail-merge with the results of this particular query in the same Access application. It will still not fire up MS Word 2000.
I have gone back and made sure that DDE is still useful in MS Access 2000. I believe that component is still supported in 2000.

Can someone help me on this matter? I will be happy to post code in a follow-up post if that will help.

Thanks!
Ralph

 

[Remou]

Yes, you can perform a mailmerge in Access 2000 to Word 2000, that is what I have. I guess there must be some small thing in your code, so you can either post your code, or look at some of the FAQs for examples, if you prefer.

 

[rlh145]

I am including the code. I apologize off the bat for the size. The first procedure called from this module is "GETWORDMERGELETTERS". To just recant, this was originally written in Access 97 and has not been touched since converting to Access 2000.

Here goes:



Option Compare Database
Option Explicit

Private myiNumber As Integer 'Used for keeping track of Fiscal Years
Private mycountReport As Integer 'Used for counting reports
Private mycountLetter As Integer 'Used for counting letters/lables

Dim wspJet As Workspace
Dim dbsRecipient As Database
Dim rstRecipient As Recordset
Dim AutoNo
Dim mstAppTitle As String


Dim AutoStr As Integer

Dim FormPath As String
Dim SavePath As String
Dim MyWord As Object, MyDoc As Object
Dim iRetValue As Integer
'(-----------------This declaration is not used------------------)
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long


Function MenuRunLetter(intConstant As Integer)

'(-----------------This function is not used-------------------)

On Error GoTo CommandErrorTrap:

Select Case intConstant
Case 1
countLetter = 1
Case 2
countLetter = 2
Case 3
countLetter = 3
Case 4
countLetter = 4
Case 5
countLetter = 5
Case 6
countLetter = 6
Case 7
countLetter = 7
Case 8
countLetter = 8
Case 9
countLetter = 9
Case 10
countLetter = 10
Case 11
countLetter = 11
Case 12
countLetter = 12
End Select
DoCmd.OpenForm "frmFiscalYearLetter"
Exit Function

CommandErrorTrap:
MsgBox Err.Description, vbCritical, "Error in MenuRunCommand"
Exit Function

End Function
Function fSetAccessCaption() As Boolean
Dim dbs As Database
Const cPropNotExit = 3270

'retrieve old title
Set dbs = CurrentDb
On Error Resume Next
mstAppTitle = dbs.Properties("AppTitle")

'if property doesn't exist
If Err = cPropNotExit Then
fSetAccessCaption = False
Else
dbs.Properties("AppTitle") = "Microsoft Access"
RefreshTitleBar
fSetAccessCaption = True
End If
End Function
Sub sRestoreTitle()
CurrentDb.Properties("AppTitle") = mstAppTitle
RefreshTitleBar
End Sub


Public Property Get iNumber() As Variant
iNumber = myiNumber
End Property

Public Property Let iNumber(ByVal vNewValue As Variant)
myiNumber = vNewValue
End Property

Sub GetWordMergeLetters()

On Error Resume Next ' Defer error trapping.

iRetValue = MsgBox("Do You want to create them?.", _
vbQuestion + vbYesNo, "Letters will be created now.")
If iRetValue = vbNo Then
Exit Sub
End If

' Remove the Application Title
Call fSetAccessCaption

'The GetEnvironment subroutine will get the directories FormPath and SavePath
GetEnvironment

AutoNo = Int((150 * Rnd) + 1)


Select Case countLetter
Case 1
Set MyWord = GetObject(FormPath + "mergeLetter1.doc")
'Set MyWord = GetObject(FormPath + "mrgflrap.doc")
Case 2
Set MyWord = GetObject(FormPath + "mergeLetter2.doc")
'Set MyWord = GetObject(FormPath + "slrap.doc")
Case 3, 8, 12
Set MyWord = GetObject(FormPath + "mergeLetter3.doc")
'Set MyWord = GetObject(FormPath + "arc.doc")
Case 4
Set MyWord = GetObject(FormPath + "mergeLetter4.doc")
'Set MyWord = GetObject(FormPath + "wf-nap.doc")
Case 5
Set MyWord = GetObject(FormPath + "mergeLetter5.doc")
'Set MyWord = GetObject(FormPath + "flrcapr.doc")
Case 6
Set MyWord = GetObject(FormPath + "mergeLetter6.doc")
'Set MyWord = GetObject(FormPath + "slrcapr.doc")
Case 7
Set MyWord = GetObject(FormPath + "mergeLetter7.doc")
'Set MyWord = GetObject(FormPath + "rlnocap.doc")
Case 9
Set MyWord = GetObject(FormPath + "mergeletter9.doc")
'Set MyWord = GetObject(FormPath + "fl-cr.doc")
Case 10
Set MyWord = GetObject(FormPath + "mergeLetter10.doc")
'Set MyWord = GetObject(FormPath + "sl-cr.doc")
Case 11
Set MyWord = GetObject(FormPath + "mergeletter11.doc")
'Set MyWord = GetObject(FormPath + "rl-nc.doc")
End Select

MyWord.mailmerge.Execute

Set MyDoc = GetObject(MyWord.Application.ActiveWindow)

Select Case countLetter
Case 1

MyDoc.saveAs SavePath + AutoNo + "flrap.doc"
'MyDoc.saveAs SavePath + "Letter1.doc"
'MyDoc.PrintOut, , , , 2
'MyWord.saveAs FormPath + AutoNo + "mergeflrap.doc"
MyWord.saveAs FormPath + "mergeLetter1.doc"

Case 2
MyDoc.saveAs SavePath + AutoNo + "Letter2.doc"
'MyDoc.PrintOut , , , , 2
MyWord.saveAs FormPath + "mergeLetter2.doc"
Case 3, 8, 12
MyDoc.saveAs SavePath + AutoNo + "Let3.doc"
'MyDoc.PrintOut , , , , 2
MyWord.saveAs FormPath + "mergeLetter3.doc"
Case 4
MyDoc.saveAs SavePath + AutoNo + "Letter4.doc"
'MyDoc.PrintOut , , , , 2
MyWord.saveAs FormPath + "mergeLetter4.doc"
Case 5
MyDoc.saveAs SavePath + AutoNo + "Let5.doc"
'MyDoc.PrintOut , , , , 2
MyWord.saveAs FormPath + "mergeLetter5.doc"
Case 6
MyDoc.saveAs SavePath + AutoNo + "Let6.doc"
'MyDoc.PrintOut , , , , 2
MyWord.saveAs FormPath + "mergeLetter6.doc"
Case 7
MyDoc.saveAs SavePath + AutoNo + "Let7.doc"
'MyDoc.PrintOut , , , , 2
MyWord.saveAs FormPath + "mergeLetter7.doc"
Case 9
MyDoc.saveAs SavePath + AutoNo + "Let9.doc"
'MyDoc.PrintOut , , , , 2
MyWord.saveAs FormPath + "mergeLetter9.doc"
Case 10
MyDoc.saveAs SavePath + AutoNo + "Let10.doc"
'MyDoc.PrintOut , , , , 2
MyWord.saveAs FormPath + "mergeLetter10.doc"
Case 11
MyDoc.saveAs SavePath + AutoNo + "Let11.doc"
'MyDoc.PrintOut , , , , 2
MyWord.saveAs FormPath + "mergeLetter11.doc"
End Select

MyWord.Application.Quit
Call sRestoreTitle
Set MyWord = Nothing

End Sub
Function MenuRunReport(intConstant As Integer)

'(-----------------This function is not used-------------------)

On Error GoTo CommandErrorTrap:

Select Case intConstant
Case 1
countReport = 1
Case 2
countReport = 2
Case 3
countReport = 3
Case 4
countReport = 4
Case 5
countReport = 5
Case 6
countReport = 6
Case 7
countReport = 7
Case 8
countReport = 8
Case 9
countReport = 9
Case 10
countReport = 10
Case 11
countReport = 11
End Select
DoCmd.OpenForm "frmFiscalYearReport"
Exit Function

CommandErrorTrap:
MsgBox Err.Description, vbCritical, "Error in MenuRunCommand"
Exit Function

End Function

Public Property Get countReport() As Variant
countReport = mycountReport
End Property

Public Property Let countReport(ByVal vNewValue As Variant)
mycountReport = vNewValue
End Property

Public Property Get countLetter() As Variant
countLetter = mycountLetter
End Property

Public Property Let countLetter(ByVal vNewValue As Variant)
mycountLetter = vNewValue
End Property

Sub GetWordMergeLabelsAll()

On Error Resume Next ' Defer error trapping.

iRetValue = MsgBox("Do You want to create them?.", vbQuestion + vbYesNo, "Labels will be created now.")
If iRetValue = vbNo Then
Exit Sub
End If

AutoNo = Int((150 * Rnd) + 1)

' Remove the Application Title
Call fSetAccessCaption

'The GetEnvironment subroutine will get the directories FormPath and SavePath
GetEnvironment

Set MyWord = GetObject(FormPath + "mergeLabelAll.doc")

MyWord.mailmerge.Execute

Set MyDoc = GetObject(MyWord.Application.ActiveWindow)

MyDoc.saveAs SavePath + AutoNo + "LabelAll.doc"
'MyDoc.PrintOut
MyWord.saveAs FormPath + "mergeLabelAll.doc"

MyWord.Application.Quit
' Remove the Application Title
Call sRestoreTitle
Set MyWord = Nothing
End Sub

Private Sub GetEnvironment()
'Returns reference to default workspace.
Set wspJet = DBEngine.Workspaces(0)
'Returns reference to current database.
Set dbsRecipient = CurrentDb()

Set rstRecipient = dbsRecipient.OpenRecordset("tblEnvironment", dbOpenDynaset)
rstRecipient.MoveFirst

FormPath = rstRecipient!FormLocation
SavePath = rstRecipient!Savelocation

'FormPath = "\\NRDCS01\VOL1\DATA\DFM\SHARED\subrecpt\"
'SavePath = "\\NRDCS01\VOL1\DATA\DFM\SHARED\subrecpt\Letters\"
End Sub

 

[Remou]

Whoof! I notice that error trapping has been deferred, have you checked that FormPath and SavePath are being populated? I do not think they are, and I think that it is because rstRecipient and dbsRecipient need to be DAO:

Dim dbsRecipient As DAO.Database
Dim rstRecipient As DAO.Recordset

 

[RoyVidar]

Remou's suggestion might be what is causing something, but I'm a bit at loss with regards to what is actually happening. Are there any errormessages, where does it bomb, what does actually happen ...

If I recall correct, when moving from a97 to later versions, I think mailmerge became a bit more reluctant with regards to which queries it it will recognize. I think only very simple selects will do, when using VBA functions ... I'm not sure you'll get it to work.

If that's one of the challenges, then have a look at pseale's faq faq181-5088, which circumvents much of what is usually providing hassle when doing mailmerge.

Roy-Vidar

 

[rlh145]

RoyVidar,

Under normal circumstances, MS Word will automatically activate. In this case, nothing appears on the screen when I execute the command to print letters.

I will look at the faq you have referred me to.

Thanks!
Ralph

 

[Remou]

I have tried your code and it seems to work fine except for some small errors that show up when
On Error Resume Next
is commented out everywhere it occurs. I do not think you can debug this code when errors are not shown.

 

[rlh145]

Gentlemen,

I have found part of the problem. I came to find out that the following lines:

FormPath = rstRecipient!FormLocation
SavePath = rstRecipient!Savelocation

pointed to elments of a table that had not been updated in several years thereby pointing to a fileserver that did not exist anymore.

So now when I attempt to run it (after making the corrections) I get a message at the lower left hand screen that says: "recordset not updateable".

Does that mean that I have rights issues? or something else? The recordset is defined at the top:

Dim rstRecipient As Recordset

I would appreciate any thoughts on this!!

Ralph