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!
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