Access to Word Mailmerge not working in Access 2003

[frank]

The following code borrowed from 'The Access Web' works fine with
Access 2000 but I can't get it to work with Access 2003. Any ideas?
I'd also like to save the newly merged document using VBA. Any help
with the syntax is appreciated.

'************* Module Start *************
'
Option Compare Database
Option Explicit
Dim mstAppTitle As String

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

Function fMailMerge()
Dim objWord As Word.Document
Dim stMergeDoc As String

If fSetAccessCaption Then
On Error Resume Next
stMergeDoc = "mailmerge.doc"

Set objWord = GetObject(stMergeDoc, "Word.Document")

objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE Customer", _
SQLStatement:="Select * from [Customer]"
objWord.MailMerge.Execute
objWord.Close

'restore the caption
Call sRestoreTitle
End If
End Function
'************* Module End *************

 

[Tom Wickerath]

Hi Frank,

When you state that you "can't get it to work with Access 2003", are you
getting any error messages? If so, what is the error message? Have you set a
reference to the "Microsoft DAO 3.6 Object Library"? Have you checked for
any references marked as "MISSING"?

Solving Problems with Library References (Allen Browne)

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



Access Reference Problems (Doug Steele)

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html


Does the code compile OK on your machine (Debug > Compile ProjectName)? If
not, where does the compile error occur?

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

The following code borrowed from 'The Access Web' works fine with
Access 2000 but I can't get it to work with Access 2003. Any ideas?
I'd also like to save the newly merged document using VBA. Any help
with the syntax is appreciated.

'************* Module Start *************
'
Option Compare Database
Option Explicit
Dim mstAppTitle As String

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

Function fMailMerge()
Dim objWord As Word.Document
Dim stMergeDoc As String

If fSetAccessCaption Then
On Error Resume Next
stMergeDoc = "mailmerge.doc"

Set objWord = GetObject(stMergeDoc, "Word.Document")

objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE Customer", _
SQLStatement:="Select * from [Customer]"
objWord.MailMerge.Execute
objWord.Close

'restore the caption
Call sRestoreTitle
End If
End Function
'************* Module End *************

 

[Frank]

Tom,
I've stepped through it in debug with no error messages. It hits all
the code with no errors thrown. It has no problem compiling. As I
mentioned it executes properly in Office Access 2000. There are no
MISSING references and "Microsoft DAO 3.6 Object Library" is set. As I
step through the code that usually causes a good deal of processing and
delays such as:
objWord.MailMerge.Execute
it executes very quickly, probably because it's not really performing
the action, but it hits the next line so I'm sure it's not throwing an
exception.
Frank

 

[Tom Wickerath]

Hi Frank,

Try removing the On Error Resume Next statements from both functions:
fSetAccessCaption and fMailMerge, place a break point in fMailMerge, and then
try stepping through the code again. I suspect you have a run-time error of
some type that is being ignored with the On Error Resume Next statement.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom,
I've stepped through it in debug with no error messages. It hits all
the code with no errors thrown. It has no problem compiling. As I
mentioned it executes properly in Office Access 2000. There are no
MISSING references and "Microsoft DAO 3.6 Object Library" is set. As I
step through the code that usually causes a good deal of processing and
delays such as:
objWord.MailMerge.Execute
it executes very quickly, probably because it's not really performing
the action, but it hits the next line so I'm sure it's not throwing an
exception.
Frank

 

[Frank Root]

Tom,
Thanks for suggestion. After turning off the 'On Error Resume
Next' I was getting a 'ClassFactory cannot supply requested class'. I
did a little research and found that my Norton AntiVirus 2004 was
probably causing the problem, so I uninstalled it and Office 2003. I
upgraded to NAV 2005 and reinstalled Office 2003. It's now running
properly, but now I need to determine how to avoid a couple of dialogs
popping up i.e. 'Data Link Properties' and 'Confirm Data Source'. I
was able to disable another dialog for the 'SQLSecurityCheck'. I'm
trying to automate the processeing of the mail merge so that the user
doesn't have to interact with the dialogs for each document. All this
security has made 'seamless' harder to achieve. I think capital
punishment for virus authoring may be justified. Do you happen to
know how to save the newly merged document using VBA?
Thanks for your help
Frank

 

[Tom Wickerath]

Hi Frank,

Glad to hear that you've made some progress on this issue.

...but now I need to determine how to avoid a couple of dialogs
popping up i.e. 'Data Link Properties' and 'Confirm Data Source'.

I noticed those same dialogs when I was testing this code. I don't know how
to disable these.

I think capital punishment for virus authoring may be justified.

Let's add spammers to that list too. Especially those who harvest e-mail
addresses from newsgroup posts!

Do you happen to know how to save the newly merged document using VBA?

Try adding this to fMailMerge:

Dim strPath As String
:
:
:
'Get the user's default directory and save the document in this folder
strPath = objWord.Application.Options.DefaultFilePath _
(wdDocumentsPath) & "\Merge.doc"
objWord.Application.ActiveDocument.SaveAs _
FileName:=strPath, AddToRecentFiles:=False


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom,
Thanks for suggestion. After turning off the 'On Error Resume
Next' I was getting a 'ClassFactory cannot supply requested class'. I
did a little research and found that my Norton AntiVirus 2004 was
probably causing the problem, so I uninstalled it and Office 2003. I
upgraded to NAV 2005 and reinstalled Office 2003. It's now running
properly, but now I need to determine how to avoid a couple of dialogs
popping up i.e. 'Data Link Properties' and 'Confirm Data Source'. I
was able to disable another dialog for the 'SQLSecurityCheck'. I'm
trying to automate the processeing of the mail merge so that the user
doesn't have to interact with the dialogs for each document. All this
security has made 'seamless' harder to achieve. I think capital
punishment for virus authoring may be justified. Do you happen to
know how to save the newly merged document using VBA?
Thanks for your help
Frank