Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 06-02-2020, 09:14 AM
Charles Kenyon Charles Kenyon is offline using mailmerge to include a grouped information in a letter Windows 10 using mailmerge to include a grouped information in a letter Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,083
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Look into using styles for your formatting. These can also be created and applied using code and will make your editing in the future much easier.


Importance of Styles in Word
Reply With Quote
  #17  
Old 06-02-2020, 09:57 AM
Tesla Tesla is offline using mailmerge to include a grouped information in a letter Windows 7 32bit using mailmerge to include a grouped information in a letter Office 2007
Advanced Beginner
using mailmerge to include a grouped information in a letter
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

Hello, thank you again for the help, but I still have one difficulty. Some debtors have may invoices which cover two or three pages. The code i have enables me to list those invoices but concluding information below the list is not seen. I wish I could generate a letter which lists invoices and the message below the invoices. I attach an example of invoices I have and the code to generate the letter.
Thank you for helping me to make a code that will enable me to produce a letter of one or more pages containing the list of invoices and concluding information.
Attached Files
File Type: doc letter-3.doc (47.0 KB, 10 views)
File Type: xls invoice list.xls (33.5 KB, 7 views)
Reply With Quote
  #18  
Old 06-02-2020, 03:28 PM
macropod's Avatar
macropod macropod is offline using mailmerge to include a grouped information in a letter Windows 7 64bit using mailmerge to include a grouped information in a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Tesla View Post
Hello, thank you again for the help, but I still have one difficulty. Some debtors have may invoices which cover two or three pages. The code i have enables me to list those invoices but concluding information below the list is not seen.
The number of pages a particular group might span is of no consequence; anything that doesn't fit on the first page will appear on the second or later page, as appropriate.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #19  
Old 06-02-2020, 10:56 PM
Tesla Tesla is offline using mailmerge to include a grouped information in a letter Windows 7 32bit using mailmerge to include a grouped information in a letter Office 2007
Advanced Beginner
using mailmerge to include a grouped information in a letter
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

Thank you macropod, may be I explained less clearly my problem. If I can summarize what I want is this:

Introductory comment
Invoice 1
Invoice 2
Invoice 3

page break

Invoice 4
Invoice 5
Invoice 6

page break

Invoice 7
Invoice 8
Invoice 9

concluding comment

The problem I have is that "concluding comment" desapears when invoices are on more than one pages. Any help is welcome
Reply With Quote
  #20  
Old 06-02-2020, 11:46 PM
macropod's Avatar
macropod macropod is offline using mailmerge to include a grouped information in a letter Windows 7 64bit using mailmerge to include a grouped information in a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

That problem has nothing to do with the number of rows in a group but everything to do with your failure to include the dummy last row specified repeatedly in the tutorial.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #21  
Old 06-03-2020, 01:49 AM
Tesla Tesla is offline using mailmerge to include a grouped information in a letter Windows 7 32bit using mailmerge to include a grouped information in a letter Office 2007
Advanced Beginner
using mailmerge to include a grouped information in a letter
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

Thank you very much, you helped me.
Reply With Quote
  #22  
Old 06-06-2020, 02:41 AM
Tesla Tesla is offline using mailmerge to include a grouped information in a letter Windows 7 32bit using mailmerge to include a grouped information in a letter Office 2007
Advanced Beginner
using mailmerge to include a grouped information in a letter
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

Hello again, I am returning after you helped me to find my first solution. I think important to post my new problem here because it is a continuation of previous conversations.

The first solution enabled me to produce a letter having an introduction, a body composed of a list of invoices and a conclusion. My new problem is to find a solution to include more than one list in the body of the letter like this:

Introduction
Body
Table 1
Table 2
Table 3
Conlusion

I am a novice in this subject of mail merge. Any help is welcome
Reply With Quote
  #23  
Old 06-06-2020, 02:43 AM
macropod's Avatar
macropod macropod is offline using mailmerge to include a grouped information in a letter Windows 7 64bit using mailmerge to include a grouped information in a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

