VBA Autofilter same sheet twice / Email results

[johnkrenkel]

I am working on sending an email report using autofilter ranges. This code works great if only sending the rngNIM but if i try to add rngError, It breaks. I am guessing it has something with storing copied cells in the same filtered worksheet. Is there a way to do this?




VBA Code:

With wsNIM
.AutoFilterMode = False
With rngNIM
.AutoFilter Field:=8, Criteria1:="<>"
.SpecialCells(xlCellTypeVisible).Copy
End With
End With

With wsNIM
.AutoFilterMode = False
With rngError
.AutoFilter Field:=7, Criteria1:="<>"
.SpecialCells(xlCellTypeVisible).Copy
End With
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
'.To = ""
.CC = "[EMAIL]me@me.com[/EMAIL]"
'.BCC = ""
.Subject = "subjectlinehere"
.HTMLBody = RangetoHTML(PCReconcilement) & vbNewLine & "Not in Master Record" & vbNewLine & RangetoHTML(rngNIM) & "Processing Error" & vbNewLine & RangetoHTML(rngError)
'You can add a file like this
'.Attachments.Add
.Send 'or use .Display
End With
On Error GoTo 0

How do I autofilter a second time and add to the body? FYI: RangeToHTML is a function

 

[zweifuss]

If you need it as a range, PasteSpecial it somwhere else and use that. Otherwise copy the values in an array and loop over the values. Unfortunately VBA.Join() doesn't help here.

' Do the AutoFilter first.
Dim arrNIM() As Variant
arrNIM = rngNIM.SpecialCells(xlCellTypeVisible).Value2

and so on...

 

[johnkrenkel]

If you need it as a range, PasteSpecial it somwhere else and use that. Otherwise copy the values in an array and loop over the values. Unfortunately VBA.Join() doesn't help here.

' Do the AutoFilter first.
Dim arrNIM() As Variant
arrNIM = rngNIM.SpecialCells(xlCellTypeVisible).Value2

and so on...

Thank you, I was just looking into Arrays so adding something new to my growing skills