This will read cell A1 for that date that you are after.
[vba]
' Need to Reference Microsoft ActiveX Data Object 2.x
Private Function GetExcelConnection(ByVal Path As String, _
Optional ByVal Headers As Boolean = True) As Connection
Dim strConn As String
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""Excel 8.0;HDR=" & _
IIf(Headers, "Yes", "No") & """"
objConn.Open strConn
Set GetExcelConnection = objConn
End Function
Sub test()
Dim objRS As ADODB.Recordset
Dim strConn As String
Dim objConn As ADODB.Connection
Dim SubjectDate As Date
'To read a sheet:
' strRequest = "SELECT * FROM Sheet1$"
'To refer to a range by its address:
' strRequest = "SELECT * FROM [Sheet1$A1
10]"
'To refer to a single-cell range, pretend it's a multi-cell range
'and specify both the top-left and bottom-right cells:
strRequest = "SELECT * FROM [Sheet1$A1:A1]"
'To read a named range:
' strRequest = "SELECT * FROM MyDataRange"
'To read a worksheet-level named range
' strRequest = "SELECT * FROM [Sheet1$MyData]"
Set objConn = GetExcelConnection("c:\test.xls", False)
'and then just open a recordset
Set objRS = objConn.Execute(strRequest)
objRS.MoveFirst
Do While Not objRS.EOF
SubjectDate = objRS.Fields(0).Value
MsgBox SubjectDate
objRS.MoveNext
Loop
Set objRS = Nothing
Set objConn = Nothing
End Sub
[/vba]
[vba]
msg.Subject = "Date - " & SubjectDate
[/vba]