YTL Excel #123: Array Formula To Count E-mails

Hi all,

Looking for a bit of help here I have been trying to find a macro I can put in a blank excel workbook that will read the number of emails in my Microsoft Outlook 2007 secondary inbox. (So not my personal mailbox the one shown below which is my departments inbox.)

I need this to count the number of emails either by day or month so we can keep track of the number of requests we are getting and paste this data into excel.

Any ideas would be appreciated.

Thanks

Danny

Hello everybody! I am very new to excel programming. I don't usually program using excel or vba but I have to for a project.

I have to find a way to send macro emails to members with a custom message with to their emails in this excel spreadsheet with their corresponding passwords in the next column. I want to use vba and I have Windows Mail.

Attached is a sample form of the members.

Hi,

I saw someone ask a similar question to what I have (counting the # of emails in an Outlook folder) and someone posted this code, which works very well.
However I would also like to be able to find the oldest date and 10th oldest date in a particular outlook folder. Can someone help ?


Sub CountDatedEmails()

'Declare Outlook app and folder object variables.
Dim objOutlook As Object, objnSpace As Object
Dim objFolder As Object, objFolderA As Object, objFolderB As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = objOutlook.GetNamespace("MAPI")

'Verify existence of MIS folder as direct subfolder of Personal Folders.
On Error Resume Next
Set objFolder = objnSpace.Folders("Personal Folders").Folders("MIS")
If Err.Number 0 Then
Err.Clear
MsgBox "No such folder named MIS.", 48, "Cannot continue"
Exit Sub
End If

'Verify existence of Enquiries folder as direct subfolder #1 of Personal Folders.
On Error Resume Next
Set objFolderA = objnSpace.Folders("Personal Folders").Folders("MIS").Folders("Enquiries")
If Err.Number 0 Then
Err.Clear
MsgBox "No such folder named Enquiries exists in the MIS folder.", 48, "Cannot continue"
Exit Sub
End If

'Verify existence of Enquiries folder as direct subfolder #2 of Personal Folders.
On Error Resume Next
Set objFolderB = objnSpace.Folders("Personal Folders").Folders("MIS").Folders("Application")
If Err.Number 0 Then
Err.Clear
MsgBox "No such folder named Application exists in the MIS folder.", 48, "Cannot continue"
Exit Sub
End If

'All folders are present, OK to continue.

'Declare and define the myDate variable to be yesterday's date.
Dim myDate As Date
myDate = DateSerial(Year(Date), Month(Date), Day(Date) - 1)

'Declare and define the count and date variables for all 3 folders.
Dim iCount As Integer
Dim EmailCountMIS As Integer, EmailCountEnquiries As Integer, EmailCountApplication As Integer
Dim DateCountMIS As Integer, DateCountEnquiries As Integer, DateCountApplication As Integer

'Count total and yesterday's received emails in the MIS folder:
EmailCountMIS = objFolder.Items.Count: DateCountMIS = 0
For iCount = 1 To EmailCountMIS
With objFolder.Items(iCount)
If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCountMIS = DateCountMIS + 1
End With
Next iCount

'Count total and yesterday's received emails in the MIS > Enquiries folder:
EmailCountEnquiries = objFolderA.Items.Count: DateCountEnquiries = 0
For iCount = 1 To EmailCountEnquiries
With objFolderA.Items(iCount)
If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCountEnquiries = DateCountEnquiries + 1
End With
Next iCount

'Count total and yesterday's received emails in the MIS > Application folder:
EmailCountApplication = objFolderB.Items.Count: DateCountApplication = 0
For iCount = 1 To EmailCountApplication
With objFolderB.Items(iCount)
If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCountApplication = DateCountApplication + 1
End With
Next iCount

'Advise the user of all counts for the three folders.
MsgBox _
"MIS folder email count - -" & vbCrLf & _
"Total: " & vbTab & vbTab & EmailCountMIS & vbCrLf & _
"Yesterday: " & vbTab & DateCountMIS & vbCrLf & vbCrLf & _
"MIS > Enquiries folder email count - -" & vbCrLf & _
"Total: " & vbTab & vbTab & EmailCountEnquiries & vbCrLf & _
"Yesterday: " & vbTab & DateCountEnquiries & vbCrLf & vbCrLf & _
"MIS > Application folder email count - -" & vbCrLf & _
"Total: " & vbTab & vbTab & EmailCountApplication & vbCrLf & _
"Yesterday: " & vbTab & DateCountApplication, , "Email counts:"

'Release object variable memory
Set objFolder = Nothing
Set objFolderA = Nothing
Set objFolderB = Nothing
Set objnSpace = Nothing
Set objOutlook = Nothing

End Sub

If there is mailbox in outlook called "MIS". What VB should I use to count emails in Mailbox.

Your help will be greatly appreciated.

Nancy

Macro Count emails in your outlook inbox
Please help me step by step as am not able to get the count with the code below. i guess am missing some config on objOutlook.GetNamespace("MAPI") and from which folder do i get the count
Code:

 
 
Sub HowManyEmails()
Dim objOutlook As Object, objnSpace As Object, objFolder As Object
Dim EmailCount As Integer
Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = objOutlook.GetNamespace("MAPI")
   
   On Error Resume Next
   Set objFolder = objnSpace.Folders("Personal Folders").Folders("MIS")
   If Err.Number  0 Then
   Err.Clear
   MsgBox "No such folder."
   Exit Sub
   End If
   
   EmailCount = objFolder.Items.Count
   Set objFolder = Nothing
   Set objnSpace = Nothing
   Set objOutlook = Nothing

MsgBox "Number of emails in the folder: " & EmailCount, , "MIS email count"
End Sub

Please help

Afternoon Experts! I hope this post finds you well.

I need help with some VBA coding, and no idea where to start. I'm a complete novice - but desperately need help.

In a sentence: I need to keep a running total of emails into a Microsoft Outlook Inbox.

I don't need to know how many are currently in there; I don't need to know how many have been moved, I just need a little counter, that increases every time a new email drops in. It can be manually reset on a daily basis back to 0, and then at 7am when the inbox starts to be worked on, the counter needs to count how many drop come in to that email address.

... Is this even possible? I've been hunting around and all I seem to be able to find is counters that count the current total number of emails in an inbox - not when a new one appears.

Thanks in advance for any help! ^_^

Hello,

I have an array formula and I want to count the below person only once. So I would want to say count either column S if it says PRO or column T if it says PRO...any suggestions? I need to calculate the rows as it pastes from an outside source, and so I cannot delete one of the person's rows.

N P S T Work Level Gender Action Reason 3 Jane Doe 1 F PRO UWL 4 Jane Doe 1 F PAY PRO

=SUM(($N$3:$N$1500="1")*($P$3:$P$1500="F")*($T$3:$T$1500="PRO"))+SUM(($N$3:$N$1500="1")*($P$3:$P$1500="F")*($S$3:$S$1500="PRO"))

I have Excel 2003

Thanks so much!!!

I would like to change for formula below to indicate specific cells rather than D3:Z3 and don't know how to separate the cell names.

=COUNTIF('Progress Details'!$D3:$Z3,"y")/COUNTA('Progress Details'!$D3:$Z3)


So instead of D3:Z3, I'd like D2, F2, H2, etc. and will need the same for the CountIf and CountA portions of the formula.

Help will be greatly appreciated.

Hi,

I am using the following array formula to count the total of 2 different criteria on a separate sheet within the workbook. I have used this method before with success and don't understand why I am coming up with the result of zero this time.

=SUM(('Sheet 2'!F2:F39="A")*('Sheet 2'!F2:F39="B"))

I do hold control + shift + enter

Thanks,
Paula

I have a formula that concatenates text from 11 cells into one string. I then want to count how many occurences of a particular text string are in that string i.e.
Code:

=IF(Z18"",CONCATENATE($Z18&$F18&$G18&$H18&$I18&$J18&$K18&$L18&$M18&$N18&$O18),"")

This may produce a string such as "Test/ImpStandard 6Your FinancesiDWHUONOCISEDI(blank)(blank)(blank)(blank)(blank)". This string consists of the project stage("Test/Imp"), project type ("Standard 6") and then 10 system names (in this example "Your Finances", "iDW", "Huon", "OCIS", "EDI" and 5 "(blank)" systems).
This formula runs down a column and I want to count the occurences of each system for each project stage:
Code:

=IF(MID(XZ$3:XZ$120,1,4)="Test",SUM(LEN(XZ$3:XZ$120)-LEN(SUBSTITUTE(XZ$3:XZ$120,"HUON",""))))

This is the formula (entered as an array formula) I hoped would work to give me a count of all projects that are in the "Test" phase (by checking the 1st 4 characters are = "Test") and also have "HUON" in the string. So for the above example string this should produce a count of 1 but this does not work as expected - the numbers the formula returns are not correct (not an Excel error there is a number but it is not the correct value).
Does anybody have any idea why or a better way to approach this - my data set consists of 10 columns each of which could contain the name of a system and I have a calculated column that gives me project lifecycle and I need to know how many projects are using a particular system at a particular life cycle stage.