Albert Kallal's Access/Word Mail Merge in 2007

[Esther Lane]

Hello!

First off, many many thanks to Albert who wrote the Mail Merge code for MS Access I am using. It has been working beautifully for a few years. However, my client just (without notice!) upgraded from Access 2000 to Access 2007. Now that component is failing.

The merge is building the data source file fine (text file named merge.888).
I am providing an absolute path reference to the word file being used for the mail merge. Nothing has changed and this worked perfectly in the 2000 version.

Has anyone else run into this or have any ideas? The actual code being used is below:


----
Function RidesMergeWord(strDocName As String, _
strDataDir As String, _
Optional strOutDocName As String)

' This code takes a word document that has been setup as a MERGE document.
' This merge document is opened, then mailmerge is executed. The original
' document is then closed. The result is a raw word document with no connectons
' to the merge.txt (a csv source data file).

'Parms:
' strDocName - full path name of word doc (.doc)
' strDataDir - dir (full path) where docuemnts and the merge.888 file is placed
' strOutDocName - full path name of merged document (saved).
'
' The above parms are suppled by other routines. You likey should not need to call this
' routine directly. See the sub called MergeNoPrompts.

' Albert D. Kallal (c) 2001
' [e-mail]
'

Dim wordApp As Object ' running instance of word
Dim WordDoc As Object ' one instance of a word doc
Dim strActiveDoc As String ' doc name (no path)
Dim lngWordDest As Long ' const for dest, 0 = new doc, 1 = printer
Dim MyPbar As New clsRidesPBar ' create a instance of our Progress bar.


MyPbar.ShowProgress
MyPbar.TextMsg = "Launching Word...please wait..."
MyPbar.Pmax = 4 ' 4 steps to inc
MyPbar.IncOne ' step 1....start!

On Error GoTo CreateWordApp
Set wordApp = GetObject(, "Word.Application")
On Error Resume Next

MyPbar.IncOne ' step 2, word is loaded.

Set WordDoc = wordApp.Documents.Open(strDocName)

MyPbar.IncOne ' step 3, doc is loaded

strActiveDoc = wordApp.ActiveDocument.Name

WordDoc.MailMerge.OpenDataSource _
Name:=strDataDir & TextMerge, _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0
' , Connection:="", SQLStatement:="", SQLStatement1:=""


With WordDoc.MailMerge
.Destination = 0 ' 0 = new doc
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .datasource
.FirstRecord = 1
' .LastRecord = 1
End With
.Execute Pause:=True
End With
MyPbar.IncOne ' step 4, doc is merged
WordDoc.Close (False)

wordApp.Visible = True
wordApp.Windows(wordApp.Windows.Count).Activate
If strOutDocName <> "" Then
wordApp.ActiveDocument.SaveAs strOutDocName
End If

MyPbar.HideProgress

' AppActivate "Microsoft Word"
wordApp.Activate
wordApp.WindowState = 0 'wdWindowStateRestore

Set wordApp = Nothing
Set WordDoc = Nothing
Set MyPbar = Nothing

DoEvents

' If bolShowMerge = True Then
' WordApp.Dialogs(676).Show 'wdDialogMailMerge
' End If

Exit Function

CreateWordApp:
' this code is here to use the EXISTING copy of
' ms-access running. If getobject fails, then
' ms-word was NOT running. The below will then
' launch word
Set wordApp = CreateObject("Word.Application")
Resume Next

End Function
---


Thank You!
-Esther Lane

[FishVal]

Hello!

First off, many many thanks to Albert who wrote the Mail Merge code for MS Access I am using. It has been working beautifully for a few years. However, my client just (without notice!) upgraded from Access 2000 to Access 2007. Now that component is failing.

The merge is building the data source file fine (text file named merge.888).
I am providing an absolute path reference to the word file being used for the mail merge. Nothing has changed and this worked perfectly in the 2000 version.

