Link to home
Start Free TrialLog in
Avatar of Svgmassive

asked on

Unread emails from ms access

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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

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:


Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Daniel Pineault
Daniel Pineault

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.