Run-time error 91 issue

The code below searches for a hyperlink within a specific email.  matthewspatrick provided me with the correct regex pattern, but I am still having trouble with the run-time error.  I can run the code one time and it works perfect, but if I try to run it again I get the run-time error 91 at this line...

           If Regex.test(objMess.body) Then

I have to exit the database and sometimes restart my computer before the code will run properly again.
Sub EB2_Link()
Forms("frmPricingLoadExternal")!txtFileLoc.Value = ""
 
 '-----EXTRACT URL FROM EMAIL-----'
Const strF As String = "urn:schemas:mailheader:subject like '%**EverBank2**%'"
    Dim olApp, objFolder, objNameSpace
    Dim oSrh As Search, rsts As Results
    Dim objItems, objMess ' As MailItem
    Dim Regex As Object, regm As Object, IE As Object
    Dim ieLink, i As Long
 
    Set olApp = CreateObject("Outlook.Application")
    Set objNameSpace = olApp.GetNamespace("MAPI")
    Set objFolder = objNameSpace.GetDefaultFolder(6)
    objFolder.Items.Sort "[Received]", False
    Set objItems = objFolder.Items
    objItems.Sort "[Received]", True
    Set oSrh = olApp.AdvancedSearch("Inbox", strF)
    Set rsts = oSrh.Results
    Set objMess = rsts.GetFirst
    
    Set Regex = CreateObject("vbscript.regexp")
    Regex.Pattern = "file:(//|\\\\)[^\.]+\.pdf"   ' "((file?):((//)|(\\\\))+[\w\d:#@%/;$()~_?\+-=\\\()*&.pdf]*)"
    Regex.Global = True
           If Regex.test(objMess.body) Then   <-------- ERROR OCCURS ON THIS LINE
            Set regm = Regex.Execute(objMess.body)
             Forms("frmPricingLoadExternal")!txtFileLoc = Right$(regm(0), Len(regm(0)) - InStr(regm(0), "//") - 1)
        End If
    Set objMess = Nothing
    Set rsts = Nothing
    Set oSrh = Nothing
    Set objItems = Nothing
    Set objFolder = Nothing
    Set objNameSpace = Nothing
    Set olApp = Nothing
    Set regm = Nothing
    Set Regex = Nothing
     
 '-----RENAME FILE-----'
    Dim fileNm As String
    fileNm = Forms("frmPricingLoadExternal")!txtFileLoc.Value
        Kill "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf", "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile fileNm, "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf"
 
DoCmd.Hourglass False
End Sub

Open in new window

rsburgeAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
STrange ... I didn't actually try it on a second run since I thought the definition was it.  I have made another try and run it twice, it always finds the same message for me but I presume in your greater picture that it gets deleted or something, (i've had to comment out some code in order to run it.

Chris
Sub EB2_Link()
Forms("frmPricingLoadExternal")!txtFileLoc.Value = ""
 
 '-----EXTRACT URL FROM EMAIL-----'
Const strF As String = "urn:schemas:mailheader:subject like '%**EverBank2**%'"
    Dim olApp, objFolder, objNameSpace
    Dim oSrh As Search, rsts As Results
    Dim objItems, objMess as object ' As MailItem
    Dim Regex As Object, regm As Object, IE As Object
    Dim ieLink, i As Long
    Dim regitm As Variant
 
    Set olApp = CreateObject("Outlook.Application")
    Set objNameSpace = olApp.GetNamespace("MAPI")
    Set objFolder = objNameSpace.GetDefaultFolder(6)
    objFolder.items.Sort "[Received]", False
    Set objItems = objFolder.items
    objItems.Sort "[Received]", True
    Set oSrh = olApp.AdvancedSearch("Inbox", strF)
    Set rsts = oSrh.Results
    Set objMess = rsts.GetFirst
    
    Set Regex = CreateObject("vbscript.regexp")
    Regex.Pattern = "file:(//|\\\\)[^\.]+\.pdf"
    Regex.Global = True
           regitm = Regex.test(objMess.body)
              If regitm Then   '<-------- ERROR OCCURS ON THIS LINE
               Set regm = Regex.Execute(objMess.body)
            Forms("frmPricingLoadExternal")!txtFileLoc = Right$(regm(0), Len(regm(0)) - InStr(regm(0), "//") - 1)
               End If
    Set objMess = Nothing
    Set rsts = Nothing
    Set oSrh = Nothing
    Set objItems = Nothing
    Set objFolder = Nothing
    Set objNameSpace = Nothing
    Set olApp = Nothing
    Set regm = Nothing
    Set Regex = Nothing
     
 '-----RENAME FILE-----'
    Dim fileNm As String
    fileNm = Forms("frmPricingLoadExternal")!txtFileLoc.Value
        Kill "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf", "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile fileNm, "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf"
 