Has anyone else run into this or have any ideas? The actual code being used is below:


----
Function RidesMergeWord(strDocName As String, _
strDataDir As String, _
Optional strOutDocName As String)

' This code takes a word document that has been setup as a MERGE document.
' This merge document is opened, then mailmerge is executed. The original
' document is then closed. The result is a raw word document with no connectons
' to the merge.txt (a csv source data file).

'Parms:
' strDocName - full path name of word doc (.doc)
' strDataDir - dir (full path) where docuemnts and the merge.888 file is placed
' strOutDocName - full path name of merged document (saved).
'
' The above parms are suppled by other routines. You likey should not need to call this
' routine directly. See the sub called MergeNoPrompts.

' Albert D. Kallal (c) 2001
' [e-mail]
'

Dim wordApp As Object ' running instance of word
Dim WordDoc As Object ' one instance of a word doc
Dim strActiveDoc As String ' doc name (no path)
Dim lngWordDest As Long ' const for dest, 0 = new doc, 1 = printer
Dim MyPbar As New clsRidesPBar ' create a instance of our Progress bar.


MyPbar.ShowProgress
MyPbar.TextMsg = "Launching Word...please wait..."
MyPbar.Pmax = 4 ' 4 steps to inc
MyPbar.IncOne ' step 1....start!

On Error GoTo CreateWordApp
Set wordApp = GetObject(, "Word.Application")
On Error Resume Next

MyPbar.IncOne ' step 2, word is loaded.

Set WordDoc = wordApp.Documents.Open(strDocName)

MyPbar.IncOne ' step 3, doc is loaded

strActiveDoc = wordApp.ActiveDocument.Name

WordDoc.MailMerge.OpenDataSource _
Name:=strDataDir & TextMerge, _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0
' , Connection:="", SQLStatement:="", SQLStatement1:=""


With WordDoc.MailMerge
.Destination = 0 ' 0 = new doc
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .datasource
.FirstRecord = 1
' .LastRecord = 1
End With
.Execute Pause:=True
End With
MyPbar.IncOne ' step 4, doc is merged
WordDoc.Close (False)

wordApp.Visible = True
wordApp.Windows(wordApp.Windows.Count).Activate
If strOutDocName <> "" Then
wordApp.ActiveDocument.SaveAs strOutDocName
End If

MyPbar.HideProgress

' AppActivate "Microsoft Word"
wordApp.Activate
wordApp.WindowState = 0 'wdWindowStateRestore

Set wordApp = Nothing
Set WordDoc = Nothing
Set MyPbar = Nothing

DoEvents

' If bolShowMerge = True Then
' WordApp.Dialogs(676).Show 'wdDialogMailMerge
' End If

Exit Function

CreateWordApp:
' this code is here to use the EXISTING copy of
' ms-access running. If getobject fails, then
' ms-word was NOT running. The below will then
' launch word
Set wordApp = CreateObject("Word.Application")
Resume Next

End Function
---


Thank You!
-Esther Lane

Hi!
There are too many external reasons for code to fail. The best way to solve the problem is to debug the code. I'm not sure whether you are experienced in code debugging so the following instructions are for complete newbie.

• if code fails silently w/o error message popup then comment the line "On Error Resume Next" (put ' mark to the start of the line)
• make the code to run as it supposed to be run
• when a dialog with error message popups press <Debug> button, this will open VBA window where faulty code row will be highlighted
• the highlighted code row may be not erroneous itself but calls procedure where program flow fails, so press <F8> until you get to the code row which raise the error

Having done this post where does the code actualy fail (the code row highlighted when you press <Debug> and the code row which you can't pass by pressing <F8> if not the same) and what is the error message.

Good luck.

P.S. I'm pretty sure the code fails after Word.Application is created and before it becomes visible, so you'll need to manually close Word process from Win TaskManager.

P.P.S. If you are not experienced in VBA make backup copy first.