By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

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

P: 36
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:
Expand|Select|Wrap|Line Numbers
  1. 'Start MS Word
  2.  Dim objWord As Word.Application
  3.  Set objWord = CreateObject("Word.Application")
  4.  
  5. 'Make Application visible
  6.  With objWord
  7.  .Visible = True
  8.  
  9.  ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
  10.  ActiveDocument.MailMerge.OpenDataSource Name:= _
  11.     "E:\Trip\Trip Database.mdb", ConfirmConversions:=True, ReadOnly:= _
  12.      False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
  13.      PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:= _
  14.      "", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
  15.      "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" _
  16.      , SQLStatement:="Select * From 'Address Export'", SubType:=wdMergeSubTypeAccess
  17.  
  18.  'Move each field to mail merge document in Word
  19.  ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="First_Name"
  20.  Selection.TypeText Text:=" "
  21.  ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Last_Name"
  22.  Selection.TypeText Text:=" "
  23.  ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Title"
  24.  Selection.TypeText Text:=" "
  25.  Selection.TypeParagraph
  26.  ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Address"
  27.  Selection.TypeText Text:=" "
  28.  Selection.TypeParagraph
  29.  ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="City"
  30.  Selection.TypeText Text:=" "
  31.  ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="State"
  32.  Selection.TypeText Text:=" "
  33.  ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Zip"
  34.  ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
  35.  
  36.  End With
  37.  
  38. 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. -
Jan 15 '10 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072

P: 36
Thank you for your reply. I appreciate it!
Feb 15 '10 #3

P: 99
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.
Feb 21 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.