What determines the content of these other tables?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #24  
Old 06-06-2020, 04:04 AM
Tesla Tesla is offline using mailmerge to include a grouped information in a letter Windows 7 32bit using mailmerge to include a grouped information in a letter Office 2007
Advanced Beginner
using mailmerge to include a grouped information in a letter
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

Table 1 is a list of invoices issues to the debtor. Table 2 is a list of delivery notes of goods my company purchased from that debtor. In fact we buy and sell to each other. I want to inform the debtor (creditor also) the list of invoices my company issued to them and a list of delivery notes we received confirming goods we purchased from that debtor. Between the two tables there is a small comment. This is how it should look like (I attach sample invoices and delivery notes. In green color are invoices, in blue color are delivery notes):

Introduction


Body

These are invoices we issued to you

Table 1(list of invoices)

These are delivery notes we received from you

Table 2 (list of delivery notes)

Conlusion
Attached Files
File Type: xls list-2.xls (41.0 KB, 14 views)
File Type: doc letter.doc (47.5 KB, 9 views)
Reply With Quote
  #25  
Old 06-06-2020, 04:16 PM
macropod's Avatar
macropod macropod is offline using mailmerge to include a grouped information in a letter Windows 7 64bit using mailmerge to include a grouped information in a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You can't use a workbook with such a data layout for the kind of merge you want to do.

You could, however, use a structure such as that in the attached workbook with a mailmerge main document like the one also attached - note the completely different approach to the mailmerge in that document, which uses two DATABASE fields to group the data.

As the DATABASE field code is written, it is assumed the mailmerge main document will be stored in the same folder as the Excel workbook.

