How to create Merged Label file in Word using Access (2007)

[brat33]

I am trying to use a command button to automate a process for creating mailing labels from a query in Access 2007, going into word 2007. Current code looks like the following:

'Start MS Word
 Dim objWord As Word.Application
 Set objWord = CreateObject("Word.Application")
 
'Make Application visible
 With objWord
 .Visible = True

 ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
 ActiveDocument.MailMerge.OpenDataSource Name:= _
    "E:\Trip\Trip Database.mdb", ConfirmConversions:=True, ReadOnly:= _
     False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
     PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:= _
     "", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
     "Provider=Microsoft.ACE.OLEDB.12.0;User ID=User;Data Source=E:\Trip\ Trip Database.mdb;Mode=Read;Extended Properties="""";Jet PLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global P" _
     , SQLStatement:="Select * From 'Address Export'", SubType:=wdMergeSubTypeAccess

 'Move each field to mail merge document in Word
 ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="First_Name"
 Selection.TypeText Text:=" "
 ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Last_Name"
 Selection.TypeText Text:=" "
 ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Title"
 Selection.TypeText Text:=" "
 Selection.TypeParagraph
 ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Address"
 Selection.TypeText Text:=" "
 Selection.TypeParagraph
 ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="City"
 Selection.TypeText Text:=" "
 ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="State"
 Selection.TypeText Text:=" "
 ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Zip"
 ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
 
 End With
 
End Sub

I receive an error message that the data source can not be verified. I can manually do the mail merger by going into word, but I want to simplfy the process for the end users. If anyone has any assistance, or could offer any suggestions I would be greatfull. -

[nico5038]

Check out http://www.tek-tips.com/faqs.cfm?fid=3237

Nic;o)

[brat33]

Thank you for your reply. I appreciate it!

[julietbrown]

Hello, Brat!!
If it's ONLY labels you want, i.e. not a full mail merge of letters, you can do labels very easily inside Access 2007 itself. Look for 'labels' on the Reports menu.