Date ranges in VBA

S

shaz0503

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
 
J

JLatham

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
 
S

shaz0503

JLatham - you are a legend - will play with this and will hopefully save many
hours of manually counting emails for a Business Unit.

Shaz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top