Loop to verify if action is needed

R

rogerisin

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

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Your "If" satement is case sensitive, ie "closed" does not equal "CLOSED".
Try: If UCase(Cells(I, 2)) = "CLOSED" Then
 
Upvote 0
Warship said:
Your "If" satement is case sensitive, ie "closed" does not equal "CLOSED".
Try: If UCase(Cells(I, 2)) = "CLOSED" Then
Click to expand...

Thanks for your help, Warship. I copied the above code but the macro still displayed emails of "CLOSED" line items.
 
Upvote 0
You are only checking one cell for 'CLOSED'.

If it is 'CLOSED' then you jump into the loop at the point labelled 'den', if it isn't the code enters the loop anyway.

Is that what you want to do?
 
Upvote 0
Get rid of the Goto and the Else.
Try:
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
   'GoTo den
  'Else
Do
    If UCase(Cells(I, 2)) <> "CLOSED" Then
           
         '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
    End If
    
    I = I + 1
    Cells(1, "H").Value = "Outlook msg  count  =" & I - 1
    
Loop Until Cells(I, "G").Value = ""
'
End Sub
 
Upvote 0
Warship

And most importantly move the If inside the loop, as you've done.:)
 
Last edited:
Upvote 0
Warship said:
Get rid of the Goto and the Else.
Try:
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
   'GoTo den
  'Else
Do
    If UCase(Cells(I, 2)) <> "CLOSED" Then
           
         '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
    End If
    
    I = I + 1
    Cells(1, "H").Value = "Outlook msg  count  =" & I - 1
    
Loop Until Cells(I, "G").Value = ""
'
End Sub
Click to expand...

Thanks Warship and Norie! Woohoo!
 
Upvote 0
You must log in or register to reply here.

Similar threads

The Ruff Report
  • Question
Help adjusting Macro to email multiple cc addresses
Replies
1
Views
378
The Ruff Report
The Ruff Report
The Ruff Report
  • Question
Add multiple attachments from email list
Replies
1
Views
278
Trebor76
T
C
  • Solved
Adding an editable pop-up
Replies
3
Views
277
ChaosPup
C
A
  • Solved
Sending hyperlink filepath via email
Replies
8
Views
350
AndyEd
A
C
  • Solved
Adding a Log from user entered comments
Replies
3
Views
217
Tupe77
T

Forum statistics

Threads
1,214,902
Messages
6,122,161
Members
449,069
Latest member
msilva74

Share this page

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
Back
Top