Help Needed to Solve if/then loop to verify if (further) action is needed

[rogerisin]

Hello and thank you to those who replied to my previous posted query. It seems when I tried posting a thank you, an error message from this site kept stating something to the effect I needed another character to add which was an error in itself.

Anyway, I am basically done with a program which looks for one condition and displays an email to send as a reminder.

What I need is to have an if/then statement which checks a column in the spreadsheet and if the cell in the column states "closed" then an email is NOT displayed/sent. It seems whenever I run the macro, an email appears even if the cell for that row reads "closed."

My if/then statement in question lies following my note " 'here lies my current dilemma to solve"
Thank you in advance!

Sub ThreeDayEmailTest()
'
' ThreeDayEmailTest Macro
'
I = 2
          
     ' selects row 2 ,since row 1 ,i am keeping for titles
Dim den As String
'here lies my current dilemma to solve
If Cells(I, 2) = "CLOSED" Then
   GoTo den
  Else
   Do
           
         'MyFile = Cells(I, 10).Value
         'Subj = Cells(I, 9).Value
        EmailTo = Cells(I, 5).Value
         ' selects column E starting from E2 ,i.e.column E row 2
     ' and this will loop until you have values in E column
         'CCto = Cells(I, 12).Value
         'User = Cells(I, 11).Value
         'msg = Cells(I, 13).Value
         
         Application.DisplayAlerts = False
         
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
strbody = "Good Morning " + Cells(I, 3).Value + "," _
                & vbNewLine _
                & vbNewLine _
                & "This is just a reminder to update or contact you to verify if the issue regarding " _
                & vbNewLine & vbNewLine & Cells(I, 3).Value + "pertaining to " + Cells(I, 9).Value _
                & " is closed and satisfied."
Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
        With OutMail
            .To = EmailTo
             ' .CC = CCto
             '.BCC = ""
             .Subject = "3 Day Reminder"
             .body = strbody
             '.Attachments.Add ActiveWorkbook.FullName
            .Display
        End With
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
        Application.DisplayAlerts = False
         'Workbooks(MyFile).Close
        Application.DisplayAlerts = True
den:
         I = I + 1
          
        Cells(1, "H").Value = "Outlook msg  count  =" & I - 1
         
    Loop Until Cells(I, "G").Value = ""
'
End If
End Sub

 

[gsistek]

I think all you need to do is move the "Do" line to before the IF statement. As it is, the loop skips the if statement.

 

[texasalynn]

your loop does not have that if statement included, so it checks it for the first row (2) then never checks it for any of the other rows. Try moving that line after the "DO" statement

 

[rogerisin]

I think all you need to do is move the "Do" line to before the IF statement. As it is, the loop skips the if statement.

Thanks gsistek but after doing that and deleting the last loop line of the macro, the compile error is stating "Loop without Do" which is doing my head in.

Deleting the Loop statement at the bottom now gives me a "Do without Loop statement."

I feel I'm close but it's still driving me bananas.

 

[rogerisin]

Thanks texasalynn,

Much like with gsistek, I followed your advice but received a compiling error stating "Loop without Do" and when I deleted the Loop statement at the bottom there's a compiling error which reads "Do without Loop statement."

I'm trying to appease the error messages while trying other things as this is my final stretch before success and of course it's causing grey hair.

-Roger

 

[texasalynn]

you this

If Cells(I, 2) = "CLOSED" Then
GoTo den
Else
Do

to be

Do
If InStr(LCase(Cells(i, 2)), "closed") = 0 Then
   GoTo den
  Else

note: I changed it a little by adding the LCase to help avoid errors with case sensitive data

 

[Norie]

The answer is to move the Do but you shouldn't be removing anything else, especially the last line of the loop.


Oops, almost forgot this is a duplicate http://www.mrexcel.com/forum/showthread.php?647333-Loop-to-verify-if-action-is-needed.

 

[xenou]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).