Posted on 1999-01-20
Last Modified: 2010-05-03
Is it possible to use VB5 automation to open a mailmerge document find out what the merge fields are and fill them in using a recordset
Question by:shaneom
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 14

Expert Comment

ID: 1469610
Here is a response (coming from Dalin) :

1. in the general declaration area, add:

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Drop a text box to your form, call it txtTemplate
Drop another text box, call it txtMerge
Drop a command button, the name is command1

Paste these code to the form:

Private Sub Command1_Click()
    'declare variables
    Dim hHandle1 As Long
    Dim hHandle2 As Long
    Dim lLength As Long
    Dim sTempFile As String
    Dim sTemplateFile As String
    Dim sMergeFile As String
    Dim sDataFile As String
    Dim nFileNum As Integer
    Dim word As Object
    Dim sTempString As String
    Dim nLoopCount As Integer
    'find out if template exists
    On Error GoTo noFile
    'assign template file name
    lLength = 0
    sTempFile = App.Path & "\" & txtTemplate.Text
    lLength = FileLen(sTempFile)
    If lLength = 0 Then
        MsgBox "Template file does not exist!"
        Exit Sub
        sTemplateFile = sTempFile
    End If

    'assign merge file name
    sMergeFile = App.Path & "\" & txtMerge.Text
    'assign data file name
    sDataFile = App.Path & "\" & "DATA.TXT"
    On Error Resume Next
    'delete merge file
    Kill sMergeFile
    'delete data file
    Kill sDataFile

    On Error GoTo TrapError
    'open data file
    nFileNum = FreeFile
    Open sDataFile For Output As #nFileNum
    'write out first record
    sTempString = "John,Jones,Acme,123 Main Street,New York,NY,11221,Marketing Director,52000"
    sTempString = FillQuote(sTempString)
    Print #nFileNum, sTempString
    'write out second record
    sTempString = "Charles,Smith,Acme,456 Elm Way,New York,NY,11222,Sales Director,50000"
    sTempString = FillQuote(sTempString)
    Print #nFileNum, sTempString
    'close the file
    Close #nFileNum
    'change mouse
    Screen.MousePointer = vbHourglass
    'change caption
    Form1.Caption = "Running Microsoft Word..."
    'create word object
    Set word = CreateObject("Word.Basic")    'create a WordBasic object
    'find word
    hHandle1 = FindWindow(0&, "Microsoft Word")
    'show word
    hHandle2 = ShowWindow(hHandle1, 0)
    'change caption
    Me.Caption = "Opening Template..."
    'open the template
    word.fileopen sTemplateFile

    'open the data
    word.MailMergeOpenDataSource sDataFile
    'perform merge
    'change caption
    Me.Caption = "Saving New Document..."
    'save merge file
    word.filesaveas sMergeFile
    'close merge file
    word.Fileclose 2
    'change caption
    Me.Caption = "Opening Merged Document..."
    'open merge document
    word.fileopen sMergeFile
    'show merged document
    hHandle2 = ShowWindow(hHandle1, 1)

    'change caption
    Me.Caption = "" 
    'change mouse
    Screen.MousePointer = vbDefault
    Exit Sub

    Select Case Err
        Case 53:    'file not found
            Resume Next
        Case Else:
            MsgBox "Error " & Err & " - " & Error
            If Not word Is Nothing Then     'clean up object
                Set word = Nothing
            End If
            Resume MergeExit
    End Select

    Exit Sub
    Resume Next
End Sub

Function FillQuote(ByVal sTempString As String) As String
    'this function fills the string with double quotes around each field
    Dim nLoopCount As Integer
    Dim sFinalString As String
    For nLoopCount = 1 To Len(sTempString)
        If Mid$(sTempString, nLoopCount, 1) = "," Then
            sFinalString = sFinalString & Chr$(34) & "," & Chr$(34)
            sFinalString = sFinalString & Mid$(sTempString, nLoopCount, 1)
        End If
    Next nLoopCount
    sFinalString = Chr$(34) & sFinalString & Chr$(34)
    FillQuote = sFinalString
End Function


Author Comment

ID: 1469611
If I have a recordset of data that I want to display in my mailmerge document, how do I give a mailmerge field called "address" for example the address field from my resultset.

Accepted Solution

PedroG earned 100 total points
ID: 1469612
This is very simple, and it is all about automatation.

1. Note that your user must also have word installed.

2. In the references option, click the Msword8.olb file.

3. Wen you create the mail merge choose the Database as an acess database. Pupulate this database with the values of the recordset

4. Create a WordDocument objec (Document Object), open the mailmerge document.

5. Now merge it for the printer or other destination using the mailmerge property
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.


Expert Comment

ID: 1469613
tomorrow i will suplly the code, i have done it and i have it in home PC. Here in my work Pc there is no VB

Expert Comment

ID: 1469614
Tell me if you still need the piece of code

Author Comment

ID: 1469615

If you have the code handy I would appreciate it if you could send it.

Expert Comment

ID: 1469616
shaneom i'm sorry tha i've forgot it at home, but tomorrow i will post it here ok!

Author Comment

ID: 1469617
Tomorrow is fine

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month6 days, 7 hours left to enroll

634 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question