Print two copies of same letter together

[aman]

Hi All

The following code prints off one copy of all the letters and then another copy of all the letters but i want it to print two copies of each letter together. I hope it makes sense. I want somebody to modify the code accordingly.

Sub WordSetup(fnTemplate As String, fnBackGroundPic As String, txtbox As String)
    On Error Resume Next
    MsgBox txtbox
    Dim strworkbookname As String
    strworkbookname = "J:\System1.mdb"
    Set WordApp = GetObject(, "Word.Application")
    
    If Err.Number <> 0 Then
            'Launch a new instance of Word
            Err.clear
        On Error GoTo ErrorHandler
        Set WordApp = CreateObject("Word.Application") 'New Word.Application
    End If
    WordApp.Documents.Add (fnTemplate)
    Set WordDoc = WordApp.ActiveDocument
    'WordApp.Visible = True
    InsertHeaderLogo (fnBackGroundPic)
    With WordDoc.MailMerge
  .MainDocumentType = 0
  .Destination = 1
  .OpenDataSource _
            Name:=strworkbookname, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Data Source=" & strworkbookname & ";Mode=Read", _
            sqlstatement:="SELECT * FROM `tblmaster` where Printpoolno='" & txtbox & "'"
            'MsgBox sqlstatement
  .Execute
  .Execute
  .Parent.Close 0
  End With
  MsgBox "The letters have been printed off module"
ExitErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error (" & Err.Number & ") : " & Err.Description & vbCrLf & vbCrLf & "Exiting procedure - WordSetUp", vbCritical
    Resume ExitErrorHandler
End Sub

 

[namliam]

Well you are doing a mailmerge with word, doing two prints will always result in the issue you describe... The module is basicaly doing the merge 2 times with the double ".execute"

How about doing only 1 print, but have the letter 2 times in the Merge document

 

[aman]

Thanks but how will I change the code accordingly?

 

[namliam]

You dont "really" change the code... Well you do .. you remove one of the .Execute lines.

Then you go into your word document and copy/paste the letter below its own, so you have 2 letters in one document. One merge should then put 2 letters to the same address.

 

[aman]

Hi namiliam

Sorry for the delay in reply. I want to print two copied of the same letter through code. The letter is already typed in word document and now If I use the code I sent u earlier that prints off all the letters in merge document and then a copy of it after. But I want to print off two copies of same letter together.

I hope u understand what i mean.

Thanks

 

[pr2-eugin]

I am either not understanding or you do not realize that you actually have the solution at hand. IMO, this

now If I use the code I sent u earlier that prints off all the letters in merge document and then a copy of it after.

is also the same as..

I want to print off two copies of same letter together.

Print a letter * 2 = 2 Copies of the same letter? Is that not correct?

 

[aman]

Hi Pr2-eugin

Please see below. This is what I want to acheive.

Letter order when printed

When more than one case is printed the letters print out in the wrong order. Each letter and copy should come out on the printer one after the other

Current result - incorrect

Policy number order when printed
4111238
1234567
4111238 copy
1234567 copy


Correct result

Policy number order when printed
4111238
4111238 copy
1234567
1234567 copy


Thanks

 

[pr2-eugin]

So is this Print out twice always going to be 2? Or would it change to 3 or 4 in the future? Because the only way I can think of is to alter the SQL statement, because once the data leaves Access we cannot have control over the Page changes (could be possible with probably a lot of excessive coding, which I am unaware of).

 

[aman]

Hi

I just need 2 copies always of each letter but in the way we discussed earlier.

Many thanks

 

[pr2-eugin]

Try this..

Sub WordSetup(fnTemplate As String, fnBackGroundPic As String, txtbox As String)
On Error Resume Next
    [COLOR=Green]'MsgBox txtbox[/COLOR]
    Dim strworkbookname As String
    strworkbookname = "J:\System1.mdb"
    Set WordApp = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
       [COLOR=Green] 'Launch a new instance of Word[/COLOR]
        Err.clear
        On Error GoTo ErrorHandler
        Set WordApp = CreateObject("Word.Application") 'New Word.Application
    End If
    
    WordApp.Documents.Add (fnTemplate)
    Set WordDoc = WordApp.ActiveDocument
    'WordApp.Visible = True
    InsertHeaderLogo (fnBackGroundPic)
    
    With WordDoc.MailMerge
        .MainDocumentType = 0
        .Destination = 1
        .OpenDataSource _
        Name:=strworkbookname, _
        AddToRecentFiles:=False, _
        Revert:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Data Source=" & strworkbookname & ";Mode=Read", _
                    sqlstatement:="[COLOR=Red][B]SELECT tmpU.* FROM ([/B][/COLOR]SELECT * FROM tblmaster where Printpoolno='" & txtbox & "'" & _
                                  "[COLOR=Red][B]UNION ALL SELECT * FROM tblmaster where Printpoolno='" & txtbox & "') tmpU ORDER BY tmpU.[Policy number];[/B][/COLOR]"
        .Execute
    .Parent.Close 0
    End With
    MsgBox "The letters have been printed off module"
    
ExitErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error (" & Err.Number & ") : " & Err.Description & vbCrLf & vbCrLf & "Exiting procedure - WordSetUp", vbCritical
    Resume ExitErrorHandler
End Sub

I have highlighted the changes I made.

 

[aman]

Thanks pr2_eugin . It works exactly the way I wanted. Many thanks

 

[pr2-eugin]

Glad to help ! Good Luck !

 

[namliam]

Didnt think of doing a union to double up on the information instead of doubling up on the word document....

Advantage of having a doubling in the word doc offcourse is that you can watermark or something like adjusted header/footer of the copy page(s). And you can still use the same code to do singles copies as well by simply having a single letter doc merged.

Now you have duplicate data, from the code, which if you need to alter it is a problem per letter type you want to send.

Doubling the doc is the way to go imho, but if this works for you great

 

[aman]

Hi namiliam

My manager also wants me to print two copies of word document letters rather than using UNION to double up the information. Please can you help me in this.

Thanks

 

[aman]

ohhhh its working. If I write .execute twice then it will print two copies of each letter.