469,612 Members | 1,529 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,612 developers. It's quick & easy.

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

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
3 4686
nico5038
3,080 Expert 2GB
Check out http://www.tek-tips.com/faqs.cfm?fid=3237

Nic;o)
Jan 17 '10 #2
brat33
36
Thank you for your reply. I appreciate it!
Feb 15 '10 #3
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.

Similar topics

9 posts views Thread by Marc Miller | last post: by
1 post views Thread by troy_s11 | last post: by
16 posts views Thread by Mich | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.