Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: Word MailMerge VB6 only works once through

    Hi there
    I'm very new to VB so if the code below looks shaky, that's why It is all part of a larger programme. The aim is to print address details to envelopes using a dotx template and information from an access database. It works well once but the second time around will not print the names and addresses yet does everything else eg open docs, close docs, print spaces and paragraph returns perfectly.

    I can't figure out what is wrong. If I query the Label table the second time around it says there are 135 records in it, so its not empty which is what I thought was happening somehow.

    Am I not closing something properly? eg word app/doc

    I have looked everywhere for help. Can anyone see whats wrong?

    It is a work in progress so I have notes to myself all over the place, I shall tidy up later


    Thanks
    Marciej.

    Code:
    'create variable names
    Dim wrd As Word.Application 'early binding
    Dim wrdDoc As Word.Document
    Dim envPath As String
    Dim envName As String
    Dim fullEnvName As String
    
    'variable to access the addressField bookmark in env.dotx template
    Dim addressField As Range
    
    'set variables to open Word template
    envPath = "C:\FolderName\"
    envName = "env3"
    
        'turn off automatic error handling
        On Error Resume Next
        'if word is already running use it
        Set wrd = GetObject(, "Word.Application")
        
        'if word is not open create new instance explicitly
        If wrd Is Nothing Then
        Set wrd = CreateObject("Word.Application") 'early binding
        End If
    
        'reset error handling
        On Error GoTo 0
    
    With wrd
    
        fullEnvName = envPath & envName & ".dotx"
      
        'delayed Error Handler
        On Error Resume Next
        
        'check to see if template is already open
        'if file is not already open it will give error - bad file name
        'this error will be ignored and next command initiated
        Set wrdDoc = .Documents(fullEnvName)
        
        'if env template isn't open
        If wrdDoc Is Nothing Then
            'open it
            Set wrdDoc = .Documents.Open(fullEnvName) 'POSSIBLE ERROR HERE TO CATCH
        Else
            'make env.dotx the active document
            wrdDoc.Activate
                
        End If
           'make word invisible to user
            .Visible = False
       
            
        'position cursor at beginning of addressField in template
        Set addressField = wrdDoc.Bookmarks("addressField").Range
        addressField.Select
        
                  
        'access Label Table and get address details
        wrdDoc.MailMerge.OpenDataSource Name:= _
        "C:\FolderName\Data\NLData.mdb", LinkToSource:=True, _
        Connection:="TABLE Labels", _
        SQLStatement:="SELECT * FROM [Labels] Order by LblType, LblNbr"
        
            
        'extract details from Label Table and merge fields into Word template
        wrdDoc.MailMerge.Fields.Add Range:=Selection.Range, Name:="LblTitle"
        .Selection.TypeText Text:=" blah "
        wrdDoc.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
            "LblFirstName"
            .Selection.TypeText Text:="  " 'add space
        wrdDoc.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
            "LblMInitials"
            .Selection.TypeText Text:="  "
        wrdDoc.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
            "LblLastName"
        .Selection.TypeParagraph 'add a new paragraph
        wrdDoc.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
            "LblAddress1"
        .Selection.TypeParagraph
        wrdDoc.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
            "LblAddress2"
        .Selection.TypeParagraph
        wrdDoc.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
            "LblSuburb"
        .Selection.TypeText Text:="  "
        wrdDoc.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
            "LblPostCode"
        .Selection.TypeParagraph
        
            'create new MSWord document and execute merge
            With wrdDoc.MailMerge
                .Destination = wdSendToNewDocument
                .MailAsAttachment = False
                .MailAddressFieldName = ""
                .MailSubject = ""
                .SuppressBlankLines = True
            
                    With .DataSource
                    .FirstRecord = wdDefaultFirstRecord
                    .LastRecord = wdDefaultLastRecord
                    End With
                    
                .Execute Pause:=True
            End With
            
        'print out merged document using MSWord
        .Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
           wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
            Collate:=False, Background:=True, PrintToFile:=False
           
            'close active window without saving
            .ActiveDocument.Close (wdDoNotSaveChanges)
            'close env.dotx without saving
            .ActiveDocument.Close (wdDoNotSaveChanges)
            wrdDoc.Application.Quit
            'destroy the instance of Word Document Object
            Set wrdDoc = Nothing
            
               
    End With
    
    'code-------------------------
    'only close word if there are no other documents opened by the user
    'use this If .Application.Documents.Count = 0 THEN...
    
    'close open window
    wrd.Quit
    'Destroy the instance of Word Application Object
    Set wrd = Nothing
    
    
    ErrorHandler:
    Code here......
    
    End Sub
    Last edited by loquin; 04-14-09 at 17:05. Reason: add [code] [/code] tags

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    You need to fix your unqualified references.

    For example here in red word application is implied:
    Code:
    wrdDoc.MailMerge.Fields.Add Range:=Selection.Range, Name:="LblTitle"
        .Selection.TypeText Text:=" blah "
    Hope that helps...

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Terrific, it now works perfectly. Thank you very much for your help. I shall be more careful about using qualified references in future.
    Warm regards

    Marciej.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •