Click a Button to Run Word Mail Merge...

[nchesebro]

I have an access database for storing tour reservation data. Also, I have three letters set up as a mail merge in word. I would like to have one button for each letter that will print that word document corresponding to record being viewed on the form.

I have done some research on this, and it looks as if this cannot be done with VBA script behind each button. Looks like I need to create a module. I have never done this procedure before, if anyone has experience with this or can point to a code example that you think would work best, I would appreciate it.

ALSO... I will be splitting the database sometime in the near future. Would this be easier to do before or after the splitting procedure, or does it not matter one way or the other?

[ajetrumpet]

a module vs. a short vba script behind the button does not matter. it is code. it doesn't matter where it goes, as long as it's executable. Here's a sample script that I wrote a long time ago that runs a merge document:

Function MailMerge(mDoc As String, _
                   strSql As String)

On Error GoTo Err_Handle

'******************************************************************************
'_____________________________________________________________________________*
'                                                                             |
'THIS FUNCTION USES THE CURRENT DATABASE AS THE MAIL MERGE SOURCE.  IT ALSO   |
'USES LETTERS AS THE MERGE FORMAT.  THERE IS NO ARGUMENT TO CHANGE THIS.      |
'_____________________________________________________________________________|
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 5/9/2005                                                               *
'Purpose:  To run an automated Mail Merge with a MS Word template.            *
'                                                                             *
'Arguments:                                                                   *
'mDoc > Full path of the Merge document.                                      *
'strSQL> The query to run the Merge with.                                     *
'                                                                             *
'******************************************************************************


Dim oApp As New Word.Application
Dim oMainDoc As Word.Document
Dim sData As String

   oApp.Visible = True
      sData = CurrentProject.Path & "\" & CurrentProject.Name

   Set oMainDoc = oApp.Documents.Open(mDoc)
   
      With oMainDoc.MailMerge
          .MainDocumentType = wdFormLetters
          .OpenDataSource Name:=sData, _
                          SQLStatement:=strSql
      End With
   
   With oMainDoc
       .MailMerge.Destination = wdSendToNewDocument
       .MailMerge.Execute
   End With
   
   oApp.Activate
   oApp.Documents.Parent.Visible = True
   oApp.Application.WindowState = 1
   oApp.ActiveWindow.WindowState = 1

Set oApp = Nothing
Set oMainDoc = Nothing

Exit Function

Err_Handle:
   Set oApp = Nothing
   Set oMainDoc = Nothing
      MsgBox "An error occurred..." & vbCrLf & vbCrLf & err.Description

End Function '//LL

[nchesebro]

where did you put this code? behind a button or in a module?

i realize you said it doesn't matter, but which do you find most preferable?

[nchesebro]

This is what I have now behind the button, and it does not work...

Private Sub btnMergeTourLetterMF_Click()

Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Dim sDBPath As String

Set oMainDoc = oApp.Documents.Open("c:\Users\Nathan Chesebro\My Documents\Lake Superior State University\Admissions\Database Project\MTWRF Confirmation Letter.doc")
oApp.Visible = True

With oMainDoc.MailMerge

.MainDocumentType = wdFormLetters

sDBPath = "C:\Users\Nathan Chesebro\My Documents\Lake Superior State University\Admissions\Database Project\Tours v.1.0.2.mdb"
.OpenDataSource Name:=sDBPath, _
SQLStatement:="SELECT * FROM [tblTour]"

End With

With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute
End With

oApp.Activate
oApp.Documents.Parent.Visible = True
oApp.Application.WindowState = 1
oApp.ActiveWindow.WindowState = 1

End Sub

[nchesebro]

Throwing:

Compile Error:

User-define type not defined

[OK] [Help]

[nchesebro]

I am still not having luck with this...

Monkeyed around with different things with the code, and cannot get it to work. I went back to the code I posted above, as it has the least amount of problems.

Any Ideas out there?

[nchesebro]

Found this website: http://www.tek-tips.com/faqs.cfm?fid=2379

Has this code:

Public Function CreateWordLetter(strDocPath As String)

   'function returns nothing, but I created this as a 
   'function so all those macro users out there could
   'use it also. :P
   'if no path is passed to function, exit - no further
   'need to do anything
   
   If IsNull(strDocPath) Or strDocPath = "" Then
      Exit Function
   End If

   Dim dbs As Database
   Dim objWord As Object
   Dim PrintResponse
   Set dbs = CurrentDb
   
   'create reference to Word Object
   
   Set objWord = CreateObject("Word.Application")  
   
   'Word Object is created - now let's fill it with data.
   
   With objWord
       .Visible = True
       .Documents.Open(strDocPath)
       'move to each bookmark, and insert correct text.
       .ActiveDocument.Bookmarks("<bookmark name>".Select
       .Selection.Text=(Cstr(Forms!<form name>!<field 
        name>))
       .ActiveDocument.Bookmarks.Add Name:=<bookmark name>, 
       Range = Selection.Range

** continue the ActiveDocument and Selection statements for each bookmark that you have on the Word Document **
      
   End With

   'find out if the user would like to print the document
   'at this time.

   PrintResponse = MsgBox("Print this document?", vbyesno)
   If PrintResponse = vbYes Then
      objWord.ActiveDocument.PrintOut Background:=False
   End If

   'release all objects
         
   Set objWord = nothing
   Set dbs = nothing

End Function

So I modified it to look like this:

Public Function CreateWordLetter(strDocPath As String)

   'function returns nothing, but I created this as a
   'function so all those macro users out there could
   'use it also. :P
   'if no path is passed to function, exit - no further
   'need to do anything
   
   If IsNull(strDocPath) Or strDocPath = "" Then
      Exit Function
   End If

   Dim dbs As Database
   Dim objWord As Object
   Dim PrintResponse
   Set dbs = CurrentDb
   
   'create reference to Word Object
   
   Set objWord = CreateObject("Word.Application")
   
   'Word Object is created - now let's fill it with data.
   
   With objWord
       .Visible = True
       .Documents.Open (strDocPath)
       'move to each bookmark, and insert correct text.
       .ActiveDocument.Bookmarks (txtFirstName.Select)
       .Selection.Text = (CStr(Forms!tblTour!txtFirstName))
       .ActiveDocument.Bookmarks.Add Name:=txtFirstName,
       Range = Selection.Range

** continue the ActiveDocument and Selection statements for each bookmark that you have on the Word Document **
      
   End With

   'find out if the user would like to print the document
   'at this time.

   PrintResponse = MsgBox("Print this document?", vbYesNo)
   If PrintResponse = vbYes Then
      objWord.ActiveDocument.PrintOut Background:=False
   End If

   'release all objects
         
   Set objWord = Nothing
   Set dbs = Nothing

End Function

This line has an error:
.ActiveDocument.Bookmarks.Add Name:=txtFirstName,

When run, it gives me this message:

=====================

Compile Error:

Method or data member not found

[OK] [Help]

=====================

I realize this is a function, and in my C++ class we have to put the functions at the beginning of the code, where do we put functions for this type of thing? Button_OnClick?