Where to add loop and other........

[risingup]

Hi all. I currently have code set up to automaticlaly email me "upon opening the excel sheet" when a specific cell matches another cell with todays date. 2 questions. How and where do I insert a loop code to look at all dates in column D for example and not just a cell like D3. What is the code and do I place in the existing sub? And, how do I have the code grab cell text or numbers and automatically add to the email. For example, there are specific ticket numbers that belong to each email. If cell E1 has a ticket number of 22222, When the email gets sent, I'd like VB to automatically see and grab 22222 and insert what is in that cell into the email subject or message body. "Ticket number 22222 is due in 30 days" is an example of what the subject could say. I have the email auto inserting a line from this code.

.TextBody = Range("N1").Text

But, I need N1 to be dynamic and look at 22222 cell and insert into email sentence.


Currently, when todays date is put in L3, it will email if it matches D3. It looks at D3 to see if that D3 cell "todays date" matches. It will not work the other way around. I cant put in the date to D3 and have it look at L3 to send email. I have a private sub procedure set up for this and the below allows the forumla "=today" to insert date into L3 upon opening and work from the sub procedure by looking at D3 and checking for a match.

Private Sub Workbook_Open()
MsgBox Date

Range("L3").Value = Date




End Sub

 

[SkipVought]

hi,

allows the forumla "=today" to insert date into L3 upon opening ...

Private Sub Workbook_Open()
MsgBox Date

Range("L3").Value = Date




End Sub

Your statement is not consistent with your code.

Your VBA does NOT place the TODAY() formula in L3. Rather, it inserts today's date in L3.

If you want a procedure to check either way, if either D3 or L3 change, then use the Worksheet_Change event...

sub worksheet_change(byval target as range)
   if target.count>1 then exit sub
   if not intersect(target, union(range("D3"), range("L3"))) is nothing then
      if [D3] = Date and [L3] = Date then
        'houston, send the email!
      end if
   end if
end sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}