I am really close to having this process working. I am sending emails with attachments for a table on Sheet1 after I filter on column 12. The problem is
Does not find the last row after the filter is applied. It is finding the entire 1,004,000 rows in the spreadsheet. Can anyone offer a fix to the code.
Thanks
VBA Code:
For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
Thanks
VBA Code:
Sub create_multiple_emails()
Dim wb As Workbook, sh As Worksheet, c As Range, m As Range
Dim sBody As String, wFile As String
Dim dam As Object, dict As Object
Dim pCode As String
Dim fileN As String
Dim sOutTo As String
Dim sOutSub As String
Dim sOutInv As String
Dim sOutCont As String
Dim sOutDue As String
Dim sOutFNm As String
Dim answer As Integer
Dim lastRow As Long
pCode = InputBox("Enter Pull Code for mailing")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'
Set sh = ActiveSheet
Set dict = CreateObject("scripting.dictionary")
If sh.AutoFilterMode Then sh.AutoFilterMode = False
sh.Range("A1").AutoFilter Field:=12, Criteria1:=pCode
For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
sOutFNm = c.Value
sOutInv = c.Offset(, 2).Value
sOutTo = c.Offset(, 7).Value
sOutCont = c.Offset(, 9).Value
sOutDue = c.Offset(, 10).Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'On Error Resume Next
With OutMail
.To = sOutTo
.Subject = "AT&T Capital Services invoice #" & sOutInv & " Contract # " & sOutCont & " Due " & sOutDue
.HTMLBody = "<b>" & "INVOICE ATTACHED: Due " & sOutDue & "</b>" & "<br>" _
& "I have attached the AT&T Capital Services invoice #" & sOutInv & " " & ". Please confirm receipt." & "<br>" _
& " Let us know if there are any changes to the billing contact or mailing address on the invoice." & "<br>" _
& " I appreciate your help." & "<br>" _
& "<br>" _
& "<br>" _
& "Thank you" & "<br>" _
& MyName & "<br>" _
& "Capital Services, Inc."
.Attachments.Add sPath & "\" & sOutFNm
.ReadReceiptRequested = True
.OriginatorDeliveryReportRequested = True
.display
Range("P" & Rows.Count) = "Invoice sent to customer on " & Format(Now, "mm-dd-yy") & " " & MyName 'Environ$("username")
End With
Next
sh.ShowAllData
MsgBox "Emails sent"
End Sub