How to verify a mail from today exist?

Question

I want to see if an email exists in a particular Outlook folder, using Excel VBA.

Sub Get_Calls_MTD_Data()

    'making sure windows not jumping forth and back
    Application.ScreenUpdating = False
    
    Dim getCalls As Workbook
    Dim releaseCalls As Workbook
    Dim fPat As String
    fPat = ThisWorkbook.Path
    Dim SNDate As String
    
    'The sheetname gets the date for the day name, so using variable for that
    SNDate = Date           
         
    '-------------------
    'Error handling doesn't work
    'this dosent work any longer?
    'If Dir(fPat & "\Outlookdata\calls mtd\" & Date & "." & "***") = "" Then
    '
    '    MsgBox "does not find mail"
    '
    'Else
    
    '   making sure the windows dosen jump forth and back and no alerts
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
            
        '---------------------------
        Set getCalls = Workbooks.Open(fPat & "\Outlookdata\Calls mtd\" & Date & "." & "*")
            
        Set releaseCalls = Workbooks.Open(fPat & "\" & ThisWorkbook.Name)
    
        getCalls.Activate
    
        If Not IsEmpty(Range("G2").Value) = True Then
                
            'finding last row
            mylastagent = getCalls.Sheets(SNDate).Cells(Rows.Count, "G").End(xlUp).Row
               
            getCalls.Sheets(SNDate).Range("G2:H" & mylastagent).Copy
        
            releaseCalls.Activate
               
            releaseCalls.Sheets("calls").Range("A1").PasteSpecial xlPasteValues
        End If     
    
        getCalls.Close
    
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
         
        Range("M3").Select
        
        Update_Day_When_Calls_Updates
        
    'Just the end if for the faulty error handling in the top
    ' End If

End Sub

Do I have to loop through the folder to find an email from today?

Also I started to get the prompt "clipboard has too much information, do you want to save it" in the end. Tried here for instance: Disable clipboard prompt in Excel VBA on workbook close

To reference an Outlook folder https://stackoverflow.com/questions/8322432/using-visual-basic-to-access-subfolder-in-inbox. Search the site for Outlook VBA questions about today's items. — niton, Oct 19 '22 at 11:28
This is a Q & A site. You could post an answer rather than editing the question. https://stackoverflow.com/help/self-answer — niton, Oct 19 '22 at 12:41

Eugene Astafiev · Answer 1 · 2022-10-18T15:52:49.040

Don't use strict date checks in Outlook. Instead, you need to use the Find/FindNext or Restrict methods of the Items class that allows getting only items that correspond to the search criteria. In the search criteria I'd recommend using less or greater conditions for dates.

Outlook evaluates date-time values according to the time format, short date format, and long date format settings in the Regional and Language Options applet in the Windows Control Panel. In particular, Outlook evaluates time according to that specified time format without seconds. If you specify seconds in the date-time comparison string, the filter will not operate as expected.

Although dates and times are typically stored with a date format, filters using the Jet and DAV Searching and Locating (DASL) syntax require that the date-time value to be converted to a string representation. In Jet syntax, the date-time comparison string should be enclosed in either double quotes or single quotes. In DASL syntax, the date-time comparison string should be enclosed in single quotes.

To make sure that the date-time comparison string is formatted as Microsoft Outlook expects, use the Visual Basic for Applications Format function (or its equivalent in your programming language).

'This filter uses urn:schemas:httpmail namespace 
    strFilter = AddQuotes("urn:schemas:httpmail:datereceived") _ 
    & " > '" & datStartUTC & "' AND " _ 
    & AddQuotes("urn:schemas:httpmail:datereceived") _ 
    & " < '" & datEndUTC & "'"

See Filtering Items Using a Date-time Comparison for more information.

Read more about the Find/FindNext and Restrict methods in the articles I wrote for the technical blog:

If you need to search for items in multiple folders you may consider using the AdvancedSearch method of the Application class, see Advanced search in Outlook programmatically: C#, VB.NET.

Thank you! I do not get any wiser with this, cus i'm not that good of a programmer. Im sure this would solve it, but its too complicated for me. I'll post my entire cod, and you might see what i want to do. — Bowman, Oct 19 '22 at 06:25
I solved it, thanks anyway, may this will give someone else a few ideas :) Code in the end o the original post — Bowman, Oct 19 '22 at 12:20

score 0 · Answer 2 · answered Oct 19 '22 at 14:04

In the case of "today's mail", if processing time is noticeable, you can .Sort then stop processing once older mail is found.

Option Explicit

Sub Check_If_Mail_From_Today_Exists_Calls_Daily()

    ' Where code is not in Outlook
    ' Reference Microsoft Outlook nn.n Object Library
    Dim ol As Outlook.Application
    Dim fol As Outlook.Folder
    Dim mi As Outlook.MailItem
    
    Set ol = New Outlook.Application
    Set fol = Session.Folders("Random@Email.com")
    Set fol = fol.Folders("OutlookData")
    Set fol = fol.Folders("Calls Daily")
    
    Dim folItems As Items
    Set folItems = fol.Items
    folItems.Sort "[ReceivedTime]", True
    
    Dim j As Long
    For j = 1 To folItems.Count
    
        If folItems(j).Class = olMail Then
        
            Set mi = folItems(j)
            
            If mi.Attachments.count > 1 Then
                If Format(mi.ReceivedTime, "yyyy-mm-dd") = Format(Date, "yyyy-mm-dd") Then
                    Debug.Print mi.Subject
                    Debug.Print " " & Format(mi.ReceivedTime, "yyyy-mm-dd")
                Else
                    'Older mail
                    Exit For
                End If
            End If

        End If
        
    Next

End Sub

.Restrict and .Find could be applied to all cases.

Thanks Niton, I’ll look in to that, and try to get how to post properly so it’s easier to follow. — Bowman, Oct 19 '22 at 15:44

score 0 · Answer 3 · answered Oct 19 '22 at 15:49

I managed to do it like this, probably not the best way, no certainly not the best way, but i solved it for my needs :) Thanks Niton.

Public RecivedToday As String

Sub Check_If_Mail_From_Today_Exists_Calls_Daily()

    Dim ol As Outlook.Application
    Dim ns As Outlook.Namespace
    Dim fol As Outlook.folder
    Dim i As Object
    Dim mi As Outlook.MailItem
    
     
    
    Set ol = New Outlook.Application
    Set ns = ol.GetNamespace("MAPI")
    Set fol = ns.Folders("Random@Email.com").Folders("OutlookData").Folders("Calls Daily")
    
   
    For Each i In fol.Items
    
        If i.Class = olMail Then
                Set mi = i
                If mi.Attachments.Count > 1 And Format(mi.ReceivedTime, "yyyy-mm-dd") = Format(Date, "yyyy-mm-dd") Then
                    'Debug.Print Format(mi.ReceivedTime, "yyyy-mm-dd")
                    RecivedToday = Format(Date, "yyyy-mm-dd")
                    'Debug.Print RecivedToday
                End If
        End If
        
    Next i
    
       
    

End Sub

How to verify a mail from today exist?

3 Answers3