Adjust VBA to Include Action Count and Auto Stop

[jr0124]

Hello,

I have code that works fine for looking at how many rows have values and sending email for each one. What I need to do now is adjust it so that I can enter a start row, and a stop row and only have it process those. For example, may have content in 600 rows but only want to send emails for 56-90 right now.

Sub TESTI9EmailsforAllinI9NewFolksList()

' Don't forget to copy the function GetBoiler in the module.

' Working in Office 2000-2016

    Dim OutApp As Object

    Dim OutMail As Object

    Dim strbody As String

    Dim SigString As String

    Dim Signature As String

    Dim ws As Worksheet

    Dim Rw As Long

  

    strbody = ""

    'Change only Mysig.htm to the name of your signature

    SigString = Environ("appdata") & _

                "\Microsoft\Signatures\Signature.htm"

    If Dir(SigString) <> "" Then

        Signature = GetBoiler(SigString)

    Else

        Signature = ""

    End If

 

    On Error Resume Next

  

source_file = {ACTUAL FILE PATH}

 

    Set ws = Sheets("I9 New Folks")

 

 

    For Rw = 2 To Range("B" & Rows.Count).End(xlUp).Row

          

    

    Set OutApp = CreateObject("Outlook.Application")

    Set OutMail = OutApp.CreateItem(0)

With OutMail

 

    .To = Sheets("I9 New Folks").Range("D" & Rw).Text

    .CC = ""

    .Subject = Sheets("I9 New Folks").Range("B" & Rw).Text & " " & Sheets("I9 New Folks").Range("C" & Rw).Text & " (" & Sheets("I9 New Folks").Range("E" & Rw).Text & "), Your action is needed on your I-9 Form - SD: " & Sheets("I9 New Folks").Range("X" & Rw).Text

   .HTMLBody = Signature

    .Attachments.Add source_file

    .Display



    End With

 

    Next Rw

 

    On Error GoTo 0

    Set objAppt = Nothing

    Set objOL = Nothing

End Sub

 

[mumps]

See if this works for you. I have commented out the 'on error' lines of code because I wasn't sure what error you were testing for. It's always best to avoid using them if possible because they will ignore any error that might occur. It is a better approach to test for the the exact error that might occur. I have also moved the "Set OutApp" line of code outside the loop because it only has to be executed once.

Sub TESTI9EmailsforAllinI9NewFolksList()
' Don't forget to copy the function GetBoiler in the module.
' Working in Office 2000-2016
    Dim OutApp As Object, OutMail As Object, strbody As String, SigString As String, Signature As String, ws As Worksheet
    Dim Rw As Long, fRow As String, lRow As String
    fRow = InputBox("Enter the first row number.")
    If fRow = "" Then Exit Sub
    lRow = InputBox("Enter the last row number.")
    If lRow = "" Then Exit Sub
    Application.ScreenUpdating = False
    strbody = ""
    'Change only Mysig.htm to the name of your signature
    SigString = Environ("appdata") & "\Microsoft\Signatures\Signature.htm"
    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If
    'On Error Resume Next
    source_file = {ACTUAL FILE PATH}
    Set ws = Sheets("I9 New Folks")
    Set OutApp = CreateObject("Outlook.Application")
    For Rw = fRow To lRow
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = Sheets("I9 New Folks").Range("D" & Rw).Text
            .CC = ""
            .Subject = Sheets("I9 New Folks").Range("B" & Rw).Text & " " & Sheets("I9 New Folks").Range("C" & Rw).Text & " (" & Sheets("I9 New Folks").Range("E" & Rw).Text & "), Your action is needed on your I-9 Form - SD: " & Sheets("I9 New Folks").Range("X" & Rw).Text
            .HTMLBody = Signature
            .Attachments.Add source_file
            .Display
        End With
    Next Rw
    'On Error GoTo 0
    Set objAppt = Nothing
    Set objOL = Nothing
    Application.ScreenUpdating = True
End Sub

 

[jr0124]

Thank you! It was this guy that was getting me eventhough it was so easy!

For Rw = fRow To lRow

 

[mumps]

You are very welcome.