DoCmd.Hourglass False
End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Try the following change, I think the issue is if the regex expressing cannot be solved.  The change detects that condition and skips the regex test in that case.

Chris
Sub EB2_Link()
Forms("frmPricingLoadExternal")!txtFileLoc.Value = ""
 
 '-----EXTRACT URL FROM EMAIL-----'
Const strF As String = "urn:schemas:mailheader:subject like '%**EverBank2**%'"
    Dim olApp, objFolder, objNameSpace
    Dim oSrh As Search, rsts As Results
    Dim objItems, objMess ' As MailItem
    Dim Regex As Object, regm As Object, IE As Object
    Dim ieLink, i As Long
    Dim regitm As Object
 
    Set olApp = CreateObject("Outlook.Application")
    Set objNameSpace = olApp.GetNamespace("MAPI")
    Set objFolder = objNameSpace.GetDefaultFolder(6)
    objFolder.items.Sort "[Received]", False
    Set objItems = objFolder.items
    objItems.Sort "[Received]", True
    Set oSrh = olApp.AdvancedSearch("Inbox", strF)
    Set rsts = oSrh.Results
    Set objMess = rsts.GetFirst
    
    Set Regex = CreateObject("vbscript.regexp")
    Regex.Pattern = "file:(//|\\\\)[^\.]+\.pdf"   ' "((file?):((//)|(\\\\))+[\w\d:#@%/;$()~_?\+-=\\\()*&.pdf]*)"
    Regex.Global = True
           On Error Resume Next
           Set regitm = Regex.test(objMess.body)
           On Error GoTo 0
           If Not regitm Is Nothing Then
               If regitm Then   '<-------- ERROR OCCURS ON THIS LINE
                Set regm = Regex.Execute(objMess.body)
             'Forms("frmPricingLoadExternal")!txtFileLoc = Right$(regm(0), Len(regm(0)) - InStr(regm(0), "//") - 1)
                End If
            End If
    Set objMess = Nothing
    Set rsts = Nothing
    Set oSrh = Nothing
    Set objItems = Nothing
    Set objFolder = Nothing
    Set objNameSpace = Nothing
    Set olApp = Nothing
    Set regm = Nothing
    Set Regex = Nothing
     
 '-----RENAME FILE-----'
    Dim fileNm As String
    fileNm = Forms("frmPricingLoadExternal")!txtFileLoc.Value
        Kill "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf", "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile fileNm, "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf"
 
DoCmd.Hourglass False
End Sub

Open in new window

0
 
rsburgeAuthor Commented:
Thank you.  This code does skip over the error, but then the regex pattern I'm looking for is not picked up even though the email with the file path exists in the inbox.

.msg is not an accepted file extension but I can forward you an email if needed.  I did use code similar to this to find an exact matching subject and it worked perfect, so I'm wondering if it is in the way I am looking for the email subject using the advanced search.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
The essence of the change is it skips over the not found scenarion ... therefore I guess the string isn't what you think!

Assuming nothing sensitive then you can post the contents here and I can put it into a msg for test purposes.

Chris
0
 
Patrick MatthewsCommented:
Hello rsburge,

Hm.  Test returns a boolean, not an object.  Try this:

Sub EB2_Link()
Forms("frmPricingLoadExternal")!txtFileLoc.Value = ""
 
 '-----EXTRACT URL FROM EMAIL-----'
