XfortunaX
New Member
- Joined
- Aug 28, 2014
- Messages
- 28
Hello,
I am using Excel 2010
The code below works flawlessly on the first time around:
For I = 2 To Range("F" & Rows.Count).End(xlUp).Row
.Attachments.Add Range("F" & I).Value
Next I
The second time around. It adds all of the rows from the first filter and the second, not just visible rows post auto filter. I am looking for the attached to only be what is shown after the filter is applied. As the code continues to run, the last email has 43 attachments (that is the total amount of rows unfiltered).
x = 2
'Loop through list one value at a time
'The array includes the title cell, so we start at the second value in the array
For Itm = 2 To UBound(MyArr)
ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=CStr(MyArr(Itm))
Set Rng = Range("A2", Range("A2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(Outmailitem)
AMEXDate = Sheets("Email").Range("I6").Value
EmployeeName = Rng(2, 1).Value
EmployeeEmail = Rng(2, 2).Value
EmailBody = Sheets("Email").Range("I3").Value
EmailBody = Replace(EmailBody, "Employeename", EmployeeName)
EmailBody = Replace(EmailBody, "AMEXDate", AMEXDate)
Subject = "AMEX Reconciliation Team Bills:" & " " & Sheets("Email").Range("I6").Value
On Error Resume Next
With olMail
.To = EmployeeEmail
.CC = ""
.BCC = ""
.Subject = Subject
.BodyFormat = olFormatHTML
.HTMLBody = EmailBody
.SendUsingAccount = olApp.Session.Accounts.Item(2)
For I = 2 To Range("F" & Rows.Count).End(xlUp).Row
.Attachments.Add Range("F" & I).Value
Next I
.Display 'or use .Display
End With
On Error GoTo 0
Set olMail = Nothing
Set olApp = Nothing
x = x + 1
Next Itm
Any information provided will be greatly appreciated.
Thanks,
Tuna
I am using Excel 2010
The code below works flawlessly on the first time around:
For I = 2 To Range("F" & Rows.Count).End(xlUp).Row
.Attachments.Add Range("F" & I).Value
Next I
The second time around. It adds all of the rows from the first filter and the second, not just visible rows post auto filter. I am looking for the attached to only be what is shown after the filter is applied. As the code continues to run, the last email has 43 attachments (that is the total amount of rows unfiltered).
x = 2
'Loop through list one value at a time
'The array includes the title cell, so we start at the second value in the array
For Itm = 2 To UBound(MyArr)
ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=CStr(MyArr(Itm))
Set Rng = Range("A2", Range("A2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(Outmailitem)
AMEXDate = Sheets("Email").Range("I6").Value
EmployeeName = Rng(2, 1).Value
EmployeeEmail = Rng(2, 2).Value
EmailBody = Sheets("Email").Range("I3").Value
EmailBody = Replace(EmailBody, "Employeename", EmployeeName)
EmailBody = Replace(EmailBody, "AMEXDate", AMEXDate)
Subject = "AMEX Reconciliation Team Bills:" & " " & Sheets("Email").Range("I6").Value
On Error Resume Next
With olMail
.To = EmployeeEmail
.CC = ""
.BCC = ""
.Subject = Subject
.BodyFormat = olFormatHTML
.HTMLBody = EmailBody
.SendUsingAccount = olApp.Session.Accounts.Item(2)
For I = 2 To Range("F" & Rows.Count).End(xlUp).Row
.Attachments.Add Range("F" & I).Value
Next I
.Display 'or use .Display
End With
On Error GoTo 0
Set olMail = Nothing
Set olApp = Nothing
x = x + 1
Next Itm
Any information provided will be greatly appreciated.
Thanks,
Tuna