Great OK,
So, I've had a look at it and come back with this, will this work?
Option ExplicitConst strWorkbook As String = "path" 'The path of the workbook
Const strSheet As String = "Sheet1" 'The name of the worksheet
Sub TestMsg()
Dim olMsg As MailItem
On Error Resume Next
Set olMsg = ActiveExplorer.Selection.Item(1)
AutoReply olMsg
lbl_Exit:
Exit Sub
End Sub
Sub AutoReply(olMail As Outlook.MailItem)
Dim olReply As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Dim Arr() As Variant
Dim iCols As Long
Dim iRows As Long
Dim strName As String
'load the worksheet into an array
Arr = xlFillArray(strWorkbook, strSheet)
With olMail
For iRows = 0 To UBound(Arr, 2) 'Check each row of the array
'If column 2 (starting at column 0) contains the e-mail address of the message
If .SenderEmailAddress = Arr(2, iRows) Then
'If the subject value is in the message subject
If InStr(1, .Subject, Arr(1, iRows)) > 0 Then
If InStr(1, .ReceivedTime, Arr(1, iRows)) > 0 Then
'If the received time is in the message subject
If InStr(1, "Yes", Arr(1, iRows)) > 0 Then
'If The string above matches then mark the email as unread and move to 'Closed' folder
.UnRead = False
.Move
Session.Folders ("Closed")
Exit For
End If
Next oAtt
End If
End If
End If
Next iRows
End With
lbl_Exit:
Set olReply = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
Exit Sub
End Sub
Private Function xlFillArray(strWorkbook As String, _
strWorksheetName As String) As Variant
Dim RS As Object
Dim CN As Object
Dim iRows As Long
strWorksheetName = strWorksheetName & "$]"
Set CN = CreateObject("ADODB.Connection")
CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strWorksheetName, CN, 2, 1
With RS
.MoveLast
iRows = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(iRows)
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function
If not where am I going wrong?
I think if anything I'm not entirely sure what the below is pointing at (specifically the number parameters)
If InStr(1, .ReceivedTime, Arr(1, iRows))
Thanks again
John