Thank God, I figured this out. I have to declare the document as a main mail merge document every time, and I have to open the link to the data source every time. I thought both of these properties were "built in" since any time I physically open the document, it's ready to go with the linked data source fully functional.
Here was the final code:
Sub DowngradeMerge()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = New Word.Application
wdApp.ChangeFileOpenDirectory _
"X:\Departments\Benefits\DocCentral\Documents\All Locations\AM Best Rating Letters"
Set wdDoc = wdApp.Documents.Open( _
Filename:="Carrier Downgrade Letter.doc", _
ReadOnly:=False, AddToRecentFiles:=False)
wdApp.Visible = True
With wdDoc.MailMerge
.MainDocumentType = wdCatalog
.OpenDataSource Name:= _
"X:\Departments\Benefits\DocCentral\Documents\All Locations\AM Best Rating Letters\Carrier Rating Merge Letter Template.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=X:\Departments\Benefits\DocCentral\Documents\All Locations\AM Best Rating Letters\Carrier Rating Merge Letter Template.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:Sys" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.DataSource.FirstRecord = wdDefaultFirstRecord
.DataSource.LastRecord = wdDefaultLastRecord
.Execute Pause:=True
End With
Set wdApp = Nothing
Set wdDoc = Nothing
End Sub
Private Sub CommandButton1_Click()
DowngradeMerge
End Sub
"KC Rippstein" <kcrippsteinAThotmailDOTcom> wrote in message I changed to early binding and set the VBE to reference the Word object library, and I still get the same error in the exact same place.
Sub DowngradeMerge()
Dim wdApp As Word.Application
Dim wdDoc As Document
Set wdApp = New Word.Application
wdApp.ChangeFileOpenDirectory _
"X:\Departments\Benefits\DocCentral\Documents\All Locations\AM Best Rating Letters"
Set wdDoc = wdApp.Documents.Open( _
Filename:="Carrier Downgrade Letter.doc", ReadOnly:=True, _
AddtoRecentFiles:=False)
wdApp.Visible = True
With wdDoc.MailMerge
'***SAME ERROR, SAME LOCATION IN THE CODE******
.DataSource.FirstRecord = wdDefaultFirstRecord
.DataSource.LastRecord = wdDefaultLastRecord
.Destination = wdSendToEmail
.MailAsAttachment = True
.MailSubject = "Carrier Rating Information"
.SuppressBlankLines = True
.Execute Pause:=True
End With
Set wdApp = Nothing
Set wdDoc = Nothing
End Sub
Private Sub CommandButton1_Click()
DowngradeMerge
End Sub
"KC Rippstein" <kcrippsteinAThotmailDOTcom> wrote in message I cannot seem to get this macro to work. I have a mail merge spreadsheet that I am adding an ActiveX button to perform the mail merge. I thought it would be fine to use late binding, but is that not a viable option?
Sub DowngradeMerge()
Dim wdApp As Object, wdDoc As Object
Set wdApp = CreateObject("Word.Application")
wdApp.ChangeFileOpenDirectory _
"X:\Departments\Benefits\DocCentral\Documents\All Locations\AM Best Rating Letters"
Set wdDoc = wdApp.Documents.Open( _
Filename:="Carrier Downgrade Letter.doc", ReadOnly:=True, _
AddtoRecentFiles:=False)
wdApp.Visible = True
With wdDoc.MailMerge
'**THIS IS AS FAR AS IT GETS**Run-time error 5852 Requested object is not available
.DataSource.FirstRecord = wdDefaultFirstRecord
.DataSource.LastRecord = wdDefaultLastRecord
.Destination = 2 ' 2 = wdSendToEmail when late binding
.MailAsAttachment = True
.MailSubject = "Carrier Rating Information"
.SuppressBlankLines = True
.Execute Pause:=True
End With
End Sub
Private Sub CommandButton1_Click()
DowngradeMerge
End Sub
