Outlook VBA counting emails automatically every minute

[Redlad1291]

Hi,

I have a VBA script set up in outlook which counts how many emails are in the specific inboxed or sub folders. I have set it up to also create a CSV file and then continue to update every minute but this is not happening. My macro is below, can someone advise what is going wrong

Sub timer()

TimeToRun = Now + TimeValue("00:01:00")
Application.OnTime TimeToRun, "stuff"

End Sub


Sub stuff()

Dim objOutlook As Object, objnSpace As Object, mailboxFolder As MAPIFolder, mailboxFolder As MAPIFolder, mailboxFolder As MAPIFolder
Dim mailbox As Integer, emailcount As Integer, emailCount As Integer
' Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = GetNamespace("MAPI")

On Error Resume Next
Set objFolder = objnSpace.Folders("Mailbox").Folders("Inbox").Folders("Sub folder")
If Err.Number <> 0 Then
Err.Clear
MsgBox "No such folder."
Exit Sub
End If

EmailCount = objFolder.Items.Count

On Error Resume Next
Set objFolder = objnSpace.Folders("Mailbox").Folders("Inbox")
If Err.Number <> 0 Then
Err.Clear
MsgBox "No such folder."
Exit Sub
End If

EmailCount = objfolder.Items.Count

On Error Resume Next
Set objFolder = objnSpace.Folders("mailbox").Folders("Inbox")
If Err.Number <> 0 Then
Err.Clear
MsgBox "No such folder."
Exit Sub
End If

EmailCount = objFolder.Items.Count

Dim csvOutput As String

' Create counts string:
csvOutput = ""
csvOutput = "Emailfolder,Emailfolder,emailfolder " & vbNewLine & mailboxCount & "," & mailboxCount & "," & mailboxCount

Dim fso As Object
Dim fo As Object

Set fso = CreateObject("Scripting.FileSystemObject")
Set fo = fso.CreateTextFile("C:\test\test\test.csv")
fo.Write csvOutput
fo.Close

Set fo = Nothing
Set fso = Nothing
Set mailboxFolder = Nothing
Set mailboxFolder = Nothing
Set mailboxFolder = Nothing
Set objnSpace = Nothing

Call timer

End Sub

if anyone could help me that would be great,

thanks

[skatonni]

Application.OnTime is not valid in Outlook which is why there is


Set objOutlook = CreateObject("Outlook.Application")

[Redlad1291]

Thanks for the response, so do you mean remove the application.ontime and replace it by pointing at the outlook.application obj?

[skatonni]

Put all the code where OnTime is valid. Try Excel.

Uncomment


Set objOutlook = CreateObject("Outlook.Application")


I think you will need


Set objnSpace = objOutlook.GetNamespace("MAPI")


At the end


Set objOutlook = Nothing

[Redlad1291]

Yeah that works in Excel now, so is there any alternative to application.ontime for outlook?

[skatonni]

You can set reminders. The method is described here where a reminder, with a unique subject, triggers the code and the code resets the reminder. Send Emails Automatically http://www.vboffice.net/en/developers/send-emails-automatically/ You will also want to automatically Dismiss the Reminder with that unique subject. https://www.slipstick.com/developer/send-email-outlook-reminders-fires/