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

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.


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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now