Finding values entered in a column TODAY by the same user

• I have column A fully populated with values
• I have a macro that when clicked, opens outlook express and sends an email to a vertical head with the latest entry # in column A.
• No issues at all. Works fine. But I want to upgrade it
• Is there a way to find out the values populated by a given user, in a particular day, in this case, today ?

For eg:

• If XYZ populated, Range A7,A8 and A9 yesterday.
• Today, ABC populated Range 10,11 , 12 and 13:
• Then the code today, should send an email to the vertical head saying , Range10,11,12 and 13 were updated today.

My code uses the following :

For finding last value in row :

Range("A65536").End(xlUp).Value

Code that sends the mail

Set oout = CreateObject("Outlook.Application")
Set omsg = oout.CreateItem(0)
Dim ireply As String
Dim LastRow As Range
Dim SigString As String
Dim Signature As String
ireply = MsgBox(Prompt:="Is your OUTLOOK open ?", _
Buttons:=vbYesNoCancel, Title:="UPDATE MACRO")

If ireply = vbYes Then
'LastRow = Cells(Rows.Count, "A").End(xlUp).row.Value

With omsg
.to = "edf@gmail.com"
.CC = "elf@gmail.com "
.BCC = ""
.Subject = "Customer Service - Request ID # " & Range("A65536").End(xlUp).Value & "  added"
.Body = "Dear edf," & vbNewLine & vbNewLine & " Customer Service Department just added  Request ID #  " & Range("A65536").End(xlUp).Value & "  to the list" & vbNewLine & "Due Date is :  " & Range("L65536").End(xlUp).Value & vbNewLine & vbNewLine & vbNewLine & "Thanks and Regards"
.Display (True)

End With
ElseIf ireply = vbNo Then
MsgBox ("Alright ! I will open that for ya ")
Shell ("OUTLOOK")
With omsg
.to = "edf@gmail.com.com"
.CC = "elf@gmail.com.com"
.BCC = ""
.Subject = "Customer Service - Request ID # " & Range("A65536").End(xlUp).Value & "  added"

.Body = "Dear edf," & vbNewLine & vbNewLine & " Customer Service Department just added  Request ID #  " & Range("A65536").End(xlUp).Value & "  to the list" & vbNewLine & "Due Date is :  " & Range("L65536").End(xlUp).Value & vbNewLine & vbNewLine & vbNewLine & "Thanks and Regards"
.Display (True)

End With
ElseIf ireply = vbCancel Then
Exit Sub
End If
                    '  If omsg.Sent Then   ' // HAVING ISSUES WITH THIS AS ON 10/30/2013...
                    '      MsgBox ("Sent")
                    '  Else
                    '     MsgBox (" Not Sent ! ")
                    '  End If
Set oout = Nothing
Set omsg = Nothing
                    'Exit Sub
                    '

I am assuming that your data starts from row 2.

use this code to get the range that was updated today.

'code by xlbiznes
Dim updated_range As String
For x = 2 To Range("a" & Rows.Count).End(xlUp).Row

     If CDate(Range("A" & x).Text) = Date Then
     
     updated_range = updated_range & Range("a" & x) & ","
     End If
Next


'to remove the last ,
If update_range <> "" Then
updated_range = Left(updated_range, Len(updated_range) - 1)
End If

MsgBox updated_range

this code will go below this

If ireply = vbYes Then
'LastRow = Cells(Rows.Count, "A").End(xlUp).row.Value

and this line

.Subject = "Customer Service - Request ID # " & Range("A65536").End(xlUp).Value & "  added"

will change to

.Subject = "Customer Service - Request ID # " & updated_range & "  added"

It says VARIABLE NOT DEFINED, pointing at 'x'

Would I have to add,

Dim x as range

Its not needed,

try adding this code.

dim x as long

oops,it should be updated_Range not update_range. that was a typo

if you can post your updated code.

I can simulate the issue.

at which line is this error occurring ?

On which line did you get the error ?

This code went through without any issues.

I am assuming that column "A" is a date field, if not which column has the date values to be compared to current date ?

Oopss.. My bad.. it was column B. Column A was full of request ID # (all integers)


Having said that, just an addition to my initial query.

In the subject it populates the dates... That is not wat should go there. The request ID is what has to go there...

So how to choose the request IDs (from column A) that were filled today.

Would it be

updated_range.offset(0,-1) = updated_range & Range("B" & x) & ","

Ok,

your date values are in column B and we need to extract the content in column a for rows that were updated today.

So change this code :

For x = 2 To Range("a" & Rows.Count).End(xlUp).row

     If CDate(Range("a" & x).Text) = Date Then
     
     updated_range = updated_range & Range("a" & x) & ","
     End If
Next

to

For x = 2 To Range("a" & Rows.Count).End(xlUp).row

     If CDate(Range("b" & x).Text) = Date Then
     
     updated_range = updated_range & Range("a" & x) & ","

     End If
Next

You are welcome, glad that we got it solved.

Can you please explain what this line in the code does?

updated_range = updated_range & Range("a" & x) & ","

I mean, I am looking for a way if I could declare and get corresponding values from column L as well ?

We are using a string variable named updated_range to store the values in the column A in the row X .

Before this you have a if condition that checks that the date in row x of column "B" is equal to current date.
So only on satisfying this condition the updated_Range string gets appended with the value in column "A".

This happens till the last row is reached.

At the end we have all your ID's in the string variable updated_range.

Current you have this code for the Request ID to be collected

For x = 2 To Range("a" & Rows.Count).End(xlUp).row

     If CDate(Range("b" & x).Text) = Date Then
     
     updated_range = updated_range & Range("a" & x) & ","

     End If
Next

To also get the due date from the column L replace the above code with this.

dim str_due_date as string

For x = 2 To Range("a" & Rows.Count).End(xlUp).row

     If CDate(Range("b" & x).Text) = Date Then
     
     updated_range = updated_range & Range("a" & x) & ","
     str_due_date = str_due_Date &  range("L" & x).text & ","  'check if the date is extracted in the same format as it is in the sheet
     End If
Next