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
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
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
thank youCode: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
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?
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
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
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 :Thank youCode:MailboxName = Worksheets("Mail").Range("A" & MailboxCount).Value
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.....
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.
thank youCode: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
sorry, did in a hurry - you should be using
dcount("*","tblMailBoxes","MailBoxName='" & MailBox.Name & "'")<> 0