Const strF As String = "urn:schemas:mailheader:subject like '%**EverBank2**%'"
    Dim olApp, objFolder, objNameSpace
    Dim oSrh As Search, rsts As Results
    Dim objItems, objMess ' As MailItem
    Dim Regex As Object, regm As Object, IE As Object
    Dim ieLink, i As Long
    Dim regitm As Boolean
 
    Set olApp = CreateObject("Outlook.Application")
    Set objNameSpace = olApp.GetNamespace("MAPI")
    Set objFolder = objNameSpace.GetDefaultFolder(6)
    objFolder.items.Sort "[Received]", False
    Set objItems = objFolder.items
    objItems.Sort "[Received]", True
    Set oSrh = olApp.AdvancedSearch("Inbox", strF)
    Set rsts = oSrh.Results
    Set objMess = rsts.GetFirst
   
    Set Regex = CreateObject("vbscript.regexp")
    Regex.Pattern = "file:(//|\\\\)[^\.]+\.pdf"   ' "((file?):((//)|(\\\\))+[\w\d:#@%/;$()~_?\+-=\\\()*&.pdf]*)"
    Regex.Global = True
           On Error Resume Next
           regitm = Regex.test(objMess.body)
           On Error GoTo 0
           If regitm Then   '<-------- ERROR OCCURS ON THIS LINE
                Set regm = Regex.Execute(objMess.body)
             'Forms("frmPricingLoadExternal")!txtFileLoc = Right$(regm(0), Len(regm(0)) - InStr(regm(0), "//") - 1)
            End If
    Set objMess = Nothing
    Set rsts = Nothing
    Set oSrh = Nothing
    Set objItems = Nothing
    Set objFolder = Nothing
    Set objNameSpace = Nothing
    Set olApp = Nothing
    Set regm = Nothing
    Set Regex = Nothing
     
 '-----RENAME FILE-----'
    Dim fileNm As String
    fileNm = Forms("frmPricingLoadExternal")!txtFileLoc.Value
        Kill "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf", "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile fileNm, "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf"
 
DoCmd.Hourglass False
End Sub

Regards,

Patrick
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Phew ... I can leave you to the pro!

Chris
0
 
Patrick MatthewsCommented:
Don't leave yet, Chris--I was only able to get part way through this last time :)

Patrick
0
 
rsburgeAuthor Commented:
Thank you both!  I will test this in just a few minutes and get back to you.
0
 
rsburgeAuthor Commented:
Hi - It still isn't finding the email.

The email information is below.

subject = **EverBank2**FW: EverBank Wholesale Rate Sheet
body of email =

-----Original Message-----
From: Gaby Deininger
Sent: 2009-04-17 10:58
To: Lynne Levine
Subject: FW: EverBank Wholesale Rate Sheet 04-17-2009




GABY DEININGER
Gaby Deininger
Office Manager
United Mortgage Services, LLC
270 Sylvan Avenue, Suite L130
Englewood Cliffs, NJ 07632
201-568-8827 Phone Ext. 126
201-227-8066 Fax
201-569-8893 Fax
gaby@unitedmortgagellc.com
www.umsllc.com

P Please consider the environment before printing this e-mail


 
CONFIDENTIALITY NOTICE: This e-mail message,  including all attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, you may not use, disclose, copy, or disseminate this information. Please contact the sender by e-mail immediately and destroy all copies of the original message including all attachments."


-----Original Message-----
From: EverBank Wholesale [mailto:wholesale@everbank.com]
Sent: Friday, April 17, 2009 10:56 AM
To: Gaby Deininger
Subject: EverBank Wholesale Rate Sheet 04-17-2009

Please see Attached Rate Sheets

All Conditions Are Required for File Review EverBank Underwriting Turn Time is 8-9 Days for Conventional and FHA

Thank you for choosing EverBank Wholesale

[Attachment removed and saved by Auto-Mate: "file://C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet4-17-09 (4).pdf"]

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Try this?

