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
Bookmarks