Link to home
Start Free TrialLog in
Avatar of Vriaeliss
Vriaeliss

asked on

Two MailMerge Print Jobs when there should be only one. VBA in Excel.

Does anyone know why this code sends two print jobs when run? The first print job is the template only... it shows the <<FIELDS>> instead of the data substitutes - <<NAME>> instead of Bob Smith. The second job follows immediately and prints the merge correctly. Is there a way to avoid wasting the extra sheet of paper for every time this gets run?

'*******Print the MailMerge*******'
    With ActiveDocument.MailMerge
        .Destination = wdSendToPrinter
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        Dialogs(wdDialogFilePrint).Show
        .Execute Pause:=False
    End With
'*********************************'

=====================The whole picture=================
Sub WordUp()
'=====================================
' WordUp Macro
'=====================================
' Purpose: Perform the MSWord MailMerge from this Macro
' Created: 4/25/06, Beau Holder
' Last Modified: 5/1/06 10:49am - Broke up longer lines to improve readability
'                               - changed directory to where the MailMerge Template is
'=====================================
'*******DECLARE VARIABLES*******'
    Dim appWd As Word.Application
    Dim WdDoc As Word.Document
    Dim sFile As String
'*******************************'
'*******Setup the Word application object*******'
    On Error Resume Next
        Set appWd = GetObject(, "Word.Application")
    If appWd Is Nothing Then
        Set appWd = GetObject("", "Word.Application")
    End If
    On Error GoTo 0
    sFile = fGetFilePath
'***********************************************'
'*******MailMerge Happens here*******'
    With appWd
        .Visible = True
        Set WdDoc = .Documents.Open(Filename:="c:\Doc.doc")
        ActiveDocument.MailMerge.OpenDataSource Name:=sFile, ConfirmConversions:=False, _
            ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
            PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", _
            Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
            "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=sFile;" & _
            "Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";" & _
            "Jet OLEDB:Registry", SQLStatement:="SELECT * FROM `data`", SQLStatement1:="", _
            SubType:=wdMergeSubTypeAccess
    End With
'************************************'
'*******Print the MailMerge*******'
    With ActiveDocument.MailMerge
        .Destination = wdSendToPrinter
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        Dialogs(wdDialogFilePrint).Show
        .Execute Pause:=False
    End With
'*********************************'
'*******Close the document now that the merge is done and quit Word*******'
    ActiveDocument.Close SaveChanges:=True
    appWd.Quit
'*************************************************************************'
'*******CLEAR OUT WORD OBJECTS*******'
    Set appWd = Nothing
    Set WdDoc = Nothing
'************************************'
End Sub

Thanks in advance.
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

The mailmerge to to the printer should print the result document.
The show method with the print dialog will print the current document.
Avatar of Vriaeliss
Vriaeliss

ASKER

That clears things up a bit. Is there any way to supply the dialogue box without printing the current document? (added points for additional question asked)
ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Seems that using Dialogs(wdDialogFilePrint).Display instead of Dialogs(wdDialogFilePrint).Show fixed it. Thanks for helping Graham.

'*******Print the MailMerge*******'
    With ActiveDocument.MailMerge
        .Destination = wdSendToPrinter
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        Dialogs(wdDialogFilePrint).Display 'not .Show
        .Execute Pause:=False
    End With
'*********************************'
It depends what you are trying to do with the dialog.
I'm just trying to get it to prompt the print dialogue so I can manually setup where the mail merge will be printed and be able to change the printer properties. A couple quick tests seemed to do that with that change. If you were referring to something else, I'd appreciate more detail (or some links to read up on).
No. I just couldn't see how you were using the information from the dialog. However, it seems that the Display method can be used to change the default printer. It just doesn't print.
Ok, that makes sense, and it does what I want. the .Execute is what does the actual print job, I'm just happy that I can change the default printer and settings without printing the extra page ^_^
The great thing about this forum is that we all learn, as I have just done.
Thanks and good luck.