Mail Merge Loop Stops With the Fourth Communication

[danders]

Hello:

I have an excel file that I need to send different parts of to different recipients. I'm using two sub procedures that filter an excel worksheet by the email address in column X, copies the related rows and pastes them into a temporary excel workbook, attaches the temporary workbook to an email along with the body of the email which is written in HTML, sends the communication, and then repeats all of these steps through a loop until it reaches the last email in the column/range. The issue I am experiencing is the loop only works through the fourth email, then it stops. There are over 100 emails in the range, so it should continue the loop through all of these, but it does not.

 

[jorismoerings]

Hi,

Just a simple question: did you check what the total number of items is loaded in aNames at the first run?

If not: insert in this on a new line in the 1 sub procedure:

Debug.Print Ubound(aNames)

It needs to go after the line starting with

aNames = Offset(1)

and before:

For Each Itm in aNames

If you run the code it will print the number of entries which is loaded into aNames in the immediate window. Check if that number is aligned with the manual count of the number of names selected by the autofilter.

 

[danders]

Hi,

Just a simple question: did you check what the total number of items is loaded in aNames at the first run?

If not: insert in this on a new line in the 1 sub procedure:

Debug.Print Ubound(aNames)

It needs to go after the line starting with

aNames = Offset(1)

and before:

For Each Itm in aNames

If you run the code it will print the number of entries which is loaded into aNames in the immediate window. Check if that number is aligned with the manual count of the number of names selected by the autofilter.

Hello:

Thank you. The Immediate Window shows the number of entries into aNames is 5. How do I correct this so that the loop runs until it has filtered through the entire worksheet?

 

[jorismoerings]

The most probable cause of this problem is the use of autofilter in combination with specialcells.

The SpecialCells returns a range of only those cells that are visible, however this method doesn’t work with noncontiguous ranges.
I had that problem once and solved it by changing the autofilter to data advanced filter, I filtered and send the result to a new range on the sheet. By doing that is solves the noncontiguous problem of ranges.
I loaded that new range into the array and then it worked.

hope this helps.

 

[danders]

The most probable cause of this problem is the use of autofilter in combination with specialcells.

The SpecialCells returns a range of only those cells that are visible, however this method doesn’t work with noncontiguous ranges.
I had that problem once and solved it by changing the autofilter to data advanced filter, I filtered and send the result to a new range on the sheet. By doing that is solves the noncontiguous problem of ranges.
I loaded that new range into the array and then it worked.

hope this helps.

Thank you. I am actually using AdvancedFilter. See the code below:

Sub LoopEachLiaisonsName()
'Filters the Current Data by Controller.

Dim aNames As Variant, Itm As Variant
Windows("Pending Actions Template.xlsm").Activate 'Selects the "Pending Actions Template.xlsm".
Sheets("Current Data").Select 'Selects the "Current Data" worksheet
With Range("AA1", Range("AA" & Rows.Count).End(xlUp))
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
aNames = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value
Debug.Print UBound(aNames)
For Each Itm In aNames
.AutoFilter Field:=1, Criteria1:=Itm

'Copies filter data.
Sheets("Current Data").Select
Range("A1:AJ20000").Select
Selection.Copy

Windows("Pending Action-.xlsx").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1:AJ1").Select
Selection.AutoFilter

Call Mail_Liaisons_ActiveSheet ' Sends a copy of the filtered worksheet in an email to each Controller.
Workbooks.Open (ThisWorkbook.path & "\Pending Action-.xlsx")
Range("A1:AJ1").Select
Selection.AutoFilter
Cells.Select
Range("A37").Activate
Selection.ClearContents
Windows("Pending Actions Template.xlsm").Activate
Next Itm
.AutoFilter
End With
End Sub

 

[jorismoerings]

Ok, don’t use filter in place but let the advanced filter copy it to a unused part of the sheet, load it into the array and go on from there.

 

[jorismoerings]

just assuming you're not using the all columns of the Current Data worksheet.

try changing this:

With Range("AA1", Range("AA" & Rows.Count).End(xlUp))
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
aNames = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value

to this:

    With Range("AA1", Range("AA" & Rows.Count).End(xlUp))
        .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "XFD1"), Unique:=True
   `     With Range("XFD2", Range("XFD" & Rows.Count).End(xlUp))
                      aNames = .Value
                     .EntireColumn.Delete
          End With

and continue with the rest of your code

 

[danders]

just assuming you're not using the all columns of the Current Data worksheet.

try changing this:

With Range("AA1", Range("AA" & Rows.Count).End(xlUp))
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
aNames = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value

to this:

    With Range("AA1", Range("AA" & Rows.Count).End(xlUp))
        .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "XFD1"), Unique:=True
   `     With Range("XFD2", Range("XFD" & Rows.Count).End(xlUp))
                      aNames = .Value
                     .EntireColumn.Delete
          End With

and continue with the rest of your code

This worked perfectly. I've just run the macros, and there were no issues. Thank you for assisting me with this.

 

[jorismoerings]

No problem, glad I could help. We got there in the end. Thanks for the feedback.