Mail merge (labels) to word document directly from Access

kevink · Apr 1, 2000

I'm trying to create a mail merge document in Word directly from Access that prints out labels for each county.  The user selects the county from a combo box and then hits a command button which executes the following code: strSQL = "SELECT * FROM NAMES WHERE COUNTY = & " '" & strCounty &"'" Set objWord = CreateObject("Word.Application&quot

Set objWord = GetObject("d:\test\Labels for Printing.doc&quot

objWord.Application.Visible = True objWord.MailMerge.DataSource.QueryString = strSQL     With objWord.MailMerge         .Destination = wdSendToNewDocument         .MailAsAttachment = False         .MailAddressFieldName = ""         .MailSubject = ""         .SuppressBlankLines = True         With .DataSource             .FirstRecord = wdDefaultFirstRecord             .LastRecord = wdDefaultLastRecord         End With         .Execute Pause:=True     End With Needless to say, it doesn;t work!!  It opens 7 (yip, 7!) instances of Access and the word document, but then does nothing else.  All the vba books I have say how to insert text into a word document, but not how to create labels based on user choice! Any ideas? Thanks Kevin

JimHorton · Apr 1, 2000

kevin, I'm not sure on this, but don't you want to use: objWord.open("d:\test\Labels for Printing.doc&quot

...instead of Getobject?...then set a Document object to this doc. I'm not real up on Word OLE, but let me know if this does it... --Jim

DougP · Apr 1, 2000

I use a query for the data source in Word instead. 1st Create query in Access that returns the results. Sort them by Zip or whatever I have had Excellent results with this Method. Forget all of the CODE above. We had a form which allowed us to pick a specific record or groups of records. A Query was created which looked at a Unique field on the form ([Forms]![Launch MSWord]![Combo1]) so when you narrow down the results on the form the query had the exact same results. Then a button was clicked which launched Word opened a specific document whose Datasource was the same query above. Ran a macro in Word that merged and printed and quit. We had LOTS of these and they ran some very complicated Word Docs (7-10 Page Proposals) with upto 75 fields of data from Access. In Word create a new Blank doc for testing Click Mail merge, Create Active Window, GetData Source, OPen Datasource, At the bottom in "Files of Type" click Microsoft Access Database. In the Browse Window Find your database. click the "Queries" TAB and find your query. Click the "Insert Merge Field" button and add your buttons. Click the <<ABC>> to see your results Save your doc Copy down the the whole drive and path where you saved it (you'll need in in a minute) and Close Word In Access make a form Add a command button and put this code in it Do NOT use any Wizard to connect to Word. -------------------------- Private Sub Command0_Click() On Error GoTo Err_Command0_Click     Dim retval As Variant     Dim DocName, MacroName, BuildWordInfo As String     ' The document name can be gotten from anywhere including Combobox, ListBox, Subform     DocName = "F:\AccessMailMerge.doc"          'Optional Macro Name     'MacroName = "/M" & "YourMacro"   ' <<< Macro name cannot have any spaces in it.          ' Launch Word     BuildWordInfo = "C:\Microsoft Office97\Office\WinWord.exe " & DocName & MacroName     retval = Shell(BuildWordInfo, 1)      Exit_Command0_Click:     Exit Sub Err_Command0_Click:     MsgBox Err.Description     Resume Exit_Command0_Click      End Sub ----------------------------- Ok I just tested it

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Mail merge (labels) to word document directly from Access

kevink

Programmer

JimHorton

Programmer

DougP

MIS

Similar threads

Part and Inventory Search

Sponsor