Results 1 to 10 of 10
  1. #1
    hazeleyre23 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    34

    Mailbox count access

    Hi,

    I was wondering if there was a way of getting Access VBA count the number of emails in various mailboxes.

    I have found code to do it in Excel VBA however this seems to be different to Access.



    Thanks

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest post the excel code - it might be slightly different but no reason why it won't work in access with a bit of tweaking

  3. #3
    hazeleyre23 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    34
    Code:
    Dim ns As Namespace
    Dim myOlApp As Outlook.Application
    Dim MonthFolderSet, YearFolderSet As Variant
    Dim MailboxCheck As MAPIFolder
    Dim Mailboxes, MailboxCount, MailBoxCounter As Integer
    Dim MailboxName, MailboxGroup As String
    
    
    
    
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    Set myfolders = myNameSpace.Folders
    Set ns = GetNamespace("MAPI")
    
    
    
    
    On Error Resume Next
    
    
        'Clear Old Data
        Sheets("Data").Activate
        
        Range("G2:L10000").ClearContents
    
    
        MonthFolderSet = Range("B2").Value
        YearFolderSet = Range("C2").Value
        
        Mailboxes = Sheets("Data").Range("A100").End(xlUp).Row
        
        c = 7
        r = 2
        
        For MailboxCount = 2 To Mailboxes
        
            MailboxName = Worksheets("Data").Range("A" & MailboxCount).Value
            MailboxGroup = Worksheets("Data").Range("B" & MailboxCount).Value
    
    
              
                n = 1
                          
                    Do Until myfolders.Item(n) = MailboxName
                            n = n + 1
                            If n > 100 Then GoTo NoMailBoxName
                    Loop
                
                Set myfolder = myfolders.Item(n)
                
                FolderNo = 1
                
                Set myfolder2 = myfolder.Folders("Inbox")
                    For Each Item In myfolder2.Items
                        itsj = Item.Subject
                        itsn = Item.SenderName
                        itbo = Item.ReceivedTime
                        itfl = myfolder2
                        Cells(r, c) = itsn
                        Cells(r, c + 1) = itbo
                        Cells(r, c + 2) = itsj
                        Cells(r, c + 3) = itfl
                        Cells(r, c + 4) = MailboxName
                        Cells(r, c + 5) = MailboxGroup
                        r = r + 1
                    Next Item
                    Set myfolder2 = myfolder
                Set myfolder3 = myfolder.Folders("Inbox").Folders("Awaiting Response")
                    If myfolder3 Is Nothing Then
                    Else
                        For Each Item In myfolder3.Items
                            itsj = Item.Subject
                            itsn = Item.SenderName
                            itbo = Item.ReceivedTime
                            itfl = myfolder3
                            Cells(r, c) = itsn
                            Cells(r, c + 1) = itbo
                            Cells(r, c + 2) = itsj
                            Cells(r, c + 3) = itfl
                            Cells(r, c + 4) = MailboxName
                            Cells(r, c + 5) = MailboxGroup
                            r = r + 1
                        Next Item
                    End If
                    Set myfolder3 = myfolder
                Set myfolder4 = myfolder.Folders("Inbox").Folders("Tipac")
                    If myfolder4 Is Nothing Then
                    Else
                        For Each Item In myfolder4.Items
                            itsj = Item.Subject
                            itsn = Item.SenderName
                            itbo = Item.ReceivedTime
                            itfl = myfolder4
                            Cells(r, c) = itsn
                            Cells(r, c + 1) = itbo
                            Cells(r, c + 2) = itsj
                            Cells(r, c + 3) = itfl
                            Cells(r, c + 4) = MailboxName
                            Cells(r, c + 5) = MailboxGroup
                            r = r + 1
                        Next Item
                    Set myfolder4 = myfolder
                    End If
        Next MailboxCount
    thank you

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    that is not actually determining the email count but populating a worksheet with a list of emails for previously specified mailboxes- is that what you want?

    If not, what do you want to do with the data? store in a table? populate a list box on a form?

  5. #5
    hazeleyre23 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    34
    Ideally I just want the code to tell me how many emails have been received today for each of the mailboxes (have 8 separate mailboxes for each department). Thanks

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    still a bit vague, not really answered my question - but try using this adapted code which shows the number of emails in inboxes.

    Code:
    Private Sub countEmails()
    Dim myOlApp As Object
    Dim myNameSpace As Object
    Dim myFolders As Object
    Dim Mailbox As Object
    Dim MailFolder As Object
    
    
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    Set myFolders = myNameSpace.folders
    
    For Each Mailbox In myFolders
        For Each MailFolder In Mailbox.folders
            If MailFolder.Name = "inbox" Then Debug.Print Mailbox.Name; " "; MailFolder.Name; "  "; MailFolder.Items.Count
        Next MailFolder
    Next Mailbox
    End Sub

  7. #7
    hazeleyre23 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    34
    Thank you this is what I am after, however how to do define the mailboxes I want to count. As I do not need it to count my own personal mailbox.

    As In excel i would be able to put something like :
    Code:
     MailboxName = Worksheets("Mail").Range("A" & MailboxCount).Value
    Thank you

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    depends on how many you want to include/exclude to modify your if statement

    If MailFolder.Name = "inbox" and MailFolder.Name <> "myName" Then Debug.Print.....

    if you keep a table of those mailfolders you want to include then perhaps use a dcount


    If MailFolder.Name = "inbox" and dcount("*","tblMallFolders","Foldername='" & MailFolder.Name & "'")<> 0 Then Debug.Print.....

  9. #9
    hazeleyre23 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    34
    sorry, can I just check - would my code then look like:

    I am very new to access, so I apologies. I only want it to look at the inbox, but for some reason the MailFolder seems to default to Deleted Items - do you know why this is or how I can just get it to look at Inbox.

    Code:
    Private Sub countEmails()
    Dim myOlApp As Object
    Dim myNameSpace As Object
    Dim myFolders As Object
    Dim Mailbox As Object
    Dim MailFolder As Object
    
    
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    Set myFolders = myNameSpace.folders
    
    For Each Mailbox In myFolders
        For Each MailFolder In Mailbox.folders
            If MailFolder.Name = "inbox" and dcount("*","tblMallFolders","Foldername='" & MailFolder.Name & "'")<> 0 Then Debug.Print Mailbox.Name; " "; MailFolder.Name; "  "; MailFolder.Items.Count
        Next MailFolder
    Next Mailbox
    End Sub
    thank you

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry, did in a hurry - you should be using

    dcount("*","tblMailBoxes","MailBoxName='" & MailBox.Name & "'")<> 0


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-15-2014, 01:59 PM
  2. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  3. Search for an attachment in a mailbox
    By Jim.H. in forum Access
    Replies: 1
    Last Post: 01-24-2012, 06:04 PM
  4. Access link to Outlook mailbox
    By chodges in forum Programming
    Replies: 0
    Last Post: 01-06-2012, 12:11 PM
  5. Assigning Outlook task from shared Outlook mailbox
    By Remster in forum Programming
    Replies: 2
    Last Post: 11-16-2011, 04:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums