How to manage error handlers in importing mails from outlook to excel through VBA?

You already have a counter i which gets incremented only if any valid email is found and you can check the value of i in the end and display the message accordingly.

Something like this...

If i > 1 Then
    MsgBox "Total " & i - 1 & "emails were found.", vbInformation, "Operation Done!!"
Else
    MsgBox "No Login/Logout found for the given date"
End If

Select allOpen in new window

I need something like this , three conditions in if and it has to show popups for all three conditions. But it is not working properly.
what changes we can make to below code? please tell me..

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Sales")
 If Range("B1").Value = "" Then
        msgbox "Please enter proper date"
    ElseIf OutlookMail.ReceivedTime <> Range("From_date").Value Then
    msgbox "No login mails are available based on this date"
   
    Else:
    i = 1
        For Each OutlookMail In Folder.Items
   
    If OutlookMail.ReceivedTime = Range("From_date").Value And OutlookMail.Subject = ("updates")
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
       
        i = i + 1
    End If
   
Next OutlookMail
 msgbox "Operation done!!."
End If

Only i don't want to show the count.  based upon date it has to take data.

like if no date present means it has to show no date.
if for present date no mails are available means it has to show no mails for that.
And last for mentioned date it has fetch mails details and also  show operation successful.

See if this is what you are trying to achieve.

'Check if B1 contains a valid date, if not exit sub
If Range("B1").Value = "" Or Not IsDate(Range("B1").Value) Then
    MsgBox "Please enter a proper date."
    Exit Sub
End If

'If B1 cotains a valid date, proceed further
Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Sales")
i = 1
For Each OutlookMail In Folder.Items
    If OutlookMail.ReceivedTime = Range("From_date").Value And OutlookMail.Subject = "updates" Then
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
        i = i + 1
    End If
Next OutlookMail

If i > 1 Then   'If any email was received on the mentioned date.
    MsgBox "Operation done!!.", vbInformation
Else    'If no eamil was received on the mentioned date
    MsgBox "No Login/Logout found for the given date", vbExclamation
End If

Select allOpen in new window

Thanks first two conditions are working properly.

But third condition, no mails for mentioned date is not working.

Try to input a future date in B1 and see what message you get.

It is showing msg as Operation done! for future dates.

But it has to show msg as no mails found right?

Yes. This is what it is supposed to do.
Are you sure that Range("From_Date") is cell B1?

yes!!

please find the attachment.
erl.PNG

Any other method is there for referring date?

Email ReceivedTime is normally a timestamp which consists of both the date and time parts in it.
Considering the date entered in B1 is a date without time part, change the IF condition with the following one and see what message do you get in that case.

If DateValue(OutlookMail.ReceivedTime) = Range("From_date").Value And OutlookMail.Subject = "updates" Then

Select allOpen in new window

Also insert a breakpoint on line#13 and execute the code. The code will then stop at line#13 (the IF condition) and from there you can run the code line by line by pressing F8 key, then you can hover your mouse over the variables to cross check if they are matching properly.

I made the changes but still not working.

It is showing as operation done only.

Okay. When you get that message, are you sure that the data is written onto the Sheet?
The messages depend upon the variable i. Are you sure that you are not manipulating the variable i after the For Loop and i is initialized to 1 before the For Loop?
Place a line MsgBox i after the line Next OutlookMail like below and run the code and check what value of i do you get in msgbox.

Next OutlookMail
MsgBox i

Select allOpen in new window

Yes, when i get that msg , data is written on sheet for both correct date and future date.

And i is initialized to 1 before the for loop

You mean data is being written onto the sheet even if the date in B1 is 18-Aug-2018? How can it be?

I am not getting that only.
it is not writing anything  but it is showing popup operation done!

If data is not being written onto the sheet when the date in B1 is 18-Aug-2018, what is the value of i in msgbox you put after the line Next OutlookMail as I suggested here?

same as msgbox i

same as msgbox i

What do you mean by that?

If you put a line MsgBox i after the For Next Loop, once the code reaches the line MsgBox i, it will display a msgbox with the value of variable i.
It would be an integer value. What value do you get in that msgbox?

