Run time error 5 - Invalid procedure or argument

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,486
I found (and borrowed) the following code from somewhere (no longer sure from where)
Not sure it is what I need now - due to errors, but: Is there a better way?;
The error is occuring 8 out of 10 times (My Inbox contains 80 unique e-mails). Can anyone
spot a problem in the code for me. Thanks very much for any assitance. Also, Version 2003 of both Outlook and Excel are in use here.

Jim


Sub SingleRequest() 'Populate requested Store Number with Current Outlook e-mail for same
Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim Fldr As Outlook.MAPIFolder
Dim olMail As Variant
Dim Subj As String
Dim recTime As Date
Dim stBody As String
Dim StNum As String
Dim LineBreak As Long
Dim i As Long
Dim ctr As Long
Application.ScreenUpdating = False

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.Folders("Personal Folders-Jim").Folders("Inbox").Folders("StopIn")

ActiveSheet.Range("A1:B200").ClearContents
ctr = 0
For Each olMail In Fldr.Items
Subj = olMail.Subject
StNum = ExtractNum(olMail.Subject)
If StNum = Range("C1").Value Then
recTime = olMail.ReceivedTime
ctr = ctr + 1
With ActiveSheet
.Cells(1, 1).Value = Subj
.Cells(1, 2).Value = recTime
.Columns(2).AutoFit
End With
stBody = olMail.Body

LineBreak = 1
i = 3

Do ' Getting R/T error Invalid Procedure call or argument (on Next line)
ActiveSheet.Cells(i, 1).Value = _
Mid(stBody, LineBreak, InStr(LineBreak, stBody, Chr(10)) - LineBreak)
LineBreak = InStr(LineBreak + 1, stBody, Chr(10)) + 1
i = i + 1
Loop Until LineBreak = 0 Or LineBreak > Len(stBody)

Exit Sub 'Quit Macro
End If
Next olMail
If ctr = 0 Then

MsgBox Range("D1").Value & " has not reported in today - " & Format(Date, "mmmm dd, yyyy")
End If

ActiveSheet.Columns(1).AutoFit
Application.ScreenUpdating = True

Set olApp = Nothing
Set olNs = Nothing
Set Fldr = Nothing
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
As a follow up - each line of the imported stBody seems to end with Chr(13) and Chr(10), although these 2 are not a part of the character count when i do a =Len(stBody).
HTH
 
Upvote 0

Forum statistics

Threads
1,214,946
Messages
6,122,401
Members
449,081
Latest member
JAMES KECULAH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top