Chris
Sub EB2_Link()
Forms("frmPricingLoadExternal")!txtFileLoc.Value = ""
 
 '-----EXTRACT URL FROM EMAIL-----'
Const strF As String = "urn:schemas:mailheader:subject like '%**EverBank2**%'"
    Dim olApp, objFolder, objNameSpace
    Dim oSrh As Search, rsts As Results
    Dim objItems, objMess ' As MailItem
    Dim Regex As Object, regm As Object, IE As Object
    Dim ieLink, i As Long
    Dim regitm As Variant
 
    Set olApp = CreateObject("Outlook.Application")
    Set objNameSpace = olApp.GetNamespace("MAPI")
    Set objFolder = objNameSpace.GetDefaultFolder(6)
    objFolder.items.Sort "[Received]", False
    Set objItems = objFolder.items
    objItems.Sort "[Received]", True
    Set oSrh = olApp.AdvancedSearch("Inbox", strF)
    Set rsts = oSrh.Results
    Set objMess = rsts.GetFirst
    
    Set Regex = CreateObject("vbscript.regexp")
    Regex.Pattern = "file:(//|\\\\)[^\.]+\.pdf"
    Regex.Global = True
           regitm = Regex.test(objMess.body)
              If regitm Then   '<-------- ERROR OCCURS ON THIS LINE
               Set regm = Regex.Execute(objMess.body)
            Forms("frmPricingLoadExternal")!txtFileLoc = Right$(regm(0), Len(regm(0)) - InStr(regm(0), "//") - 1)
               End If
    Set objMess = Nothing
    Set rsts = Nothing
    Set oSrh = Nothing
    Set objItems = Nothing
    Set objFolder = Nothing
    Set objNameSpace = Nothing
    Set olApp = Nothing
    Set regm = Nothing
    Set Regex = Nothing
     
 '-----RENAME FILE-----'
    Dim fileNm As String
    fileNm = Forms("frmPricingLoadExternal")!txtFileLoc.Value
        Kill "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf", "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet-previous.pdf"
        MoveFile fileNm, "C:\Users\Public\aLXE-Pricing\EverBank2\JacksonvilleRateSheet.pdf"
 
DoCmd.Hourglass False
End Sub

Open in new window

0
 
rsburgeAuthor Commented:
it's working, but only on the first time I run it.  If I try to run it more than once, I get the error at this line...

regitm = Regex.test(objMess.body)

and it says regitm = Empty

could it be because it thinks it has already found the getfirst from this line...
Set objMess = rsts.GetFirst

do I somehow need to clear the advanced search?
0
 
rsburgeAuthor Commented:
I don't actually delete the email until after I verify the new file using code attached to a different command button.  For some reason I keep getting this error if I run it more than once.

Sometimes if I reset the project and wait a few minutes it will run again, other times it won't.  It is almost like one of the objects isn't being released.

Do I possibly need some additional references that I may not have checked off?  
0
 
rsburgeAuthor Commented:
I just tried changing this...
Const strF As String = "urn:schemas:mailheader:subject like '%**EverBank2**%'"

to
dim strF as String
strF = "urn:schemas:mailheader:subject like '%**EverBank2**%'"

and it seems to run multiple times without issue.

Do you know why that might be?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Given it works once then it makes no sense at all (to me) but if it's fixed it then well done to you.

The best I can guess at is a dodgy character somewhere corrupting matters but I can't begin to think how/where ... but your redefine might just have influenced something - either removing the problem or 'moving it' elsewhere.

Chris
0
 
rsburgeAuthor Commented:
Well, I'm not sure why it is working either, but I am thankful that it is.  :)

Thank you Chris and Patrick for all of your help on this; I really appreciate it.
0
 
rsburgeAuthor Commented:
I split the points between all of the answers because I went back and tested them all with the Const change and they all work.  Thank you again for all of your help!!  Have a pleasant day!
0
 
rsburgeAuthor Commented:
FYI to anyone looking at this question for help...  I accepted all of the answers because I went back and tested them all with the Const change and they all work.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
That's very considerate and have a nice day yourself!

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.