Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-26-2021, 03:38 AM
JamesWood JamesWood is offline Record count on merge Windows 10 Record count on merge Office 2019
Advanced Beginner
Record count on merge
 
Join Date: Nov 2020
Posts: 37
JamesWood is on a distinguished road
Question Record count on merge

Hi guys


I have a simple code here which displays the record count of a mail merge in Word:


Sub CountRecipients()
MsgBox (ActiveDocument.MailMerge.DataSource.RecordCount)


End Sub


However if you edit the recipients list and untick a few, the record count macro above still remains the same, showing all records rather than less.


Any thoughts?


Thanks a lot
James
Reply With Quote
  #2  
Old 07-26-2021, 04:51 AM
gmayor's Avatar
gmayor gmayor is offline Record count on merge Windows 10 Record count on merge Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

There is no simple VBA function that will give you this information. A workaround method for a letters merge document would be to merge to a new document and count the sections e.g.
Code:
Sub CountRecipients()
Dim lngCount As Long, i As Long
Dim oDoc As Document
    If ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument Then
        MsgBox "The active document is not a merge document?", vbCritical
        Exit Sub
    End If
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    Set oDoc = ActiveDocument
    lngCount = oDoc.Sections.Count - 1
    oDoc.Close 0
    MsgBox lngCount
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 07-26-2021, 08:15 AM
JamesWood JamesWood is offline Record count on merge Windows 10 Record count on merge Office 2019
Advanced Beginner
Record count on merge
 
Join Date: Nov 2020
Posts: 37
JamesWood is on a distinguished road
Question

Thanks Graham! I feel like I could be onto something here, but have no idea what to use for my 'For i =' ?


Sub CountRecipients2() 'Accurate count of recipients even if some records are unticked
Dim i As Long 'For the loop
Dim oCount As Integer 'Counts the records
Dim oResult As String 'String to display in final count
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord 'Default start to first record
oCount = 1 'Count starts at 1
For i = ??? 'Loop begins
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord 'Move to next record
oCount = oCount + 1 'Add 1 to the total count
Next i
oResult = oCount
MsgBox (oResult)
End Sub
Reply With Quote
  #4  
Old 07-26-2021, 04:56 PM
Guessed's Avatar
Guessed Guessed is offline Record count on merge Windows 10 Record count on merge Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

This seems to work:
Code:
Sub listIncluded()
  Dim iLast As Long, iCount As Long
  With ActiveDocument.MailMerge.DataSource
    .ActiveRecord = wdLastRecord
    iLast = .ActiveRecord         '' Store index of the last active record for later use
    .ActiveRecord = wdFirstRecord
    Do
      iCount = iCount + 1
      Debug.Print .ActiveRecord
      DoEvents
      .ActiveRecord = wdNextRecord
    Loop Until .ActiveRecord = iLast
    MsgBox "There are " & iCount & " active records out of " & .RecordCount
  End With
End Sub
Full Disclosure: I didn't invent this code. I sourced it from here vba - How can get a list of included records in a Word Mail Merge Document? - Stack Overflow and made a small adjustment so I could understand it better.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #5  
Old 07-27-2021, 12:29 AM
JamesWood JamesWood is offline Record count on merge Windows 10 Record count on merge Office 2019
Advanced Beginner
Record count on merge
 
Join Date: Nov 2020
Posts: 37
JamesWood is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
This seems to work:
Code:
Sub listIncluded()
  Dim iLast As Long, iCount As Long
  With ActiveDocument.MailMerge.DataSource
    .ActiveRecord = wdLastRecord
    iLast = .ActiveRecord         '' Store index of the last active record for later use
    .ActiveRecord = wdFirstRecord
    Do
      iCount = iCount + 1
      Debug.Print .ActiveRecord
      DoEvents
      .ActiveRecord = wdNextRecord
    Loop Until .ActiveRecord = iLast
    MsgBox "There are " & iCount & " active records out of " & .RecordCount
  End With
End Sub
Full Disclosure: I didn't invent this code. I sourced it from here vba - How can get a list of included records in a Word Mail Merge Document? - Stack Overflow and made a small adjustment so I could understand it better.


This is very interesting! Thank you! Not completely fool proof (e.g. if you only select one record then it bugs out) but I think I could play with this code and get it to work. Thanks so much
Reply With Quote
Reply

Tags
mail, merge, record

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Record count on merge Letter from Mail Merge Redundancy Record zidan3311 Mail Merge 3 10-04-2017 12:54 PM
Record count on merge Showing record number during mail merge catflap Mail Merge 1 04-13-2017 07:32 AM
Record count on merge Mail Merge Next Record If rule RHensley Mail Merge 10 03-07-2017 08:05 AM
avoid duplicete record and merge the record with the existed record hemant.behere Excel 0 01-10-2012 02:53 AM
How to count the frequency of data and also tally value from an array of Excel record KIM SOLIS Excel 5 09-07-2011 09:01 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:30 AM.


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