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

Status
Not open for further replies.

rogerisin

New Member
Joined
Jul 11, 2012
Messages
11
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!
Code:
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

gsistek

Well-known Member
Joined
Apr 4, 2011
Messages
660
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

New Member
Joined
Jul 11, 2012
Messages
11
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

New Member
Joined
Jul 11, 2012
Messages
11

ADVERTISEMENT

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

Well-known Member
Joined
May 19, 2002
Messages
8,458
you this
Code:
If Cells(I, 2) = "CLOSED" Then
GoTo den
Else
Do
to be

Code:
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
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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).
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,122,482
Messages
5,596,402
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top