VBA Excel - Outlook - For Each - Returns mail = nothing - Error mngt

Ploom

New Member
Joined
Feb 28, 2016
Messages
28
Hi,
I made a small macro on Excel which aims to look for a mail on outlook with a specific subject and a specific received time. Works pretty with almost everyone but bugs with the oulook inbox one of my colleague.

The code i wrote:

Code:
For Each olMail In oDefaultFolder.Items
    If olMail.Subject = "DDJ of " & date_target Then
        MyAr = Split(olMail.Body, vbCrLf)

        For i = LBound(MyAr) To UBound(MyAr)
        Sheets("DDJ").Cells(i + 1, 1).Value = MyAr(i)
        Next i
        ddj_empty = False
        Exit For
    End If
Next olMail

I tried with 3 outlook accounts and there was no pb but with the 4th account it bugs..

The pb it returns is it highlights "Next oLMail" and says oLMail = Nothing
I would like for my macro to go on and continues and its search and i have some trouble to handle this nothing error.

i was looking for smtg similar to "if mail = nothing then i = i+1 .. next i " but i found on google a "continue for" but my excel doesn't recognize the continue function ? so i don't know what to do

Any advice?
Thanks a lot
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
What is olMail declared as (or was it not declared)?

i.e. Do you have any of these lines listed above the code you posted?
Code:
Dim olMail As Variant
Dim olMail As Object
Dim olMail As MailItem

What is the error message you receive when it bugs out?
 
Upvote 0
Could you please show the whole sub, not only the for next loop?
 
Upvote 0
Try something like this:

Code:
' Excel module
Sub hand()
Dim ons, ol, olmail, myar, i%, ddj_empty As Boolean, odefaultfolder, date_target$
Set ol = CreateObject("Outlook.Application")
Set ons = ol.GetNamespace("MAPI")
On Error GoTo ErrHandler:
date_target = "07/11/16"
Set odefaultfolder = ons.Folders(4).Folders(1)          ' your folder here
For Each olmail In odefaultfolder.Items
    If olmail.Subject = "DDJ of " & date_target Then
        myar = Split(olmail.Body, vbCrLf)
        For i = LBound(myar) To UBound(myar)
            Sheets("DDJ").Cells(i + 1, 1).Value = myar(i)
        Next
        ddj_empty = False
        Exit For
    End If
Next olmail
MsgBox "end of code."
ErrHandler:
    ' error handling code
    MsgBox "Error " & Err.Number
    Resume Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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