I’m writing this post in response to a comment Shaima Alsaif left on my “Exporting Outlook Messages to Excel” entry from this last October. In the comment Shaima asked if it’s possible to alter the export to do the following:
- Get message counts instead of messages
- Add to the spreadsheet instead of overwriting it
- Have the process run automatically each week
The answer to all three is “yes”. Doing this requires taking a different approach from the one I used in the original solution. The biggest change is that this solution uses VBScript instead of VBA. Here’s why. Outlook does not have a built-in means of scheduling code to run. It’s possible to get around that (e.g. using a repeating task coupled with an event handler that traps the ReminderFire event), but it’s better to use a true scheduler like the one built into Windows (i.e. Task Scheduler). To the best of my knowledge Windows Task Scheduler cannot call a VBA routine, hence the need to use VBScript instead.
Here is how the new script will meet Shaima’s requirements. Getting the message count is easy. Each Outlook folder stores its content in a collection called Items. This collection includes a Count property that denotes the number of items in the folder. As a side note, if we wanted the unread item count, then we’d look to the folder itself instead of its Items collection. Since the solution shouldn’t overwrite the Excel workbook each time we need to alter the original code to open an existing workbook instead of creating a new one each time. We also have to add code to find the last used row in the spreadsheet the message count will be written to. Once we have this we’ll add 1 to it so the new value is written to the next available line. Scheduling the script is easy. All that requires is creating a new Windows Task that calls the script and setting it to run at whatever the desired interval is.
Requirements.
- Outlook 2007 – 2010.
- Any version of Windows.
- The spreadsheet(s) must already exist.
- The computer must be on for the task scheduler to function.
Instructions.
- Open Notepad.
- Copy the code below and paste it into Notepad.
- Edit the code as needed. I’ve included comments where changes can/should be made.
- Save the file with a .vbs extension. You can name it anything you want.
- Create a scheduled task that runs periodically. You can set the frequency to whatever you want.
- Set the task to run this script.
'Declare some variables Dim olkApp, olkSes 'Connect to Outlook Set olkApp = CreateObject("Outlook.Application") Set olkSes = olkApp.GetNamespace("MAPI") olkSes.Logon olkApp.DefaultProfileName 'Call the export process once for each folder count to be exported 'Format is ExportMessageCountToExcel <Path to Outlook Folder>, <Path and filename of the Excel file to export to>, <Number of the sheet the count goes on> 'The following lines are examples. Edit them as needed. Add additional lines as desired. ExportMessageCountToExcel "Mailbox - Doe, John\Inbox", "C:\Message_Counts.xlsx", 1 ExportMessageCountToExcel "Personal Folders\Projects", "C:\Message_Counts.xlsx", 2 'Disconnect from Outlook olkSes.Logoff Set olkSes = Nothing Set olkApp = Nothing WScript.Quit Sub ExportMessageCountToExcel(strFolder, strWorkbook, intSheet) Const EXCEL_COL = 1 Dim olkFld, excApp, excWkb, excWks, lngRow Set olkFld = OpenOutlookFolder(strFolder) Set excApp = CreateObject("Excel.Application") Set excWkb = excApp.Workbooks.Open(strWorkbook) Set excWks = excWkb.Worksheets(intSheet) lngRow = excWks.UsedRange.Rows.Count If lngRow = 1 Then If excWks.Cells(lngRow,1) <> "" Then lngRow = lngRow + 1 End If Else lngRow = lngRow + 1 End If excWks.Cells(lngRow, EXCEL_COL) = olkFld.Items.Count Set excWks = Nothing excWkb.Close True Set excWkb = Nothing excApp.Quit Set excApp = Nothing Set olkFld = Nothing End Sub Function OpenOutlookFolder(strFolderPath) Dim arrFolders, varFolder, bolBeyondRoot On Error Resume Next If strFolderPath = "" Then Set OpenOutlookFolder = Nothing Else Do While Left(strFolderPath, 1) = "\" strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1) Loop arrFolders = Split(strFolderPath, "\") For Each varFolder In arrFolders Select Case bolBeyondRoot Case False Set OpenOutlookFolder = olkApp.Session.Folders(varFolder) bolBeyondRoot = True Case True Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder) End Select If Err.Number <> 0 Then Set OpenOutlookFolder = Nothing Exit For End If Next End If On Error GoTo 0 End Function
Notes.
Here are a few thoughts on how the code could be extended to create additional functionality.
- Create the spreadsheet if it does not exist.
- Get both the item and unread item counts. Right now it only reports the item count.
- Get the counts for a folder and all its subfolders.
- Record the date the count was recorded on.
In the past, I’ve used your scripts to help compile an inventory of my mail archives.
But since my company upgraded to Office365, this script returns:
Error: ActiveX component can’t create object: ‘OutlookApplication’
However, VB Scripts that I run within Outlook still work fine.
Is there any easy way to convert this .vbs file run from the command prompt into a .bas file I can import into Outlook?
Thanks.
Hi, Lis.
I don’t know why you’d be getting that error and unfortunately I’m not an Office365 subscriber so I can’t test in that environment. There’s no simple command prompt conversion of a .vbs to a .bas file. Here is the code converted to run from inside Outlook. That said, if the code can’t create the Outlook application object, then I suspect it may not be able to create an Excel application object either. It will have to do that in order for the solution to work.
That worked.
Thank you very very much.
Pingback: Identify number of items in folder Outlook Cached Exchange Mode and Exchange - SlaveTechno.com
Hi smart man! I copied into notepad and it works fine but I have to save the file like 1000 times as a new “save” window opens right after I click save/over write or re-name it into something else. Like it keeps looping the save as option. I have to close down outlook to put an end to this.. any idea why?
This was your code I copied into notepad which gave me an input at one point of me trying but the next time, it’ asks for 3000 safe prompts.. No matter what I name/rename it, a new save box opens up..
TY!!
‘–> Declare some constants
‘On the next line, edit the internal domain name beginning with the @. Enter it in all lowercase.
Const INTERNAL_DOMAIN = “@company.com”
Const MACRO_NAME = “Export Message Counts”
‘–> Declare some variables
Dim olkApp, olkSes, strRng, arrTmp, datBeg, datEnd, datD1, datD2
‘–> Main routine
‘Prompt for the date range, then calculate the beginning and ending dates
strRng = InputBox(“Enter the date range of the messages to export in the form “”mm/dd/yyyy to mm/dd/yyyy”””, MACRO_NAME, Date & ” to ” & Date)
arrTmp = Split(strRng, “to”)
datD1 = IIF(IsDate(arrTmp(0)), CDate(arrTmp(0)), Date)
datD2 = IIF(IsDate(arrTmp(1)), CDate(arrTmp(1)), Date)
‘Connect to Outlook
Set olkApp = CreateObject(“Outlook.Application”)
Set olkSes = olkApp.GetNamespace(“MAPI”)
olkSes.Logon olkApp.DefaultProfileName
‘Call the export process once for each folder count to be exported
‘Format is ExportMessageCountToExcel , ,
‘The following lines are examples. Edit them as needed. Add additional lines as desired.
Do Until datD1 > datD2
datBeg = datD1 & ” 12:00am”
datEnd = datD1 & ” 11:59pm”
ExportMessageCountToExcel “chris.salamanca@cshs.org\Angela”, “C:\users\SalamancaC\documents\axel.xlsx”, 1
datD1 = DateAdd(“d”, 1, datD1)
Loop
‘Disconnect from Outlook
olkSes.Logoff
Set olkSes = Nothing
Set olkApp = Nothing
MsgBox “Run complete.”, vbInformation + vbOKOnly, MACRO_NAME
WScript.Quit
Sub ExportMessageCountToExcel(strFol, strWkb, intWks)
Dim olkFld, olkLst, olkMsg, olkRec, excApp, excWkb, excWks, lngRow, lngInt, lngExt, strAdr, bolInt, bolExt, dicUnq
lngInt = 0
lngExt = 0
Set dicUnq = CreateObject(“Scripting.Dictionary”)
Set olkFld = OpenOutlookFolder(strFol)
Set excApp = CreateObject(“Excel.Application”)
Set excWkb = excApp.Workbooks.Open(strWkb)
Set excWks = excWkb.Worksheets(intWks)
lngRow = excWks.UsedRange.Rows.Count + 1
Set olkLst = olkFld.Items.Restrict(“[SentOn] >= ‘” & OutlookDateFormat(datBeg) & “‘” & ” AND [SentOn] 0 Then
bolInt = True
Else
bolExt = True
If Not dicUnq.Exists(strAdr) Then
dicUnq.Add strAdr, strAdr
End If
End If
Next
If bolExt And Not bolInt Then
lngExt = lngExt + 1
Else
lngInt = lngInt + 1
End If
Next
excWks.Cells(lngRow, 1) = datD1
excWks.Cells(lngRow, 2) = strFol
excWks.Cells(lngRow, 3) = lngInt
excWks.Cells(lngRow, 4) = lngExt
excWks.Cells(lngRow, 5) = dicUnq.Count
excWks.Columns(“A:E”).AutoFit
Set excWks = Nothing
excWkb.Close True
Set excWkb = Nothing
excApp.Quit
Set excApp = Nothing
Set olkFld = Nothing
Set olkLst = Nothing
Set dicUnq = Nothing
End Sub
Function OpenOutlookFolder(strFolderPath)
Dim arrFolders, varFolder, bolBeyondRoot
On Error Resume Next
If strFolderPath = “” Then
Set OpenOutlookFolder = Nothing
Else
Do While Left(strFolderPath, 1) = “\”
strFolderPath = Right(strFolderPath, Len(strFolderPath) – 1)
Loop
arrFolders = Split(strFolderPath, “\”)
For Each varFolder In arrFolders
Select Case bolBeyondRoot
Case False
Set OpenOutlookFolder = olkApp.Session.Folders(varFolder)
bolBeyondRoot = True
Case True
Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
End Select
If Err.Number 0 Then
Set OpenOutlookFolder = Nothing
Exit For
End If
Next
End If
On Error GoTo 0
End Function
Function GetSMTP(olkAdr)
Const olExchangeUserAddressEntry = 0
Const olExchangeDistributionListAddressEntry = 1
Dim olkEnt
Select Case olkAdr.AddressEntryUserType
Case olExchangeUserAddressEntry
Set olkEnt = olkAdr.GetExchangeUser
GetSMTP = olkEnt.PrimarySmtpAddress
Case olExchangeDistributionListAddressEntry
Set olkEnt = olkAdr.GetExchangeDistributionList
GetSMTP = olkEnt.PrimarySmtpAddress
Case Else
GetSMTP = olkAdr.Address
End Select
Set olkEnt = Nothing
End Function
Function IIF(varTest, varTrue, varFalse)
If varTest Then
IIF = varTrue
Else
IIF = varFalse
End If
End Function
Function OutlookDateFormat(varDate)
Dim intHour, strAMPM
intHour = Hour(varDate)
If intHour > 12 Then
intHour = intHour – 12
strAMPM = “PM”
Else
strAMPM = “AM”
End If
OutlookDateFormat = Month(varDate) & “/” & Day(varDate) & “/” & Year(varDate) & ” ” & intHour & “:” & Minute(varDate) & ” ” & strAMPM
End Function
Hi, nesbie.
Sorry, but I’m not clear on what’s happening. Are you saying that when you try to save the code after pasting into Notepad that Notepad is prompting you over and over to save the file? Or are yous saying that when running the code you’re getting those prompts over and over? Also, what version of Outlook are you running?
Hi David
Need your help on modify your script so that it can run across various shared inbox and sub folders and get the count for each day in the same sheet when I select the month to date.For example, this data should be transfer to excel
Inbox Name 1(Date) 2 3
Inbox1 20 25 30
Inbox1/John 15 20 35
Inbox2 10 35 40
Thanks
Hi, Rahul.
Sorry, but I’m a little confused. Are you saying that you want the number of messages for each date in a separate column? For example
If not, then I don’t understand.
Hi David
Is there any code to export the mails in a folder to csv/xlsx file.
I need ‘from address’ , ‘subject’ and ‘body of mail’ in separate columns in csv file. I want to run the code outside outlook, that is in notepad. Is it possible?
Hi, Geeta.
It is impossible to run any code from Notepad. Notepad does not execute code. That said, it is possible to have an export that runs outside of Outlook. It won’t run in Notepad, but it would be editable in Notepad. You’d actually run the script from either the command line or Windows Explorer. Let me know how you’d like to proceed.
Hello David,Hope you are doing fine .Thanks for the above codes.You are doing a wonderful job by sharing your knowledge.we are learning a lot from you
I have to create a vba code to count the emails received and sent per day for our company generic mailboxes as a part of an audit.i should have to provide this data everyday updated in an excel .I have more than 10 shared mailboxes to do this audit.I was not successful with my codes.
Is there any method that i can get the count of sent /Receive mails segregated by days
I can run the vb manually (i am not looking for vbs script) ..
Thank you very much
Jafer
Hi, Jafer.
So long as the received emails are all in a fixed set of folders and the sent emails are all in the Sent Items folder, then this should be easy to do. Please let me know if that’s the case.
Hello David,
thanks for your quick reply.
I will try to explain my request through the below two cases. I need to count the email per day on received / sent
Case 1 – Mails received
In the case of received emails in inbox, whenever they attend one email , they will move the emails to respective clients folders with in the inbox
So I think while counting we have to iterate through sub folders in inbox also. Is it possible to find emails in the mailbox by using their date of reception even if we moved to subfolders?
Case 2 – emails sent from generic mailbox
Emails sent from generic mail boxes are residing in the sent items of the agent replied to that email. but we are putting cc to the same generic mailbox always while replying from generic mail boxes
.
Thanks
Jafer
Hi, Jafer.
Yes, we can have the code search sub-folders. Should I have the code search a specific set of folders and sub-folders, or does it need to search the entire mailbox (i.e. all folders and sub-folders)?
Hello David,
I prefer to search the entire mailbox.The agents may move the mails to different folders with in the inbox.
For count of sent emails , as i mentioned before we are putting cc to same mailbox itself for audit purpose .Is it possible to count such emails ?(For eg: If somebody sent email from “accounting mailbox” there will be a cc to accounting mailbox itself)
Can we use this criteria to count the daily sent emails since the sent emails are not residing in the sent items of the generic mailbox.
Thank you Very much
Jafer
Hi, Jafer.
I owe you an apology. I’d completely forgotten about your request until someone else made reference to it in their post.
Yes, the script can search the entire mailbox and all its sub-folders. It can also total messages sent and received, including ones sent from that mailbox and ccing the mailbox. In fact, the script would count that as both a send and a receive.
David thanks for all the hard work. Very impressive to read up on the various comments and how quickly you were able to handle them and provide help.
That said – did you ever finish the code for Jafer? I didn’t see it in the comments. From what I can tell, he never answered your last question.
It would be extremely helpful to me if I could get access to that code if it indeed exists. Count of received and sent emails per day – exported to a spreadsheet for various mailboxes and shared mailboxes. Starting with the oldest left in folder and always appending new data when vbs is run.
thanks
–axel
Hi, Axel.
Thanks!
Jafer had replied but I’d not approved his comment. I’ve just done so. I have not written the code for this yet. You mention “Starting with the oldest left in folder and always appending new data when vbs is run.” If I understand what you mean, then that may not be practical. Depending on the number of mailboxes and folders in each mailbox, this would require the script to keep track of the oldest message in every folder in every mailbox. It would be more practical to have the script collect the data for a given date or date range each time it runs.
David, thanks for the insight. Yes it makes a lot more sense to do it your way. Would it be possible to alter your code for me to have a variable in which I can set the range and then it runs on just the sent folder counting each days sent emails and saves it in Excel one line per day.
Export should look like this:
6/25/2015 USER\Sent Items 35
6/26/2015 USER\Sent Items 38
and so on. always adding to the spreadsheet so I can rerun each week to report on their activity.
thanks for all the help in this matter.
Best
–axel
Hi, Axel.
Yes, I can write the code to allow you to enter a date range. I’ve been busy with some other things and it’s going to be a few days, perhaps another week, before I get to this. Just letting you know so you don’t think I’m ignoring you.
David, thanks for taking the time to do help us with it. No worries on when. We are grateful that you are considering it at all. I have another challenge for the master of Outlook VBA if you want to include it in the code.I know I am asking for too much but just in case you like a challenge I thought I’d throw you one. 😉
Would it be possible to report the sent email count in 3 different columns according to the following additional conditions:
1. Total Sent Emails to External domains (all domains – any internal emails)
2. Total Sent Emails to internal domains (aka emails to the same @domain.com as the sent FROM user you are counting for)
3. Unique Sent External (which would be 1 minus any additional email sent to the same user@externaldomain.com same day only)
Here is why that info matters in case you are wondering: We still rely on outlook as our delivery medium for all sales rep emails. To include a measure of productivity we have to validate/cross check touches in our CRM with emails sent. Only column 3 would really show how many unique leads the rep contacted that day.
Hi, Axel.
I’ve one question about the three additional conditions. When a message has a single addressee it’s easy to calculate if the recipient is internal or external. But, some messages are bound to have multiple addressees. How do you want me to count a message where some of the recipients are internal and some are external?
David,
sorry for the late response. I didn’t get a notification for some reason on your reply.
In regards to emails with multiple recipients (internal and external) I would count it only in 1. The rule would be
at least 1 external TO: email address count it only in 1. External.
In regards to unique external – if any of the external emails in a mass email was sent to before that day – the unique count would only count it 1 time.
Axel,
If I’ve understood correctly, then this should do what you’ve described. To use this
To run the script, double-click on the .vbs file. It will prompt you for the date range you want to collect for, run, then notify you when it’s finished. Note that the script does not create the Excel spreadsheet. It expects it to already exist. The format of the spreadsheet is
Please try the script out and let me know if it’s what you wanted.
Hello David, Hope your are fine .I am beginner in VBA I was trying to make audit in our different company mailboxes.I would to get the count of emails received and replied from a generic mailbox per day(going forward everyday). As like you did on above code i would like to get the results on an excel .I have made some codes but not working.Please help
David, thank you so much for the hard work. I have tried to run the script and I am currently getting this error. (outlook 2013, against exchange 2014SP3, outlook running)
Script: Name of Script
Line: 111
Char:5
Error: An internal support function returned an error.
Code: 8004010F
Source: MS Outlook
I would attach the screenshot but I don’t see the option in the comment area.
Not much more to show in the screenshot anyways.
I checked the file and it points to the start of this section:
thanks for all your help.
David,
some more insight on my tests and previous comment just minutes ago. I solved the error by inserting the following error handling to your function. Not sure that is the correct way to handle but it worked and the script runs flawlessly.
Does it help when I tell you the folder/user we are working with has many email aliases?
After that I was able to run the script just fine and it gave me an output.
I have tested a few dates and everything comes in correctly it seems.
Thank you very much.
Would it be possible to make one small additional change?
– currently the output creates one single line for the entire range. I would like to run it for a period in the past and it would be super if I when entering a range I could get the script to write a line for each day of the range with the daily numbers not just a summary for the entire range. is that possible?
thank you so much for all that hard work.
–axel
Hi, Axel.
You’re welcome.
I don’t know why you’d get an error on that line. No, the number of aliases shouldn’t make any difference.
Yes, it’s possible to split the date range into individual days. Rather than doing that though, why not set the existing script to run once a day and have it get the data for the preceding day rather than prompting for a date range?
David,
you are giving me great new ideas 😉
Now I am getting greedy I guess. How about both? 😉
I need to run it for May, June, July 2015 as well to catch up so scheduling would be great but I really also need to be able to run it for a specific range in the past as well.
if scheduling and range is too complex – range would be preferable with daily output per line.
Thank you so much for making those last changes.
Axel,
Please try this version.
David,
thanks for the latest code. I tried it and its seems to be calculating longer but once finished for month timeframe it only added one line to the spreadsheet. the records for the last day of the period.
feels to me as if it overwrites the values in the same row versus pasting to the next row.
could that be?
Axel,
It never ceases to amaze me how a script can work perfectly for me and not work for someone else. After changing the code I ran it several times. The date range I picked each time was 8/1 through 8/4. For each run the script added four lines to the spreadsheet, one for each day in that range. I don’t know how it can fail to do the same thing when you run it. I don’t doubt that it’s not working right for you, I just don’t see how that can happen. What date range did you use?
David,
I have some info for you on why it works and why it might have not. I was testing it in at first in an RDP/TS environment with 2008R2/Office2010 and it overwrites the lines. I then tested it on Win7/Office2013 and it worked. So maybe that explains it.
But most importantly thank you very much for all the hard work and the fantastic script. I really appreciate it very much.
Please let me know if there is anything I can do for you in return.
Best
–axel
Axel,
Thanks for sharing that info. I don’t know why it would behave differently in a RDP/TS environment, but I don’t have access to that type of setup so cannot troubleshoot it. Glad that it works properly on a desktop though.
You’re welcome. Happy I could help out.
Cheers!
Hi David,
This is a wonderful bit of script ! Exactly what I am looking for to automate our reporting ! Thank you so much for sharing this knowledge.
I am trying to adapt Pieter’s version from above for my use but I do not want the popup prompt. Any idea which code I’d need to remove? I’ve tried a few ways around it but the code seems to fail regardless 😦
Thanks!
It also seems like the code is replacing instead of adding lines 😦
Hi, Shawn.
Thanks!
This version of the code removes the popup prompt. It also fixes the problem you discovered of overwriting cells instead of appending them.
Thank you a whole heap David ! This scrpit works flawlessly !
You’re welcome, Shawn!
Hey David,
Trying to work on getting the latest results (of the 3 mailbox’s) pasted into an e-mail and sent to Senior Management Daily.
Are you aware if this is possible? If so, are you able to provide me with a headstart?
If not Thank you so much for your help so far !
Hi, Shawn.
You want one email with all the counts in it or would a separate email for each folder be okay?
Hi David,
One E-mail with all the counts would be great ! If that is possible ! You are a legend !
Regards,
Shawn
Hi, Shawn.
This should do it. Please replace the code you have with the version below. Don’t forget to edit the constants at the top of the code.
Hi David.
Thank you for all the scripting work you’ve given me a hand with !
The script is wonderful and has been working well.
Just a small request,
Is it possible to have the most recent version of the script changed so the E-mail is automatically sent through the Server instead of the Outlook client.
Using something like this :
Dim strbody
schema = “http://schemas.microsoft.com/cdo/configuration/”
Set objEmail = CreateObject(“CDO.Message”)
With objEmail
.From = “XXX_XXX@amx.com.au”
.To = “XXX_XXX@amx.com.au”
.Subject = “Mailbox Report for ” &Date
strBody = “Date” & Space(40) & “Mailbox Name” & Space(20) & “Unread E-mails” & Space(15) & “Total E-mails Today” & Space(10) & “Total E-mails” & vbCRLF
‘ Here we call the function GetData to populate the body text.
strBody = strBody & GetData
.Textbody = strbody
With .Configuration.Fields
.Item (schema & “sendusing”) = 2
.Item (schema & “smtpserver”) = “ausmtp01”
.Item (schema & “smtpserverport”) = 25
.Item (schema & “smtpauthenticate”) = cdoBasic
.Item (schema & “sendusername”) = “XXX_XXX@amx.com.au”
.Item (schema & “sendpassword”) = “password$$123”
End With
.Configuration.Fields.Update
.Send
End With
Thanks !
Hi, Shawn.
What version of Outlook do you have installed on your computer?
Hi David,
I have Outlook 2010 installed on my computer. 🙂
Regards,
Shawn
Hi, Shawn.
Yes, it’s possible to send messages directly through the server and bypass Outlook. However, the CDO libraries, required to use the code you posted, does not come with Outlook 2010 (Microsoft stopped installing CDO beginning with Outlook 2007). So, the first thing to do is to check and see if you have CDO installed on your computer. Search for CDOSYS.DLL. If it’s not there, then you either need to download and install a copy of it or use an alternative library such as vbSendMail. I’m familiar with both of those and can use either one. I just need to know which one to use.
Hi David,
While your working on that. There’s been a small hitch I have been trying to fix. I have been trying to change the Constant Unread Before 5pm to Read Yesterday. (which means I’d like to know the number of e-mails that have been Read dated the previous day).
However, I have not been able to get it to work. your help once again will be priceless !
Hi, Shawn.
The constant UNREAD_BEFORE_5PM only defines the column that count goes in. It has nothing to do with determining the time-frame the message was sent in. That is controlled by these two lines
If you want to count the messages received yesterday, then we need to change those two lines to this
Hi David,
Luckily for me CDOSYS.DLL is present. I have used the CDO Script prior to send automated e-mails so it worked a treat.
Thanks !
Shawn,
Please try this version. Please be sure to edit the constants in the SendMessage subroutine.
David,
You are a legend! Thank you so much for your dedicated help!
You’re welcome, Shawn!
Works like a charm. 😉
BTW: this is my code:
Dear David,
I am writing you regarding this topic, i made some modifications to the vbs script i need the script to count the e-mails only from yesterday, and it will run every single day as sheduled task.
But there are 2 issues
The sheet-s name is sheet2 always (although the filename is correctly set up), and for 2 folders it is counting some more see resutls in the xlsx file below.
Scrip:http://www.mediafire.com/view/38z811zoxz94nki/NonCallBasedReport2.vbs
Xlsx: http://www.mediafire.com/view/moa19w7lq7b9k1z/Book2.xlsx
Could you please advise why it is not correctly showing up for the 2 folders, but all others are fine for each day.
PS: The folder names are intentionally deleted from both the script and the xlsx
Hi, megoldom.
I’m going to be unavailable for about 10 days. I’ll be back in touch with you on the 27th or 28th.
David
Hello David,
Did you find anything? they are still bugged somehow, but all others are working fine.
Hi, megoldom.
I apologize for being slow to get back to you on this.
I’m not clear on what you mean by “… for 2 folders it is counting some more …”. Do you mean that it is counting some messages that it should not? If so, what is it that’s different about those messages?
Hi David,
I receive a message telling me Windows Script Host
Error: Object Required: ‘olkFId’
Hi David,
Ok solved 🙂 My bad. However, I’ve got another question. Can you tell me how I can have the following.
I want to shedule a task every day at 7PM.
So every day a new excell file should be created with all unread mails (in the specified folders) before 2:30PM
Hi, Pieter.
The best way to run this on a schedule is to convert the code from VBA (code that runs from inside Outlook) to VBscript (code that runs outside of Outlook). That approach allows you to create a scheduled task in Windows Task Scheduler that would run each day at 7PM. The alternative is to add some additional code that will allow you to use an Outlook task to schedule this. For this approach to work, Outlook will have to be open and running at 7PM. Which approach do you prefer?
Hi David,
I would like to use the approach with the sheduled task in windows. So maybe I can re-use some code you’ve already written.
Can you tell me which code I have to use/adapt to have this vbs script properly working for me.
I would be very thankfull
Hi, Pieter.
I misread the post you’re comment is to. I thought you were asking about exporting messages to Excel when in fact you were asking about exporting message counts to Excel. That changes my earlier answer. The code for this pose is already written in VBScript, no modifications required. All you nee to do is download the code, edit it as needed, save to a .vbs file, then create a scheduled task that runs it on whatever schedule you want.
Hi David,
But that code needs to be adapted to count the “unread” mails received before 14:30.
The code from you main topic is not counting the unread ones. It is counting them all.
Can you adapt the code?
Thanks!!
Pieter,
This should do it. Replace the ExportMessageCountToExcel subroutine with the one below. Leave the rest of the code as is.
Hi David,
Some little remarks. I think I did not explain myself very well.
So there are three little “issues”:
1. If the code has to count three folders (or more), there are three excell files that open. But is it possible to save those info in the same excell file?
2. If 1 = yes, Is it also possible to add the date that the task has run to those lines?
For example: (layout of excel file)
column a: date(task has run); column b: name of the folder; column c: nr of unread mails; column d: nr of ALL unread mails before 14:30 of the day that the task has run?
3. The code I have now counts the unread mails before 14:30 of each day but that is not what I want. For Example: if the task runs today, I want the number of unread mail of before 14:30 today and all unreads from before (so all of them of yesterday,… It does not matter for those if they are from before 14:30 or after.
Hope this is a bit clear?
Grtz,
Pieter
Further one nice to have would be that each day when the task runs, the file is appended every time?
Further, this is the code I currently have:
BTW: this is my code:
Hi David,
Maybe I did not explain very well, but the code you’ve posted here does not completely meet the requirement.
This code shows the unread mails of all days before 14:30.
What I want it to do is the following:
If the task runs today at 22:00, it should count ALL unread mail from before 14:30 today. Which means also the unread mails from yesterday before 14:30. That is the code currently not doing.
And the second thing I would like to have is that the excel file is appended with the date/time the task has run. So the same workbook is always appended with all the folders it has to count.
FE: (task has run on 03/08 & 04/08
03/08 Foldername totalmails unreadmails unreadmail<14:30
03/08 Foldername2 totalmails unreadmails unreadmails<14:30
04/08 Foldername totalmails unreadmails unreadmails<14:30
04/08 Foldername2 totalmails unreadmails unreadmails<14:30
and so on every day the tasks run, the workbook/sheet should be appended with the new info.
Is this possible David and what should the code be?
Kind regards,
Pieter
Pieter,
I think I’ve got it. Please try this version and let me know if that’s what you wanted.
Hi Davind,
Thanks for your reply but…
can you please check my code. I copied the code you have posted but I suppose that is VB instead of VBS. I had tot delete the keywords like “AS String”,..
Now, when I run the it as a vbs script, it does not do anything. Can you check please?
Thanks!
Here is the code:
Hi, Pieter.
Sorry about that. Somehow I forgot that you wanted the solution in VBscript and not VBA. Here’s the VBscript version.
Hi David,
The code of your last post does the trick but it opens every time a new excel file so the layout is not like underneath. The file is not appended…
Can you adapt the code? (PS do not forget it has to be vbs 🙂 )
I feel we are almost there. Thanks a lot for you help!!
(see earlier in my post)
And the second thing I would like to have is that the excel file is appended with the date/time the task has run. So the same workbook is always appended with all the folders it has to count.
FE: (task has run on 03/08 & 04/08
03/08 Foldername totalmails unreadmails unreadmail<14:30
03/08 Foldername2 totalmails unreadmails unreadmails<14:30
04/08 Foldername totalmails unreadmails unreadmails<14:30
04/08 Foldername2 totalmails unreadmails unreadmails<14:30
and so on every day the tasks run, the workbook/sheet should be appended with the new info.
Hi, Pieter.
“… it opens every time a new excel file …”
I don’t know how that’s possible. The script specifically tells Excel to open an existing workbook. Here’s the relevant line in the script
The Open command tells Excel to open an existing file. If instead we wanted Excel to create a new file, then we’d use an Add command.
I don’t know how the script can be creating a new file each time when the script is telling it to open an existing file. Plus, when I run the script here it opens the existing file each time. I don’t get a new file. In fact, if the specified workbook doesn’t exist, then the code generates an error. If the script were adding workbooks instead of opening an existing workbook, then it wouldn’t error out at that point.
David,
It opens every time the same excel book but the sheet does not show all folders. I gett the message every time for each folder it has to process asking me if i would like to op see the result. When i click yes the sheet only shows one line with the result of the folder he has processed.. I would like to see the five folders in the sheet underneath eachother?
Good morning, Pieter.
“I would like to see the five folders in the sheet underneath each other?”
That’s exactly what I do see when I run the code. How about waiting until the last folder has processed before clicking “Yes” to view the results? I’m thinking that you may be clicking “Yes” after the first folder has processed which would only show you that folder and would likely block the other folders from processing correctly.
You are right David.
I’ve commented out the part where the code asks me to see the result and now it shows the result on one page.
However, 1 more issue: It still shows ALL unread messages instead of the ones of before 14:30.
Underneath is the code:
Dim olkApp, olkSes
Set olkApp = CreateObject(“Outlook.Application”)
Set olkSes = olkApp.GetNamespace(“MAPI”)
olkSes.Logon olkApp.DefaultProfileName
‘ExportMessageCountToExcel “mailbox\Inbox”, “c:\pieter.xlsx”, 1
ExportMessageCountToExcel “PIETER TELENET\Postvak IN”, “C:\Users\Pieter\Documents\Message_Counts.xlsx”, 1
ExportMessageCountToExcel “PIETER TELENET\Postvak IN\Facebook”, “C:\Users\Pieter\Documents\Message_Counts.xlsx”, 1
ExportMessageCountToExcel “PIETER TELENET\Postvak IN\LinkedIn”, “C:\Users\Pieter\Documents\Message_Counts.xlsx”, 1
ExportMessageCountToExcel “PIETER TELENET\Postvak IN\Stepstone”, “C:\Users\Pieter\Documents\Message_Counts.xlsx”, 1
‘C:\Users\Pieter\Documents
‘ExportMessageCountToExcel “PIETER\Inbox”, “H:\TEMP\Message_Counts.xlsx”, 1
‘ExportMessageCountToExcel “PIETER\Inbox\Cockpit FCI”, “H:\TEMP\Message_Counts.xlsx”, 1
‘ExportMessageCountToExcel “PIETER\Inbox\CB Requests”, “H:\TEMP\Message_Counts.xlsx”, 1
‘ExportMessageCountToExcel “PIETER1: RBK”, “H:\TEMP\Message_Counts.xlsx”, 1
‘ExportMessageCountToExcel “PIETER3. MAIL\Incidentfiches”, “H:\TEMP\Message_Counts.xlsx”, 1
‘ExportMessageCountToExcel “Projects\Project X”, “c:\pieter.xlsx”, 1
olkSes.Logoff
Set olkSes = Nothing
Set olkApp = Nothing
WScript.Quit
Sub ExportMessageCountToExcel(strFolder, strWorkbook, intSheet)
‘Declare some constants
Const RUN_DATE = 1
Const FOLDER_NAME = 2
Const UNREAD_TOTAL = 3
Const UNREAD_BEFORE_1430PM = 4
‘Declare some variables
Dim olkFld, olkFlt, olkItm, excApp, excWkb, excWks, lngRow, lngCnt, datRun, datChk
‘Initialize some variables
datRun = Now
datChk = Date & ” 2:30 pm”
intAns = MsgBox(datChk, vbInformation + vbYesNo)
‘Connect to Outlook
Set olkFld = OpenOutlookFolder(strFolder)
Set olkFlt = olkFld.Items.Restrict(“[Unread] = True”)
For Each olkItm In olkFlt
If olkItm.ReceivedTime <= datChk Then
lngCnt = lngCnt + 1
End If
Next
'Connect to Excel
Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Open(strWorkbook)
Set excWks = excWkb.Worksheets(intSheet)
lngRow = excWks.UsedRange.rows.Count + 1
'Write the results to Excel
With excWks
.Cells(lngRow, RUN_DATE) = datRun
.Cells(lngRow, FOLDER_NAME) = olkFld.FolderPath
.Cells(lngRow, UNREAD_TOTAL) = olkFld.UnReadItemCount
.Cells(lngRow, UNREAD_BEFORE_1430PM) = lngCnt
End With
'Ask the user if they want to view the results now
'intAns = MsgBox("Sheet in location ‘" & excWkb.FullName & "’ is Updated with the Mail Box Details. Do you want to open the sheet?", vbInformation + vbYesNo)
'If intAns = vbYes Then
' excApp.Visible = True
'Else
' excWkb.Close True
' excApp.Quit
'End If
excWkb.Close True
excApp.Quit
'Clean-up
Set excWks = Nothing
Set excWkb = Nothing
Set excApp = Nothing
Set olkItm = Nothing
Set olkFlt = Nothing
Set olkFld = Nothing
End Sub
Function OpenOutlookFolder(strFolderPath)
' Purpose: Opens an Outlook folder from a folder path.
' Written: 4/24/2009
' Author: David Lee
' Outlook: All versions
Dim arrFolders, varFolder, bolBeyondRoot
On Error Resume Next
If strFolderPath = "" Then
Set OpenOutlookFolder = Nothing
Else
Do While Left(strFolderPath, 1) = "\"
strFolderPath = Right(strFolderPath, Len(strFolderPath) – 1)
Loop
arrFolders = Split(strFolderPath, "\")
For Each varFolder In arrFolders
Select Case bolBeyondRoot
Case False
Set OpenOutlookFolder = olkSes.Folders(varFolder)
bolBeyondRoot = True
Case True
Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
End Select
If Err.Number 0 Then
Set OpenOutlookFolder = Nothing
Exit For
End If
Next
End If
On Error GoTo 0
End Function
Hi David,
Found the issue.
Had to adapt the following line:
datChk = Date & ” 2:30 pm”
To:
datChk = CDate(Date & ” 14:30:00″)
Thanks a lot for your help!!!
It is now doing what it should 🙂
Sorry I’ve bothered you a lot!
Hi, Pieter.
That’s great. Thanks for letting me know.
No worries. I’m happy I could help.
Cheers!
David,
One more thing. Can you tell me how I have to set the filter in vbscript to get the unread messages without category. I already tried several things but every time I get an error.
Set olkFlt = olkFld.Items.Restrict(“@SQL=” & Chr(34) & _
“urn:schemas-microsoft-com:office:office#Keywords” & _
Chr(34) & ” is null And [Unread] = true”)
I already tried with set olkFlt = olkFld.Items.Restrict(“[Categories] is null and [Unread] = True”)
This does not work neither… 😦
Pieter,
You cannot use Restrict or Find with certain Outlook properties. Categories is one of those properties. See this page for details. The alternative is to filter on unread messages then read each hit and check it to see if Categories equals “”.
Which vbs code should I use to get that result?
Pieter,
The code would be something like this
Hi Peter
Hope you are well.
How did you solve the object required error?
Thanks
Hi, Jake.
David here, not Pieter.
An invalid Outlook folder path is the most common reason for that error. Here’s a macro you can use to find the path to any given folder. Add this code to Outlook. Select a folder then run that macro. It will display the path to that folder in a dialog-box.
Hi Pieter
Are you able to tell me how you solved this issue?
Thanks in advance!!
Hi, Jake.
Did you see my earlier response to your question?