Excel 2007 Open Word mail merge with button in Excel (VBA Code)

[jtemp57]

I can not seem to figure this one out. I want to open Mail merge from my excel spreadsheet with a button. I need word to open with my mail merge template and pull data from the spreadsheet that I have open with the button on it. No one has answered me on any forum yet. Is this impossible to do?
I get this code when I run the macro: "Run time Error 424; Object needed" It will open my word document but will not finish out my mail merge. Any suggestions?

Sub Print_Tags()
'
' Macro1 Macro
'
'
Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "C:\Users\J.Templet\Desktop\LOTO Print Tags\PRINTING TAGS.dotx"
wordapp.Visible = True
docwd.MailMerge.OpenDataSource Name:= _
"C:\Users\J.Templet\Desktop\LOTO Print Tags\Isolation LOTO Template.xlsx" _
, 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=C:\Users\J.Templet\Desktop\LOTO Print Tags\Isolation LOTO Template.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Ty" _
, SQLStatement:="SELECT * FROM `Tag_List`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:="Tag_"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Equip_No"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:="Name"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Date_of_Isolation"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Lock__Tag_No"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Isolation_Type"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Isolation_Location"
WordBasic.MailMergePropagateLabel
With docwd.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub


This is the part that is highlighted yellow when I open the debuger:

docwd.MailMerge.OpenDataSource Name:= _
"C:\Users\J.Templet\Desktop\LOTO Print Tags\Isolation LOTO Template.xlsx" _
, 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=C:\Users\J.Templet\Desktop\LOTO Print Tags\Isolation LOTO Template.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Ty" _
, SQLStatement:="SELECT * FROM `Tag_List`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess

 

[Andrew Poulsom]

Try replacing:

wordapp.documents.Open "C:\Users\J.Templet\Desktop\LOTO Print Tags\PRINTING TAGS.dotx"

with:

Set docwd = wordapp.documents.Open("C:\Users\J.Templet\Desktop\LOTO Print Tags\PRINTING TAGS.dotx")

 

[jtemp57]

I tried replacing it but it still highlights the same are in yellow? It opens the right file, but it doesn't finish running the Macro. It still gives the "Run Time Error 424".

Sub Print_Tags()
'
' Macro1 Macro
'
'
Set wordapp = CreateObject("word.Application")
Set docwd = wordapp.documents.Open("C:\Users\J.Templet\Desktop\LOTO Print Tags\PRINTING TAGS.dotx")
wordapp.Visible = True
docwd.MailMerge.OpenDataSource Name:= _
"C:\Users\J.Templet\Desktop\LOTO Print Tags\Isolation LOTO Template.xlsx" _
, 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=C:\Users\J.Templet\Desktop\LOTO Print Tags\Isolation LOTO Template.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Ty" _
, SQLStatement:="SELECT * FROM `Tag_List`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:="Tag_"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Equip_No"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:="Name"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Date_of_Isolation"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Lock__Tag_No"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Isolation_Type"
docwd.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Isolation_Location"
WordBasic.MailMergePropagateLabel
With docwd.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub

 

[Andrew Poulsom]

I'm not sure why you are getting an object required error, but if you are using late binding you can't use Word constants like wdOpenFormatAuto and wdMergeSubTypeAccess. You have to use the values of those constants which are 0 and 1 respectively. You can find a list of the constants' values here:

Word Enumerated Constants