For without Next Error on Email Macro

Question

Hello I have this code here returning an error "For without Next" and it highlights the end sub all the way at the bottom.

Why is this happening? I'm just trying to insert the value of certain cells in the body of my email.

Can someone please help with this?

Sub Notify()
    Dim name, email, evnt, status As Range
    Dim SigString As String
    Dim Signature As String

        SigString = Environ("appdata") & _
                "\Microsoft\Signatures\Will.txt"

    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If

    For Each name In Range(("B2"), Range("B2").End(xlDown))
    For Each email In Range(("C2"), Range("C2").End(xlDown))
    For Each evnt In Range(("E2"), Range("E2").End(xlDown))
    For Each status In Range(("F2"), Range("F2").End(xlDown))

    On Error Resume Next

        With CreateObject("Outlook.Application").CreateItem(0)
            .To = email.Value
            .CC = email.Offset(0, 1).Value
            .Subject = "Information You Requested"
            .Body = "Dear " & name.Value & "," & vbNewLine & vbNewLine & _
                "As you may know the 2014 race started on November 4th and will be open until November 15th in order for you to select your candidate." & vbNewLine & vbNewLine & _
                "Your 2014 election is now On Hold since you have a previous " & evnt.Value & "event with a " & status.Value & "status in Workday. " & vbNewLine & vbNewLine & _
                "In order for you to be able to finalize your Elections you need to finalize the event above as soon as possible." & vbNewLine & vbNewLine & _
                "If you have any questions about this task please call us at 1-877-777-7777 option 8" & vbNewLine & vbNewLine & _
                "Regards" & vbNewLine & vbNewLine & _
                "Department"
            .Display
            '.Attachments.Add "C:\test.txt"
            '.Send
        End With
    Next
End Sub

Function GetBoiler(ByVal sFile As String) As String
    'Dick Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Use [this](http://www.oaltd.co.uk/Indenter/Default.htm) for code indenting - it will show you the levels and make matching if/endif and other like constructs easier. It works in Office 2007 and 2010, but the website does not show those — SeanC, Nov 06 '13 at 19:06

score 3 · Answer 1 · answered Nov 06 '13 at 18:45

3

You have 4 For Each but just one Next

You need to have another 3 Next. See this.

Sub Notify()
    '
    '~~> Rest of the code
    '
    For Each Name In Range(("B2"), Range("B2").End(xlDown))
        For Each Email In Range(("C2"), Range("C2").End(xlDown))
            For Each evnt In Range(("E2"), Range("E2").End(xlDown))
                For Each Status In Range(("F2"), Range("F2").End(xlDown))
                    '
                    '~~> Rest of the code
                    '
                Next
            Next
        Next
    Next
End Sub

answered Nov 06 '13 at 18:45

Siddharth Rout

147,039
17
206
250

Hey thanks!!! Now I don't get the error but the macro just keeps cycling through the names and it wont stop... – William Nov 06 '13 at 19:40
It will stop :) It's just that you are looping through so many times. I would also discourage you to use `xlDown` to find the lastrow. You might want to see [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) – Siddharth Rout Nov 06 '13 at 19:43
Would I insert that code in the beginning? What would my range be? – William Nov 06 '13 at 19:51
`Range(("B2"), Range("B2").End(xlDown))` will change into something like `Range("B2:B" & Lrow)` If you have large number of rows then you might want to consider using arrays? – Siddharth Rout Nov 06 '13 at 19:54
Sorry for the delay in response, but I still cant get it to work properly. I will just have to keep trying to adjust the code until I get it to work... – William Nov 06 '13 at 20:38
yeah, give it a try and if you get stuck post the code as a new question and we will take it from there :) – Siddharth Rout Nov 06 '13 at 20:42

For without Next Error on Email Macro

1 Answers1