Posted on 1999-01-20
Medium Priority
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 300 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

609 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