We help IT Professionals succeed at work.

Unread emails from ms access

Svgmassive asked
I am looking for the fates way to get the number of unread emails and the latest email from ms access vba.The inbox has multiple folders
Watch Question

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012


Depends on how you want to go about it; will you have direct access to the mailbox or would you want to do this through the client (which it sounds like), such as Outlook?

For the later, you'll need something like vbMAPI:



Software & Systems Engineer
If you don't 3rd party then you can use Outlook Automation as described here :https://www.excelanytime.com/excel/index.php?option=com_content&view=article&id=165:automate-microsoft-outlook-from-excel-using-vba&catid=79&Itemid=475
Because this kind of a rare question it needs some work to located the correct folder and read the email status.
I think example 12,16 & 17 are best suited for your case.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

To get unread emails you can do something like
Sub GetUnreadEmails()
    Dim oOutlook              As Object
    Dim oOutlookNS            As Object
    Dim oOutlookFolder        As Object
    Dim oOutlookMsgs          As Object
    Dim oOutlookMsg           As Object
    Dim i                     As Long
    Const olFolderInbox = 6

    On Error GoTo Error_Handler

    Set oOutlook = CreateObject("Outlook.application")
    Set oOutlookNS = oOutlook.GetNamespace("MAPI")
    Set oOutlookFolder = oOutlookNS.GetDefaultFolder(olFolderInbox)
    Set oOutlookMsgs = oOutlookFolder.Items.Restrict("[UnRead] = True")
    If oOutlookMsgs.Count = 0 Then
        MsgBox "There are currently no unread e-mails."
        For Each oOutlookMsg In oOutlookMsgs
            i = i + 1
            Debug.Print i, oOutlookMsg.Subject
        Next oOutlookMsg
    End If

    On Error Resume Next
    If Not oOutlookMsg Is Nothing Then Set oOutlookMsg = Nothing
    If Not oOutlookMsgs Is Nothing Then Set oOutlookMsgs = Nothing
    If Not oOutlookFolder Is Nothing Then Set oOutlookFolder = Nothing
    If Not oOutlookNS Is Nothing Then Set oOutlookNS = Nothing
    If Not oOutlook Is Nothing Then Set oOutlook = Nothing
    Exit Sub

    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: UnreadEmails" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Open in new window

You can simply change the folder or Restrict clause to suit pretty much any need.