Date ranges in VBA
JLatham - you are a legend - will play with this and will hopefully save many
hours of manually counting emails for a Business Unit.
Shaz
"JLatham" wrote:
Try it this way. I broke up your Dim statement and added one to define the
EmailCount (don't use it if it was defined earlier). I changed MyDate to
MyFirstDate and also added variable MyLastDate. The test will test and count
dates that are within the range, inclusive of the first and last dates.
Dim iCount As Integer
Dim DateCount As Integer
Dim EmailCount As Long ' if not defined earlier
Dim myFirstDate As Date
Dim myLastDate As Date
EmailCount = objFolder.Items.Count
DateCount = 0
myFirstDate = Sheets("Sheet1").Range("A1").Value
myLastDate = Sheets("Sheet1").Range("A2").Value
For iCount = 1 To EmailCount
With objFolder.Items(iCount)
If DateSerial(Year(.ReceivedTime), _
Month(.ReceivedTime), _
Day(.ReceivedTime)) = myFirstDate And _
DateSerial(Year(.ReceivedTime), _
Month(.ReceivedTime), _
Day(.ReceivedTime)) <= myLastDate Then
DateCount = DateCount + 1
End If
End With
Next iCount
"shaz0503" wrote:
All
Have borrowed some really useful code for calculating the number of emails
in a specific mail folder...
However, the code uses a single date - I would like to use a range
identified in two cells eg Date one - Sheet 1 A1 and Date two Sheet 1 A2
Below is the code and I am at a loss as to how to update this
Dim iCount As Integer, DateCount As Integer
Dim myDate As Date
EmailCount = objFolder.Items.Count
DateCount = 0
myDate = Sheets("Sheet1").Range("A1").Value
For iCount = 1 To EmailCount
With objFolder.Items(iCount)
If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime),
Day(.ReceivedTime)) = myDate Then DateCount = DateCount + 1
End With
Next iCount
any assistance would be greatly appreciated
Shaz
|