See, we are not discussing here any rocket science but a simple logic. And the logic is, you have initialized the variable i to 1 before the For Loop starts and it is incremented only if an email is found with the same date in B1 and if it happens, due to the line i = i + 1, i will be increased by 1 and this process will continue until the for loop gets finished.

After the For Loop gets finished, if i is still 1, that means no email matching to the date in B1 was found so i was not incremented and if i is greater than 1 that means i was incremented which tells that at least one email was found with the matching date in B1.
And then based on value if variable i, msgbox will be popped up.

then what i have to do there?

please tell me..

How can I tell if you are not able to reply to my queries.
Please go through all my posts and see what I suggested and what I wanted to know from you and make sure you follow what I suggested and reply accordingly.

if i gave msgbox i,
first it is showing count of mails and msg operation done.

for future it is displaying as same , means it will show count with decreasing 1 an dshow the msg as operation done.

but it should not happen right.

If i tried like this means,

It will show same popup for both availbale mails and for future date i.e., No mails available.

In immediate window it is showing

counter i is :1
and showing popup as no mails avalable

if i entered correct date also it is showing same popup.

please help!!

when i executed that second time, if i add any future date also it is showing mails for earlier date and showing msg operation done.

if i entered correct date also it is showing same popup.

Before testing the code for each scenario, clear the content of Immediate Window and check the Immediate Window again to see the value of i.
If for a correct date, the Immediate Window shows i as 1 that means now email qualifies the following criteria...

If DateValue(OutlookMail.ReceivedTime) = Range("From_date").Value And OutlookMail.Subject = "updates" Then

Select allOpen in new window

Make sure the you are checking DateValue(OutlookMail.ReceivedTime) = Range("From_date").Value and the Subject of email is "updates" because if that's not the case, the counter i will not be incremented by 1 and will be equal to 1 in the end.

Also if i is 1 for correct date, no data will be written onto the sheet. Is that the case

Where have you declared the variable i?
Make sure you haven't declared it on top of the module or as a Public variable otherwise it will retain it's value and it will not be reset to 1 unless you close the application and open it again.

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer

'Check if B1 contains a valid date, if not exit sub
If Range("B1").Value = "" Or Not IsDate(Range("B1").Value) Then
    msgbox "Please enter a proper date."
    Exit Sub
End If

'If B1 cotains a valid date, proceed further
Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Sales")
i = 1
For Each OutlookMail In Folder.Items
    If DateValue(OutlookMail.ReceivedTime) = Range("From_date").Value And OutlookMail.Subject = "updates" Then
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
        i = i + 1
    End If
Next OutlookMail

'***************************************************************************************
'The following line will print the value of variable i in the Immediate Window
'Press Ctrl + G to open Immediate Window and see what message you get

Debug.Print "Counter i is : " & i

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

If i > 1 Then   'If any email was received on the mentioned date.
    msgbox "Operation done!!.", vbInformation
Else    'If no eamil was received on the mentioned date
    msgbox "No Login/Logout found for the given date", vbExclamation
End If
Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub

i have tried like this

When you enter a correct date, are you sure that the subject of each valid email is "updates"?

yes

Are you sure that the subject is "updates" in lower case?
Also when date is correct, is data written onto the sheet?
Clear the data from the sheet and run the code with the correct date and let me know if the data is written on the sheet what msg you get?

If DateValue(OutlookMail.ReceivedTime) = Range("From_date").Value And OutlookMail.Subject = "updates" Or OutlookMail.Subject = "RE: update" Then

today i have tried like this, because we will get some reply for mails right so..

But it is showing older mails for that mail and show the msg as operation done.

Means if i have given date as 20 Aug, for that it will show mails of 16 and 17 Aug  and displays msg as operation done.

and in immediate window count is showing as 3 for future dates.

It is getting complicated day by day, i am unable  to complete this from long..

What you are telling is actually weird and I don't think I can help any further on this. Logic is very simple and you will need to figure it out yourself.

And one more thing, why it is displaying all mails in list? when i am giving only specific date in B1 cell?

If i have given 20 Aug in that cell means it will show all the mails from 17, 18 and 19th Aug.

Yes. Logic is simple but why it is not working?

Please  help me in this why it is fetching all the mails?

Hi,

Please help for this question..

I am sending attachment for the same.

This solution is resolved my question and also very much helpful.