Pre-formatted label printer

macropod · #5 03-04-2013, 04:49 AM

Hi jdorin,

If you're opening the document via a desktop shortcut, you really don't need a macro for anything more than replicating whatever parts of "each Word step in how to configure to print in those labels" need to be done each time you open the document, including selecting the output printer. However, none of that is in the code you posted. Everything else - labels, mergefields, etc - should already be present in the document. Automating the actual merge execution, printing and closure of the mailmerge main document is also quite feasible. See below.

The SQL warning is a standard message for a mailmerge document. The only way you can not have that warning is to drive the mailmerge setup via a macro from a document that, although it contains the labels & mergefields, isn't saved as a mailmerge main document. Although the necessary code to do this is in the macro you recorded, embedding all the complexity of connecting to the data source and running the data query in the macro actually makes it harder for the 'typical' user to do maintain the setup if, say, the Excel workbook is renamed or moved to a new folder - or you move between PCs and Macs.

The print margins warning suggests the printer configuration and/or page setup isn't quite right, even though you're apparently getting the desired output. Depending on the printer driver you're using, you may be able to reduce its non-printing zones around the page edges (not something you can do with many (if not all) of the standard MS driver offerings). Get the zones small enough, or increase the page margins sufficiently, and the message should go away. Even so, code below suppresses the message.

The first save prompt concerns your output document. If you send the output direct to the printer, instead of to a new document, the prompt for that document won't occur. Sending direct to the printer is the approach the macro takes.

The second save prompt concerns your mailmerge main document. That message can be suppressed, which the macro also does.

And, when it's finished processing, the macro quits Word (if there are no other documents open). On that front, if Word is already running when the shortcut for the mailmerge is used, I expect it'll simply open the document in the existing Word session. In that case, if there are other unsaved documents open, the user will get the usual 'save before closing/cancel message' - from which they'll want to cancel.

Regarding the environment, the code should work on any PC (not sure about Macs). Since I don't have the code that would be required to select your Epson CX4200, the code includes the ability to choose a printer at run-time (again, a bit more flexible if the printer's network name, for example, changes).

Code:

Private Sub Document_Open()
Application.ScreenUpdating = False
Dim bPrnBkgrnd As Boolean, CurrPrn As Variant
'Save current background printing setting
bPrnBkgrnd = Options.PrintBackground
'Save current printer setting
CurrPrn = Application.ActivePrinter
'Cannot suppress warning if background printing is on
Options.PrintBackground = False
'Choose a printer
Application.Dialogs(wdDialogFilePrintSetup).Show
'Run the mailmerge
With ThisDocument
  With .MailMerge
    .Destination = wdSendToPrinter
    .SuppressBlankLines = True
    With .DataSource
      .FirstRecord = wdDefaultFirstRecord
      .LastRecord = wdDefaultLastRecord
    End With
    'Turn off DisplayAlerts
    Application.DisplayAlerts = wdAlertsNone
    .Execute Pause:=False
  End With
  'Tell Word the document has already been saved.
  'This suppresses the 'close' warning.
  .Saved = True
End With
'Turn on DisplayAlerts again
Application.DisplayAlerts = wdAlertsAll
'Restore original printer
Application.ActivePrinter = CurrPrn
'Restore original background printing setting
Options.PrintBackground = bPrnBkgrnd
Application.ScreenUpdating = True
If Documents.Count > 1 Then
  'ThisDocument.Close
Else
  'Application.Quit
End If
End Sub

Note: As yet, the code doesn't close/exit - the lines to do that are commented out, so you can test the code before committing to it. For testing purposes, you might also want to save on page, which you can do by changing:
.Destination = wdSendToPrinter
to:
.Destination = wdSendToNewDocument

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
AutoCorrect with Formatted Text	pmokover	Word	16	10-30-2024 02:50 AM
Reversing Formatted Fractions?	Rusty123	Word	1	03-05-2012 05:44 PM
MS Word 2003, printer queue in 'printer properties' shows 1 job; no job in printer	benhuxham	Word	0	07-25-2011 06:58 PM
Import formatted text from Word into PowerPoint	parboy	PowerPoint	0	07-06-2011 08:52 AM
Odd behaviour in formatted cells	furface00	Excel	3	03-11-2011 08:02 AM