We help IT Professionals succeed at work.

Unread emails from ms access

Svgmassive
Svgmassive asked
on
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
Comment
Watch Question

View Solution Only

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

Commented:

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:


https://www.everythingaccess.com/vbmapi.asp


Jim.

Software & Systems Engineer
Commented:
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

Commented:
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."
    Else
        For Each oOutlookMsg In oOutlookMsgs
            i = i + 1
            Debug.Print i, oOutlookMsg.Subject
        Next oOutlookMsg
    End If

Error_Handler_Exit:
    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

Error_Handler:
    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.