+ Reply to Thread
Results 1 to 3 of 3

Loop is not working

Hybrid View

KColgrove Loop is not working 02-22-2020, 01:34 PM
KColgrove Re: Loop is not working 02-22-2020, 02:17 PM
AliGW Re: Loop is not working 02-22-2020, 02:17 PM
  1. #1
    Registered User
    Join Date
    04-12-2019
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    64

    Loop is not working

    Hello,

    I am trying to make a loop that will loop through a table and if the task hasn't been completed it will send out an email (I'm just testing it right now so I just want to display the email). But when I run the code it only displays 1 of the emails, when it should be displaying at least 4 right now. When I run the code line by line doing f8 the code works perfectly. Is there something I need to add that will slow it down or make sure that it runs through the full loop? I'm not 100% sure I'm using GoTo correctly.


    Sub AutomatedEmail()
    Dim WDay As Integer, w As Integer
    Dim e As String, i As String, c As String, f As String
    Dim OutApp As Object
    Dim OutMail As Object
    Subject As String, Body As String, BodyCon As String
    Dim Row As Long
    Dim Tot As String
    Dim Answer As String
    Dim MyNote As String
    
    Subject = Sheets("Email Outline").Range("D10")
    Body = Sheets("Email Outline").Range("D12")
    BodyCon = Sheets("Email Outline").Range("D14")
    WDay = Sheets("Email Outline").Range("D8")
    Tot = Sheets("Email Outline").Range("O9")
    Row = Cells(Rows.Count, 2).End(xlUp).Row
    w = 4
    
        MyNote = "Do you want to send email reminders out?"
        Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Email Reminder?")
        
    If Answer = vbNo Then
        Exit Sub
    Else
        GoTo Start
    End If
    
    Start:
    Sheets("PreHeat").Activate
    Do Until w >= Row
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    i = Range("I" & w)
    e = Range("E" & w)
    c = Range("H" & w)
    f = Range("F" & w)
    
    If i = "Y" Then
        GoTo ESub
    Else
        If e >= WDay Then
            GoTo ESub
        Else
             With OutMail
                .To = c
                .CC = ""
                .BCC = ""
                .Subject = Subject
                .HTMLBody = Body & " " & f & " " & BodyCon
                .Display
            End With
        End If
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
        
    Set OutMail = Nothing
    Set OutApp = Nothing
    
          End If
    End If
    
    GoTo ESub
    
    ESub:
        w = w + 1
        Loop
    
    End Sub

  2. #2
    Registered User
    Join Date
    04-12-2019
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    64

    Re: Loop is not working

    I figured it out. I needed to move sheets before I counted the rows. Was counting the wrong sheet...

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,266

    Re: Loop is not working

    Thanks for letting us know.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Next loop is not working
    By Aatomo72 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2019, 01:57 PM
  2. For Next Loop not Working
    By basmster in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-01-2016, 01:11 PM
  3. [SOLVED] Do Until Loop Not Working
    By jjl287 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-30-2015, 04:49 PM
  4. For loop within loop not working correctly
    By HalPlz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-25-2015, 12:06 AM
  5. Vba Loop not working
    By dahveedoff in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-27-2014, 03:57 PM
  6. Loop not working...
    By yunesm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2011, 08:12 AM
  7. Loop not working
    By blankus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2009, 04:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1