Macro to send email upon certain date trigger

Hi guys,

I think this is super complicated and i can't get it to work even a little bit...

i need excel to, upon opening a workbook, send an email if a condition is met, that condition being a date in a cell passing ONLY if adjacent cell is blank.

for example, in column A we have a list of dates, and if the date in A2 passes and B2 is blank, i need it to trigger an email sent to one fixed recipient, preferably with the title of the workbook within the body or title of the email.

Can someone help me?

If any more information is needed please ask!

EDIT* thinking about it, this doesn't seem like a macro is needed, perhaps an active function type thing? (forgive the lack of technical jargon)

hi, try the attachement and see if it heps you!
http://www.excelforum.com/excel-gene...coming-up.html

yeap, copy the whole code and paste it in Thisworkbook (have a look in the left side and click on Thisworkbook and then in the right side select Workbook
something like this...

Private Sub Workbook_Open()
Dim ce As Range, i As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
Dim MyName As String

    MyName = ThisWorkbook.Name

    For i = 2 To Sheets("Sheet1").Range("a65536").End(xlUp).Row
        If Cells(i, 1).Value <= Date And Cells(i, 2) = "" Then
            Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
        
            With Sheets("Sheet1")
                strto = .Cells(i, 3).Value 'column O
                strcc = "" '.Cells(i, 4).Value
                strbcc = ""
                strsub = MyName
                strbody = "Hi there," & "pls see" & " " & MyName 'vbNewLine & vbNewLine & _
                    .Cells(i, 2).Value & " " & _
                    "needs an updated" & " " & .Cells(i, 4).Value & " " & _
                    "because it expires on" & " " & .Cells(i, 11).Value & "." & _
                    vbCrLf & vbCrLf & "Thank you."
            End With
    
            With OutMail
                .To = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody
                '.Send
                .display
                Cells(i, 1).Select
     With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
            End With
    
            Set OutMail = Nothing
            Set OutApp = Nothing
        End If
  
    Next i
End Sub

replace

.display

with

.send

have a look at code and replace what is reffering to column A B,C
here 1 is for col A, 2 is for col B

'
If Cells(i, 1).Value <= Date And Cells(i, 2) = "" Then
'

here 3 is for col C (please ignor the text in red)

'
 strto = .Cells(i, 3).Value 'column O
'

and this one for yr date, I mean L as you said...

'
For i = 2 To Sheets("Sheet1").Range("a65536").End(xlUp).Row
'

k, I try it but tell me what are your columns used, for date, for empty cell, for email address!
edit:
hope these are yr colums

Dim ce As Range, i As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
Dim MyName As String

    MyName = ThisWorkbook.Name

    For i = 2 To Sheets("Sheet1").Range("l65536").End(xlUp).Row
        If Cells(i, 12).Value <= Date And Cells(i, 13) = "" Then
            Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
        
            With Sheets("Sheet1")
                strto = .Cells(i, 14).Value 'column O
                strcc = "" '.Cells(i, 4).Value
                strbcc = ""
                strsub = MyName
                strbody = "Hi there," & "pls see" & " " & MyName 'vbNewLine & vbNewLine & _
                    .Cells(i, 2).Value & " " & _
                    "needs an updated" & " " & .Cells(i, 4).Value & " " & _
                    "because it expires on" & " " & .Cells(i, 11).Value & "." & _
                    vbCrLf & vbCrLf & "Thank you."
            End With
    
            With OutMail
                .To = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody
                '.Send
                .display
                Cells(i, 12).Select
     With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
            End With
    
            Set OutMail = Nothing
            Set OutApp = Nothing
        End If
  
    Next i

if your column M is blank then

'
 If Cells(i, 12).Value <= Date Then
'

try this, C2 is yr cell having the email address

'
' With Sheets("Sheet1")
                strto =[c2]
                strcc = ""
                strbcc = ""
'

or

 With Sheets("Sheet1")
                strto ="kkk@yahoo.com"
                strcc = ""
                strbcc = ""
'

I am sorry, maybe someone else can help you!

Dim ce As Range, i As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
Dim MyName As String

    MyName = ThisWorkbook.Name

    
        If [l2] <= Date And [m2] = "" Then
            Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
        
            With Sheets("Sheet1")
                strto = "karl.cassidy@argos.co.uk"
                strcc = "" '.Cells(i, 4).Value
                strbcc = ""
                strsub = MyName
                strbody = "Hi there," & "pls see" & " " & MyName 'vbNewLine & vbNewLine & _
                    .Cells(i, 2).Value & " " & _
                    "needs an updated" & " " & .Cells(i, 4).Value & " " & _
                    "because it expires on" & " " & .Cells(i, 11).Value & "." & _
                    vbCrLf & vbCrLf & "Thank you."
            End With
    
            With OutMail
                .To = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody
                '.Send
                .display
               
    End With
           
    
            Set OutMail = Nothing
            Set OutApp = Nothing
        End If