Excel Reminders

[Copan1795]

[TABLE="width: 786"]
<tbody>[TR]
[TD="class: xl64, width: 832, bgcolor: transparent, colspan: 13"]The excel sheet I have has a I2 set to go red when it is past the BBD. I would like it to send a email when it goes red. [/TD]
[TD="width: 152, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A[/TD]
[TD="class: xl66, bgcolor: transparent"]B[/TD]
[TD="class: xl67, bgcolor: transparent"]C[/TD]
[TD="class: xl65, bgcolor: transparent"]D[/TD]
[TD="class: xl65, bgcolor: transparent"]E[/TD]
[TD="class: xl65, bgcolor: transparent"]F[/TD]
[TD="class: xl65, bgcolor: transparent"]G[/TD]
[TD="class: xl65, bgcolor: transparent"]H[/TD]
[TD="class: xl65, bgcolor: transparent"]I[/TD]
[TD="class: xl65, bgcolor: transparent"]J[/TD]
[TD="class: xl65, bgcolor: transparent"]K[/TD]
[TD="class: xl65, bgcolor: transparent"]L[/TD]
[TD="class: xl65, bgcolor: transparent"]M[/TD]
[TD="class: xl65, bgcolor: transparent"]N[/TD]
[TD="class: xl65, bgcolor: transparent"]O[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]IG#[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Date[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"]no of count[/TD]
[TD="class: xl71, width: 64, bgcolor: transparent"]total count per Rec[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Supplier name [/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"]Supplier item number[/TD]
[TD="class: xl71, width: 64, bgcolor: transparent"]Supplier 's BBD Use by date [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Supplier 's lot numbers[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"]Kosher Y/N[/TD]
[TD="class: xl71, width: 64, bgcolor: transparent"]Country of Origin from container or COA[/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 152, bgcolor: transparent"]Email [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: red"]2/5/2018[/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[arunsjain]

Hi,

You can use conditional formatting. In any cell of your spreadsheet include today's function "=Today()"

Then apply conditional formatting, if BDD is less than or equal to cell have " =Today() " then fill cell color RED. As today date passes to BDD date cell color will change to red.

Hope it will help.

Cheers!!!

 

[Copan1795]

Hi,

You can use conditional formatting. In any cell of your spreadsheet include today's function "=Today()"

Then apply conditional formatting, if BDD is less than or equal to cell have " =Today() " then fill cell color RED. As today date passes to BDD date cell color will change to red.

Hope it will help.

Cheers!!!

I already have the Conditional formatting done. I want a email reminder sent out based on the cell going red or being based on the being pasted the BBD.

 

[ScottWUK]

Hello Copan,
You can find lots of useful information on the Ron De Bruin site: https://www.rondebruin.nl/win/s1/outlook/mail.htm
I think you could probably adapt this code to make it work for you

[COLOR=#3366CC]Sub Test1()[/COLOR][COLOR=black]'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2016[/COLOR]
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "C").Value) = "yes" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = "Dear " & Cells(cell.Row, "A").Value _
                      & vbNewLine & vbNewLine & _
                        "Please contact us to discuss bringing " & _
                        "your account up to date"
                [COLOR=black]'You can add files also like this
                '.Attachments.Add ("C:\test.txt")[/COLOR]
                .Send [COLOR=black] 'Or use Display[/COLOR]
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True [COLOR=#3366CC]End Sub[/COLOR]

 

[Fluff]

@Copan1795

I removed your email address from your post, as Spam Bots routinely troll Public User Forums like these looking for email addresses to Spam.

 

[Copan1795]

I am still very new to VBA. I am I correct that Column A would be where the email address is and column B would be the date and Column C would signal Yes the date in column B is is passed due.