Excel Reminders

[Copan1795]

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.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

2

IG#

Date

no of count

total count per Rec

Supplier name

Supplier item number

Supplier 's BBD Use by date

Supplier 's lot numbers

Kosher Y/N

Country of Origin from container or COA

Email

3

2/5/2018

4

5

<tbody>
</tbody>

 

[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.