[Zack Barresse]

Ah, missed a critical part - appending the previous entries to the string variable when setting. Here is the code (look at the Select Case statements and see how it sets the strMsg variable differently)...

Option Explicit

Const NL As String = vbNewLine
Const DNL As String = vbNewLine & vbNewLine
Const StrDateFormat As String = "m/d/yyyy"

Sub CheckEmailStatus()
    Dim OL As Object, olMail As Object, objwShell As Object
    Dim ws As Worksheet, c As Range, strMsg As String, strTemp As String, blnCreated As Boolean
    Set ws = ThisWorkbook.Sheets("Sheet1")
'    Set objwShell = CreateObject("wscript.shell")
    On Error Resume Next
    Set OL = GetObject(, "Outlook.Application")
    blnCreated = False
    If OL Is Nothing Then
        Set OL = CreateObject("Outlook.Application")
        blnCreated = True
    End If
'    objwShell.Run ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -activate")
    For Each c In ws.Range("AS10", ws.Cells(ws.Rows.Count, "AS").End(xlUp))
        strTemp = strTemp & "Tag #: " & ws.Cells(c.Row, "Q").Text & NL
        strTemp = strTemp & "Description: " & ws.Cells(c.Row, "R").Text & NL
        strTemp = strTemp & "Equipment Type: " & ws.Cells(c.Row, "T").Text & NL
        strTemp = strTemp & "Activity Code: " & ws.Cells(c.Row, "AP").Text & NL
        Select Case c.Value - DateValue("January 30, 2008") ' Date
        Case Is = 3    'next service date matches a day after today
            strMsg = strMsg & strTemp & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 2    'next service date matches a day after today
            strMsg = strMsg & strTemp & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 1    'next service date matches a day after today
            strMsg = strMsg & strTemp & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 0    'next service date matches today
            strMsg = strMsg & strTemp & "Next service date is today!" & DNL
        End Select
        strTemp = ""
    Next c
    If strMsg <> "" Then
        Set olMail = OL.CreateItem(0)
        olMail.To = "erikson.t@gmail.com"
        olMail.Subject = "Preservation Date"
        olMail.Body = strMsg
        ' olMail.Display 'for testing purposes only
        olMail.Send
    End If
'    objwShell.Run ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -suspend")
'    ' objwShell.Run ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -stop") 'optional to stop the service altogether
    If blnCreated = True Then OL.Quit
End Sub

I think that should get all of your values. Let us know how it works. Sorry it's taken me a bit to get back to you, been super busy here.

[sonson411]

Just got back from my vacation...
once again, great thanks, firefytr!!!

one little question about this line:
Select Case c.Value - DateValue("January 30, 2008") ' Date

is there a particular command to set "Today" date, instead of inputting the date everytime by myself??

[Zack Barresse]

Select Case c.Value - Date

Date is the VBA command for the current System date. If you want the time, use the Time command. If you want the current date *and* time, use Date + Time.

[BigDog12345]

I tried the sample .xls file and the code and when I click on the script I only get a screen flash. Nothing else happens. I did change the script for my username on where to find the .xls file on my desktop. Any ideas. The code I used was in #12.


Thanks

[slurpee55]

Try the code in #31

[BigDog12345]

I tried it and it did the same thing. Here are the files I'm using.

[slurpee55]

BigDog, since this is a rather old thread (and I haven't seen Zack around lately, but there are some other good coders out there - and I am not one), I would suggest you start a new thread - put a link to this one in it, but post the actual code you are using.