1

Can't make the document printout command work... I am running a mail merge, and that part works perfectly. My only issue is printing the doc after the mail merge runs. I have tried researching this on line, but have not been able to find anything similar. If I use, Printout Range:=Printcurrentpage everything works great, except it will only print the first page of 4 (obviously). But, if I use Printalldocument or printallpages, the print job seems to get stuck in a loop. The Word doc returns a message that says something about "Please wait...Word is trying to....." How can I code this so that it will: A. Print the doc B. Print the whole document

Here is the code...

    Private Sub cmdprint_Click()

    Dim Sheet As Worksheet, wsName As String, DataSource As String, WordPath As String
    Dim WordApp As New Word.Application, WordDoc As Word.Document, StrName As String

    With ActiveWorkbook
    DataSource = .FullName
    WordPath = .Path & "\MailMerge_DD220.docx"
    wsName = .Sheets("dd220").Name
    StrName = .Sheets("dd220").Range("C2").Text
    SavePath = .Path & "\DD220 Forms\"
    End With

    With WordApp
    .Visible = True
    .DisplayAlerts = wdAlertsNone
    Set WordDoc = .Documents.Open(WordPath, AddToRecentFiles:=False)
    With WordDoc
    'Select Data Source and Complete Mail Merge



    With .MailMerge
    .MainDocumentType = wdFormLetters
    .Destination = wdSendToNewDocument
    .OpenDataSource Name:=DataSource, ConfirmConversions:=False, ReadOnly:=False, nkToSource:=True,_
     AddToRecentFiles:=False, PassWordDocument:="", PasswordTemplate:="",WritePassWordDocument:="",_
      WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,  SubType:=wdMergeSubTypeAccess, _              
     Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=DataSource;Mode=Read;" & _
     "Extended Properties=""HDR=YES;IME", SQLStatement:="SELECT * FROM `" & wsName & "$`", SQLStatement1:=""
     With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    .ActiveRecord = wdDefaultActiveRecord
    End With
    .Execute Pause:=False
    End With
    .Close SaveChanges:=False
    End With

    With .ActiveDocument
     NewFileName = StrName & " - DD220 - " & Format(Date, "dd mmm yyyy") & ".docx"
     .SaveAs savepath + NewFileName, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False

       WordApp.Application.DisplayAlerts = wdAlertsNone
     .PrintOut Background = False ', , Range:=wdPrintAllDocument

     .Close SaveChanges:=False

    End With

   .DisplayAlerts = wdAlertsAll
   .Quit
    End With
    Set WordDoc = Nothing: Set WordApp = Nothing


     End Sub
ChipsWorld
  • 13
  • 4
  • It would help if the entire error message were given - to know what Word is telling you it's trying to do... 1. If you skip printing, closing and quitting Word (and make sure Word is viislbe), then go into Word - CAN YOU PRINT the document as a user? If not, what kind of message to you get? Do you see anything odd in the document? – Cindy Meister Oct 21 '19 at 17:08
  • Have you tried simplifying the line to `.PrintOut` without any additional arguments? I've not messed around a whole lot with printing Word from Excel, but that is what has worked for me in the past. – Cyril Oct 21 '19 at 17:10
  • Cindy...Sorry...The Message in Word says: "Just a minute, Word needs to finish printing" and the print job is showing in the print server, but just sits there "Spooling" forever. It will NEVER print. The Mail Merge works fine, the letter is created and I can open it in Word and print it, but the automated print is a failure. – ChipsWorld Oct 22 '19 at 21:38
  • OK...may have found a clue? When I stepped through the code, and the merged document was opened in Word, I got to the .Print out line, and saw a warning that said... "Your margins and pretty small. It's possible some of your content will be cut off when you print. Do you still want to Print?" This is weird though, as I only get this message when using .Print out Range:=wdPrintalldocument and not when using .print out Range:=wdPrintCurrentPage. – ChipsWorld Oct 23 '19 at 17:47
  • I did check the whole document, and the margins are the same for all pages. They are thin margins, but I have never had this issue pop-up before in other merges, and I have done quite a few using similar documents with thin margins. How could I override this message and allow Word to print anyway? IT also does not explain the "Spooling" issue previously mentioned. – ChipsWorld Oct 23 '19 at 17:47
  • Did more research...I believe the "Spooling" and Word message is directly related to the margins issue. Found some stuff, and changed the printing code section to (see above update). Still doesn't work, but getting hopeful someone here can help me figure this out. – ChipsWorld Oct 23 '19 at 22:11

0 Answers0