I have recorded a macro in Word to carry out a Mail Merge. I then have an Excel macro which initiates Word and Runs the Word macro.
I would prefer to run the whole thing from within Excel so that I can make the workbook file name within the merge dynamic but when I copy the code into my Excel macro I get error "Command Failed".
I can resolve this by linking the MS Word Object Library in Excel but I have been avoiding doing this as there are a number of different Word versions in use and that creates it's own problem! The error occurs at the line highlighted in red. Any suggestions to help please?
Private Sub CommandButton3_Click()
Dim wdapp As Object
Dim mypath As String
Dim myfile As String
mypath = ActiveWorkbook.Path
myfile = ActiveWorkbook.Name
Set wdapp = CreateObject("Word.Application")
wdapp.Visible = True
wdapp.Activate
wdapp.Documents.Add Template:="F:\PRF Docs\Custom Office Templates\Outcome Letter v1.dotm", NewTemplate _
:=False, DocumentType:=0
wdapp.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
wdapp.ActiveDocument.MailMerge.OpenDataSource Name:= _
"F:\PRF Docs\Grainstore\Zebedee COO123456.xlsm" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin; _
Data Source=""F:\PRF Docs\Grainstore\Zebedee COO123456.xlsm"";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OL" _
, SQLStatement:="SELECT * FROM `'Prog 01-08-15$'`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
'wdapp.Run MacroName:="outcomemerge"
End Sub
Bookmarks