Simply connect the 'Letter' document to the 'List' workbook for an ordinary 'letter' merge, using the 'Clients' worksheet as the data source, then complete the merge. There's a macro in the 'Letter' document that intercepts the Finish & Merge>Edit Individual documents process to do some post-merge table formatting.
Attached Files
File Type: xls List.xls (47.5 KB, 75 views)
File Type: doc letter.doc (51.5 KB, 65 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #26  
Old 06-08-2020, 12:00 AM
Tesla Tesla is offline using mailmerge to include a grouped information in a letter Windows 7 32bit using mailmerge to include a grouped information in a letter Office 2007
Advanced Beginner
using mailmerge to include a grouped information in a letter
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

Thank you very much macropod for your time to write the code and clear explanations, the code works without problem. I wish I could be able to help others as well as a recognition of your help to me. If possible could you please show me how to add subtotals for each month and general total?.
Thank you
Reply With Quote
  #27  
Old 06-08-2020, 01:29 AM
macropod's Avatar
macropod macropod is offline using mailmerge to include a grouped information in a letter Windows 7 64bit using mailmerge to include a grouped information in a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Tesla View Post
If possible could you please show me how to add subtotals for each month and general total?
It would have been helpful had you said from the outset that's what you wanted to do. I don't enjoy re-writing code - a considerable amount of which would be required - just to satisfy belated scope creep.

Try replacing the existing macro with:
Code:
Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim s As Long, c As Long, r As Long, t As Long
Dim Tbl As Table, StrRDt As String, StrCDt As String, Rng As Range
Dim u As Long, v As Long, w As Long, x As Long
ActiveDocument.MailMerge.Execute
With ActiveDocument
  For s = 1 To .Sections.Count ' - 1
    Set Tbl = .Sections(s).Range.Tables(1)
    With Tbl
      .Range.ParagraphFormat.Alignment = wdAlignParagraphRight
      .Rows.Alignment = wdAlignRowCenter
      .Rows(1).HeadingFormat = True
      .Rows(1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
      For c = 3 To 5
        .Columns(c).PreferredWidthType = wdPreferredWidthPoints
        .Columns(c).PreferredWidth = InchesToPoints(1)
      Next
      StrRDt = Split(.Cell(.Rows.Count, 1).Range.Text, vbCr)(0)
      StrRDt = Split(StrRDt, "-")(1) & "-" & Split(StrRDt, "-")(2)
      .Rows.Add: t = .Rows.Count
      For r = t - 1 To 2 Step -1
        StrCDt = Split(.Cell(r, 1).Range.Text, vbCr)(0)
        StrCDt = Split(StrCDt, "-")(1) & "-" & Split(StrCDt, "-")(2)
        If StrRDt = StrCDt Then
          For c = 3 To 5
            Select Case c
              Case 3: u = u + Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1)
              Case 4: v = v + Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1)
              Case 5: w = w + Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1)
            End Select
          Next
        ElseIf (StrRDt <> StrCDt) Or (r = 2) Then
          .Cell(t, 1).Range.Text = StrRDt
          .Rows(t).Range.Font.Italic = True
          For c = 3 To 5
            Select Case c
              Case 3
                .Cell(t, c).Range.Text = Format(u, "£#,##0")
                u = Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1)
              Case 4
                .Cell(t, c).Range.Text = Format(v, "£#,##0")
                v = Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1)
              Case 5
                .Cell(t, c).Range.Text = Format(w, "£#,##0")
                w = Split(Split(.Cell(r, c).Range.Text, vbCr)(0), "£")(1)
            End Select
          Next
          StrRDt = StrCDt: t = r + 1
          If r <> 2 Then
            .Rows.Add Tbl.Rows(r + 1)
          Else
            Exit For
          End If
        End If
      Next
      .Cell(t, 1).Range.Text = StrRDt
      For c = 3 To 5
        Select Case c
          Case 3
            .Cell(t, c).Range.Text = Format(u, "£#,##0")
          Case 4
            .Cell(t, c).Range.Text = Format(v, "£#,##0")
          Case 5
            .Cell(t, c).Range.Text = Format(w, "£#,##0")
        End Select
      Next
      .Rows(t).Range.Font.Italic = True
      .Rows.Add: t = .Rows.Count
      For c = 3 To 5
        Set Rng = .Cell(t, c).Range
        Rng.Collapse wdCollapseStart
        Rng.Fields.Add Rng, wdFieldEmpty, "=SUM(ABOVE)/2 \# £,#0", False
      Next
      .Cell(t, 1).Range.Text = "TOTAL:"
      .Rows(t).Range.Font.Bold = True
    End With
    Set Tbl = .Sections(s).Range.Tables(2)
    With Tbl
      .Range.ParagraphFormat.Alignment = wdAlignParagraphRight
      .Rows.Alignment = wdAlignRowCenter
      .Rows(1).HeadingFormat = True
      .Rows(1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
      StrRDt = Split(.Cell(.Rows.Count, 1).Range.Text, vbCr)(0)
      StrRDt = Split(StrRDt, "-")(1) & "-" & Split(StrRDt, "-")(2)
      .Rows.Add: t = .Rows.Count
      For r = t - 1 To 2 Step -1
        StrCDt = Split(.Cell(r, 1).Range.Text, vbCr)(0)
        StrCDt = Split(StrCDt, "-")(1) & "-" & Split(StrCDt, "-")(2)
        If StrRDt = StrCDt Then
          x = x + Split(Split(.Cell(r, 2).Range.Text, vbCr)(0), "£")(1)
        ElseIf (StrRDt <> StrCDt) Or (r = 2) Then
          .Cell(t, 1).Range.Text = StrRDt
          .Rows(t).Range.Font.Italic = True
          .Cell(t, 2).Range.Text = Format(x, "£#,##0")
          StrRDt = StrCDt: t = r + 1
          If r <> 2 Then
            .Rows.Add Tbl.Rows(r + 1)
          Else
            Exit For
          End If
        End If
      Next
      .Cell(t, 1).Range.Text = StrRDt
      .Cell(t, 2).Range.Text = Format(x, "£#,##0")
      .Rows(t).Range.Font.Italic = True
      .Rows.Add: t = .Rows.Count
      Set Rng = .Cell(t, 2).Range
      Rng.Collapse wdCollapseStart
      Rng.Fields.Add Rng, wdFieldEmpty, "=SUM(ABOVE)/2 \# £,#0", False
      .Cell(t, 1).Range.Text = "TOTAL:"
      .Rows(t).Range.Font.Bold = True
    End With
  Next
  .Fields.Unlink
End With
Set Rng = Nothing: Set Tbl = Nothing
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #28  
Old 06-08-2020, 09:28 AM
Tesla Tesla is offline using mailmerge to include a grouped information in a letter Windows 7 32bit using mailmerge to include a grouped information in a letter Office 2007
Advanced Beginner
using mailmerge to include a grouped information in a letter
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

Hello again, I am really sorry I am a beginner,

Trying to replace existing macro was also a problem, I don't know how to do it. I went on View-Macros-Edit. When I printed the document, it came as if I did no thing.


Previously I thought I would do subtotals myself on the basis of the tutorial "Using Mailmerge To Create Category-Based Lists", I didn't want to give you the work I can learn to do myself, but the code you developed was different from the code included in the tutorial, so I couldn't know where to insert the code for subtotals and the general total. This is why I didn't express my request previously.
Reply With Quote
  #29  
Old 06-08-2020, 03:34 PM
macropod's Avatar
macropod macropod is offline using mailmerge to include a grouped information in a letter Windows 7 64bit using mailmerge to include a grouped information in a letter Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Tesla View Post
Trying to replace existing macro was also a problem, I don't know how to do it. I went on View-Macros-Edit. When I printed the document, it came as if I did no thing.
Open the mailmerge main document, then press Alt-F11 to open the VBA Editor. Delete the exiting MailMergeToDoc macro and replace it with the one in my previous post, then press Alt-F11 to close the VBA Editor.
Quote:
Originally Posted by Tesla View Post
Previously I thought I would do subtotals myself on the basis of the tutorial "Using Mailmerge To Create Category-Based Lists", I didn't want to give you the work I can learn to do myself, but the code you developed was different from the code included in the tutorial, so I couldn't know where to insert the code for subtotals and the general total. This is why I didn't express my request previously.
The tutorial has complete instructions on how to do that but you never mentioned that as a goal. In any event, as I said a few posts back, you can't do the kind of multi-table merge you want using that approach, let alone a workbook as yours was structured.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #30  
Old 06-09-2020, 12:23 AM
Tesla Tesla is offline using mailmerge to include a grouped information in a letter Windows 7 32bit using mailmerge to include a grouped information in a letter Office 2007
Advanced Beginner
using mailmerge to include a grouped information in a letter
 
Join Date: Sep 2018
Posts: 59
Tesla is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Open the mailmerge main document, then press Alt-F11 to open the VBA Editor. Delete the exiting MailMergeToDoc macro and replace it with the one in my previous post, then press Alt-F11 to close the VBA Editor.

I did as instructed above but I don't see "MailMerge ToDoc macro" so that I can replace it. I attach a sreen shot of my computer
Attached Files
File Type: docx New Microsoft Office Word Document (2).docx (170.7 KB, 11 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
using mailmerge to include a grouped information in a letter Macro to insert an image to a word mailmerge document based on the value of a mailmerge field? Jake93 Mail Merge 3 07-02-2019 05:38 PM
using mailmerge to include a grouped information in a letter Use L and R arrow to move cursor letter to letter, not top of the line mellowkitten Word 2 05-12-2016 07:23 PM
How to replace a letter to random letter with different color? cikanoz87 Word 7 06-18-2015 09:43 PM
using mailmerge to include a grouped information in a letter Mailmerge with grouped data Keith Henderson Mail Merge 14 02-04-2015 01:45 PM
Directory MailMerge with Category Grouped Lists on Labels screech Mail Merge 3 06-18-2014 11:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:20 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft