trying to search excel from Outlook... NO Results

  • Thread starter Thread starter Phillips
  • Start date Start date
P

Phillips

I am trying to find a phone number that is located in an excel spreadsheet.
The phone number is the last 10 digets in the subject of an email. The phone
should be found in workbook "Current" and the worksheet "Master"

The format of the phone is 1-(xxx) xxx-xxxx in the excel spreadsheet, so it
should be being found, but it is not...
I am calling this from a script being ran by RULES in outlook when a given
string is found. That part is working, but I can not getting anything after
the MsgBox "Mail message arrived: " & phn
If I move the above line in to the if found (even in the else!) I get
nothing...
TIA,
Phil
-------------------------
Sub Phonetest(Item As Outlook.MailItem)
Dim phn As String, phn1 As String, phn2 As String, phn3 As String, phn4 As
String
Dim xlApp As Excel.Application

phn1 = Right(Item.Subject, 10)
phn2 = Left(phn1, 3)
phn3 = Mid(phn1, 4, 3)
phn4 = Right(phn1, 4)
phn = "1-(" & phn2 & ") " & phn3 & "-" & phn4
MsgBox "Mail message arrived: " & phn

Set c = xlApp.Cells.Find(What:=phn, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox "found" & Excel.ActiveCell.Offset(0, -2)
MsgBox "Mail message arrived: " & phn
'does not pop up
Else
MsgBox "not found"
MsgBox "Mail message arrived: " & phn
'does not pop up
' I would expect THIS or the one
above, but it does not....

End If

End Sub
 
You don't ever initialize xlApp. You probably need a line:

set xlApp = Getobject(, "Excel.Application")


if you have error handling, perhaps that is suppressing the error.
 
I do not have any error handling...
I will try the additional line.
I currently have had excel open already.

Thanks
Phil
 
Tom, here is my current code. I am now getting a message asking if I want to
reopen the file....
Before I put the code in to test for the file and open it, I was trapping
the errors and it was

error # 91 was generated by project1
object varible or with block varible not set

so I put in the portion to test for and open the file if nessasarry.
I am hoping to have this run in the background if I am using excel. Any
suggestions on that?
I am using Office XL if that makes any difference.

'*******************************************

Sub Phonetest(Item As Outlook.MailItem)

Dim phn As String, phn1 As String, phn2 As String, phn3 As String, phn4 As
String
Dim xlApp As Excel.Application
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim blnExcelWasRunning As Boolean
Dim blnFileWasOpen As Boolean
Set xlApp = GetObject(, "Excel.Application")

On Error GoTo errormsg 'msgbox error

phn1 = Right(Item.Subject, 10)
phn2 = Left(phn1, 3)
phn3 = Mid(phn1, 4, 3)
phn4 = Right(phn1, 4)
phn = "1-(" & phn2 & ") " & phn3 & "-" & phn4
MsgBox "Mail message arrived: " & phn

'trying to search......
' test for file, and open if it is not open.
strFileName = "C:\excel\current.xls" 'file to search

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
Err.Clear
blnExcelWasRunning = False
Set objExcel = GetObject("", "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
MsgBox "Error: You must have Excel installed on your" & vbCrLf & _
"computer to use this function."
Set objExcel = Nothing
Exit Sub
End If
Else
blnExcelWasRunning = True
End If

' Test to see if the file is already open. If it is, make it the
' active workbook.

Set objWorkbook = objExcel.Workbooks(objExcel.GetFileName(strFileName))
If Err.Number <> 0 Then
Err.Clear
blnFileWasOpen = False
Set objWorkbook = objExcel.Workbooks.Open(strFileName)
If Err.Number <> 0 Then
Err.Clear
MsgBox "Error: Could not open file. " & strFileName
Exit Sub
End If
objWorkbook.Activate
Else
Err.Clear
blnFileWasOpen = True
objWorkbook.Activate
End If
On Error GoTo 0

Set c = xlApp.Cells.Find(What:=phn, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox "found" & Excel.ActiveCell.Offset(0, -2)
MsgBox "Mail message arrived: " & phn
Else
MsgBox "not found"
MsgBox "Mail message arrived: " & phn
End If

errormsg:
If Err.Number <> 0 Then
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End If


End Sub
 
Back
Top