none
Automated Save As after MailMerge and .Destination = wdSendToPrinter in VBA RRS feed

  • Question

  • Hi Professionals,

    I am running Win7 MS Office 2010  and Visual Basic 6.5.

    I am trying to automate a .pdf Output and came so far to only enter a filename for the printed .pdf. Since the outfile is to have a similar name as the inputfile, the save as could be automated as well.

    Now for my code so far:

    Sub Macro2()
    
    Dim x, i As Integer
    Dim y As String
    x = 1
    i = 1
    y = x
    
    Do While i < 999
    
        If Dir("D:\Video\list\b-" + y + "\") = "" Then
        
            i = i + 1
            x = i
            y = x
            
          Else
            
        ActiveDocument.MailMerge.OpenDataSource Name:="D:\Video\list\b-" + y + "\" + y + ".xlsx", _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:= _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\Video\list\1.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:G" _
            , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
            wdMergeSubTypeAccess
        ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
        With ActiveDocument.MailMerge
            .Destination = wdSendToPrinter
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
           .Execute Pause:=False
            
            End With
            
        i = i + 1
        x = i
        y = x
        
        End If
    Loop
    
    
    
    End Sub
    

    Is there a way to insert something like

    With ActiveDocument.MailMerge .Destination = wdSendToPrinter .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With

    .SaveAs FileName:="C:\Users\user\Documents\b-" + y + "XXX.pdf"

    .Execute Pause:=False

    Help is much appreciated!

    Greetings

    • Moved by Carl Cai Monday, October 20, 2014 6:39 AM more related
    Sunday, October 19, 2014 12:23 PM

Answers

  • A possibility (actually, it is the only one I can think of):

    If your Adobe driver lets you define a FILE: output port, it probably lets you define a file name as an output port. Since you would have to do this on every machine that your code needs to run on, it's only really got a chance of working if this process only needs to run on a small number of systems.

    If you haven't attempted this before, this is an outline of how you would set one up in Windows 7:
     a. go into Devices and Printers

     b. add a printer

     c. add a local printer

     d. Select "Create a new port"

     e. In the dropdown, select "Local Port"

     f. click Next.

     g. you are prompted for a Port name. Enter the full path name of a suitable output file (it does not have to exist, but the folder structure will probably need to exist). For example,

    c:\pdfprint\pdffile.pdf

     h. continue with printer/driver selection.

    If you can print your output to that port, you should end up with a file with that name. As long as Word VBA (or whatever) waits for the print operation to complete, you should then be able to rename the output file and save it. 

    If you are mailmerging multiple documents to printer and you need them split into file names, you will also have to find some way to merge each document separately, because Word does not give you any opportunity to save the output after each separate document is merged. For example, you may be able to do one merge for each record in the data source.

    It may also be possible to use WMI (Windows Management Instrumentation) or some other API to modify the port name programmatically before each output (if you can be sure when the last one has finished). It isn't something I have ever attempted, but perhaps worth a look - I think you have to send a SQL query along the following lines via the WBEMScripting.SWBEMLocator object, then modify the printer's properties.

    "Select * from Win32_Printer WHERE Name LIKE 'theprintername%'"



    Peter Jamieson

    Tuesday, October 21, 2014 1:50 PM
  • Perhaps, then, you need to use the Document.ExportAsFixedFormat method. This gives you the option of telling Word to include bitmaps of the text when the font license doesn't permit embedding in the PDF file.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, October 21, 2014 11:07 PM

All replies

  • Hello,

    Since this issue is mainly related to development for word, I have helped you move this thread to

    Microsoft Office for Developers > Word for Developers

    forum to get supports.

    Regards.

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Carl Cai Monday, October 20, 2014 6:41 AM
    Monday, October 20, 2014 6:41 AM
  • Since you want to both save and print, you should consider sending the output to a new document, then simply using:
    ActiveDocument.Printout
    then:
    ActiveDocument.SaveAs
    with whatever SaveAs parameters you desire.

    For generating individual output files, see the 'Send Mailmerge Output to Individual Files' example at: http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html . It would be a trivial addition to incorporate .Printout into that code.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, October 20, 2014 12:20 PM
  • I don't want to do both.

    For further purposes like embedded fonts, I need to "SendToPrinter" and the printer is the AdobePDF-Printer. So it's a virtual printer that prints out the MailMerge to .pdf.

    But unluckily Word needs to ask for every record a save-location and name. I need to auto-fill in the text-box of the question and auto-accept everytime for record.

    Thanks for the help


    • Edited by vyrovcz Tuesday, October 21, 2014 12:09 PM
    Tuesday, October 21, 2014 12:08 PM
  • A possibility (actually, it is the only one I can think of):

    If your Adobe driver lets you define a FILE: output port, it probably lets you define a file name as an output port. Since you would have to do this on every machine that your code needs to run on, it's only really got a chance of working if this process only needs to run on a small number of systems.

    If you haven't attempted this before, this is an outline of how you would set one up in Windows 7:
     a. go into Devices and Printers

     b. add a printer

     c. add a local printer

     d. Select "Create a new port"

     e. In the dropdown, select "Local Port"

     f. click Next.

     g. you are prompted for a Port name. Enter the full path name of a suitable output file (it does not have to exist, but the folder structure will probably need to exist). For example,

    c:\pdfprint\pdffile.pdf

     h. continue with printer/driver selection.

    If you can print your output to that port, you should end up with a file with that name. As long as Word VBA (or whatever) waits for the print operation to complete, you should then be able to rename the output file and save it. 

    If you are mailmerging multiple documents to printer and you need them split into file names, you will also have to find some way to merge each document separately, because Word does not give you any opportunity to save the output after each separate document is merged. For example, you may be able to do one merge for each record in the data source.

    It may also be possible to use WMI (Windows Management Instrumentation) or some other API to modify the port name programmatically before each output (if you can be sure when the last one has finished). It isn't something I have ever attempted, but perhaps worth a look - I think you have to send a SQL query along the following lines via the WBEMScripting.SWBEMLocator object, then modify the printer's properties.

    "Select * from Win32_Printer WHERE Name LIKE 'theprintername%'"



    Peter Jamieson

    Tuesday, October 21, 2014 1:50 PM
  • Perhaps, then, you need to use the Document.ExportAsFixedFormat method. This gives you the option of telling Word to include bitmaps of the text when the font license doesn't permit embedding in the PDF file.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, October 21, 2014 11:07 PM
  • I know this is an old thread but I was coming up against a similar issue (need to send output to file first prior to printing). Here is a snippet of what I came up with after the main part of the mail merge: 

        With objMergeTemplate.MailMerge
            .MainDocumentType = wdFormLetters
              'long bit of datasouce code remove
                    .Destination = wdSendToNewDocument
                    .Execute Pause:=False
                    Set objOutputDoc = objWord.ActiveDocument
                    With objOutputDoc
                        .SaveAs2 strOPath & "\" & strOFileName & str(lngNextStart) & "To" & str(lngNextEnd), wdFormatDocument
                        .Close wdDoNotSaveChanges
                    End With
                    Set objOutputDoc = Nothing
        End With

    Wednesday, September 7, 2016 9:22 PM