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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy a row 12 62
Using an encrypted  value to decrypt and display contents in vb6 9 51
Advice in Xamarin 21 79
Automatic Email Reminder 4 37
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

770 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