Exporting Outlook Message Counts to Excel


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.

  1. Open Notepad.
  2. Copy the code below and paste it into Notepad.
  3. Edit the code as needed. I’ve included comments where changes can/should be made.
  4. Save the file with a .vbs extension. You can name it anything you want.
  5. Create a scheduled task that runs periodically. You can set the frequency to whatever you want.
  6. 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.
About these ads

69 comments on “Exporting Outlook Message Counts to Excel

  1. Hi David,

    preventing double items would be fine – for “same day items” (it is not necessarily). One Sub-sub-folder-export is needed, something like this here: (objnSpace.Folders(“XXX”).Folders(“XXX”).Folders(“XXX”)

    Thank you!

  2. Hello David, very good work!

    My Question: I want to make my own statistics in excel. So i would have every single mail in excel with these fields:
    Sender, Subject, Received date (xx.xx.xxx), Sent date (xx.xx.xxx)

    It is Ok when received date (xx.xx.xxx) or sent date (xx.xx.xxx) is blank (my Outlook shows sent date, the outlook of a friend of mine uses received date).

    Best Regards,
    Alfred Hoechst

    • Hi, Alfred.

      Thanks, I’m glad you like it.

      Do you want to export the information manually or would you prefer having the information exported automatically each time a new message arrives?

    • Hi David, I want to it daily with the task-scheduler, not “real-time”. But it would be a good thing if you describe both solutions (for other people). Thank you for your toil!

    • Alfred,

      Ok. What folder or folders do you want to export from? Also, will the solution need to have a means to prevent exporting the same item twice?

    • Hi David,

      preventing double items would be fine – for “same day items” (it is not necessarily). One Sub-sub-folder-export is needed, something like this here: (objnSpace.Folders(“XXX”).Folders(“XXX”).Folders(“XXX”)

      Thank you!

    • Alfred,

      Do you plan to export once a day and then combine the workbooks? For example, export today and tomorrow, and then manually combine the two exports into a single workbook for analysis?

  3. Yes I want to delete the sheets as soon as the workbook is opened. .

    As i mentioned sheet names are dates and i only want to delete days that come under Saturday and Sunday.

    Thanks David :-)

    • Andy,

      This subroutine will delete sheets with a date that falls on Saturday or Sunday.

      Sub DeleteSheets(ByRef excWkb As Object)
          Dim excWks As Object, intCnt As Integer
          For intCnt = excWkb.Worksheets.Count To 1 Step -1
              Set excWks = excWkb.Worksheets(intCnt)
              Select Case Weekday(CDate(excWks.Name))
                  Case vbSaturday, vbSunday
                      excWks.Delete
              End Select
          Next
          Set excWks = Nothing
      End Sub
      

      To use this subroutine, call it and pass it a workbook object. Make sure that the sheets in the workbook you pass all have a name that is a valid date.

  4. Can I have a separate VBA that will delete the sheets.

    Sheet name are assigned as date for example sheet1 as “1 March 2014″, sheet2 as 2 March 2014. How can I delete the sheet based on the day. For example I want to automatically delete sheets which come on Saturday and Sunday.

    • Hi, Andy.

      Yes, that’s doable. How do you want to trigger the delete? Do you want to run it manually or do you want it to happen automatically? If automatically, then what’s the triggering action?

  5. Hello David,

    I have a secured exe file that will open the application. Is it possible to open the application.exe file from vba.

    It should be like when the user runs the vba, the vba should authenticate the user and if the user name and password is correct then it will allow to open the application.

    Regards

    • Hi, Andy.

      Sorry it’s taken me so long to get back to you.

      I don’t think what you describe will work the way you envision. First, there’s no practical way that I know of to secure the exe in such a way that it could only be run by the script. A user would only need to look at the source code of the script to find the location of the exe and launch it from outside the script. Second, when you say “authenticate the user” I’m assuming you mean you want to authenticate them to Active Directory. If so, then that’s a problem. I don’t know of a secure way of doing that. By “secure” I mean not passing the password in clear text. Third, Once the user is “authenticated” (i.e. the script has verified who they are), then there’d have to be a list of authorized users the script would consult to see if the user is authorized to run the program.

      I think there’s a better and much simpler solution to this that doesn’t require any scripts at all. Create a group in AD that will control access to the program. Add everyone who is allowed to run the program to that group. Add the exe to the computer of everyone who is authorized to run the program and set the permissions to only allow members of the group to execute the program. That should accomplish what you’ve described without the need for scripts or additional control mechanisms.

  6. Hi David,

    How can I change the last unread email time stamp for Date & Time to time only. I don’t want the cell to have the date. I only require time. Could you please help.

    • Hi, Anil.

      Change this line of code from

      excWks.Cells(lngRow, OLDEST_UNREAD_COL) = datAge
      

      to

      excWks.Cells(lngRow, OLDEST_UNREAD_COL) = FormatDateTime(datAge, vbShortTime)
      
  7. Hi David,

    I have tried the code, but in my code I am using 5 mailbox to get Unread message count. and when i tried the option to open the excel file towards the end, it opened 5 times, that is every time when the code has checked the unread emails in a mailbox.

    • Anil,

      That’s something of a problem. I can change the behavior so you’re only prompted once, but that will only work if all of the exports are always to the same workbook. If you ever exported to different workbooks (e.g. Inbox to one workbook, some other folder to another workbook), then the code would only open the last workbook. Will each run always be to the same workbook?

  8. It worked. Thank a Lot. You are a Genius. :-)

    David, I have included a Message that will show at the end of the script that will display that the sheet is updated.

    Set olkApp = Nothing
    MsgBox “Sheet in location ‘C:\Message_Counts.xlsx’ is Updated with the Mail Box Details”
    WScript.Quit

    Is it possible to provide the option in the message box? Like “Do you want to open the sheet” Click Yes or No.

    Again Thanks you very much for the help :-)

    • Thanks, Anil, and you’re welcome.

      Yes, that’s possible. I’ve modified the code in ExportMessageCountToExcel accordingly. Please replace the version of ExportMessageCountToExcel you have now with the one below. Leave the rest of the code as is.

      Sub ExportMessageCountToExcel(strFolder, strWorkbook, intSheet)
          Const FOLDER_NAME = 1
          Const UNREAD_COUNT_COL = 2
          Const OLDEST_UNREAD_COL = 3
          Const TOTAL_COUNT_COL = 4
          Dim olkFld, excApp, excWkb, excWks, lngRow, datAge, olkMsg, intAns
          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
          datAge = #1/1/2100 12:01:00 AM#
          For Each olkMsg In olkFld.Items
              If olkMsg.Class = olMail Then
                  If olkMsg.UnRead = True Then
                      If olkMsg.ReceivedTime < datAge Then
                          datAge = olkMsg.ReceivedTime
                      End If
                  End If
              End If
          Next
          If datAge = #1/1/2100 12:01:00 AM# Then
              datAge = ""
          End If
          excWks.Cells(lngRow, FOLDER_NAME) = olkFld.FolderPath
          excWks.Cells(lngRow, UNREAD_COUNT_COL) = olkFld.UnReadItemCount
          excWks.Cells(lngRow, OLDEST_UNREAD_COL) = datAge
          excWks.Cells(lngRow, TOTAL_COUNT_COL) = olkFld.Items.Count
          intAns = MsgBox("Sheet in location ‘C:\Message_Counts.xlsx’ is Updated with the Mail Box Details.  Do you want to open the sheet?", vbInformation + vbYesNo)
          If intAns = vbYes Then
              excApp.Visible = True
          Else
              Set excWks = Nothing
              excWkb.Close True
              Set excWkb = Nothing
              excApp.Quit
              Set excApp = Nothing
          End If
          Set olkFld = Nothing
      End Sub
      
  9. Hi David, I got the solution for Type mismatch: ‘OpenOutlookFolder’, it was a small code mistake from my side.

    Now I am able to get the folder name and the unread email count, but I am unable to get the time stamp for Date & Time of the oldest unread emailand Total emails(Read and Unread) counts in the excel sheet.

    • Anil,

      Sorry, I thought you just wanted the number of unread messages and the date of the oldest unread. I’ve modified the code to include the total number of messages also. I also fixed the problem with the date of the oldest message. I’d forgotten to include a constant. Replace just the subroutine ExportMessageCountToExcel with the one below.

      Sub ExportMessageCountToExcel(strFolder, strWorkbook, intSheet)
          Const FOLDER_NAME = 1
          Const UNREAD_COUNT_COL = 2
          Const OLDEST_UNREAD_COL = 3
          Const TOTAL_COUNT_COL = 4
          Const olMail = 43
          Dim olkFld, excApp, excWkb, excWks, lngRow, datAge, olkMsg
          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
          datAge = #1/1/2100 12:01:00 AM#
          For Each olkMsg In olkFld.Items
              If olkMsg.Class = olMail Then
                  If olkMsg.UnRead = True Then
                      If olkMsg.ReceivedTime < datAge Then
                          datAge = olkMsg.ReceivedTime
                      End If
                  End If
              End If
          Next
          If datAge = #1/1/2100 12:01:00 AM# Then
              datAge = ""
          End If
          excWks.Cells(lngRow, FOLDER_NAME) = olkFld.FolderPath
          excWks.Cells(lngRow, UNREAD_COUNT_COL) = olkFld.UnReadItemCount
          excWks.Cells(lngRow, OLDEST_UNREAD_COL) = datAge
          excWks.Cells(lngRow, TOTAL_COUNT_COL) = olkFld.Items.Count
          Set excWks = Nothing
          excWkb.Close True
          Set excWkb = Nothing
          excApp.Quit
          Set excApp = Nothing
          Set olkFld = Nothing
      End Sub
      
  10. Hi David,

    i have tried the code but when executing the code it is showing the error:

    Type mismatch: ‘OpenOutlookFolder’

    Kindly help

  11. Yes David :-)
    And one more thing. After the execution of the code I want excel sheet to be opened automatically and the data to be in first like below.

    Uread emails Date & Time of the oldest unread email Total emails
    Inbox 30 02 Jan 2014, 10:50 AM 59
    Personal 12 31 Dec 2013, 01:50 PM 48
    Official 9 24 Dec 2013, 09:00 AM 25
    Junk 5 02 Dec 2013, 02:00 AM 15

    Thanks in Advance :-)

    • Anil,

      To accomplish this, we need to change the code in the ExportMessageCountToExcel subroutine. Replace that subroutine with the code below and you should be in business. Leave the rest of the code as is.

      Sub ExportMessageCountToExcel(strFolder, strWorkbook, intSheet)
          Const FOLDER_NAME = 1
          Const COUNT_COL = 2
          Const OLDEST_COL = 3
          Dim olkFld, excApp, excWkb, excWks, lngRow, datAge, olkMsg
          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
          datAge = #1/1/2100 12:01:00 AM#
          For Each olkMsg In olkFld.Items
              If olkMsg.Class = olMail Then
                  If olkMsg.UnRead = True Then
                      If olkMsg.ReceivedTime < datAge Then
                          datAge = olkMsg.ReceivedTime
                      End If
                  End If
              End If
          Next
          If datAge = #1/1/2100 12:01:00 AM# Then
              datAge = ""
          End If
          excWks.Cells(lngRow, FOLDER_NAME) = olkFld.FolderPath
          excWks.Cells(lngRow, COUNT_COL) = olkFld.UnReadItemCount
          excWks.Cells(lngRow, OLDEST_COL) = datAge
          Set excWks = Nothing
          excWkb.Close True
          Set excWkb = Nothing
          excApp.Quit
          Set excApp = Nothing
          Set olkFld = Nothing
      End Sub
      
  12. Hi David,
    Could you please help me with the below queries
    1. I would like to know the number of unread emails.
    2. Time when i have received the first unread email.

    i am using the above code. could you please help.

    • Hi, Anil.

      “Time when i have received the first unread email.”
      I’m assuming you mean the date/time of the oldest unread email in the folder. is that correct?

  13. Thanks David, I would like to appreciate your blogging efforts. With the help of this blog post,Ii am able to extract my email messages safely….

  14. Thanks David, With the help of this blog post, now I am able to export all imporant messages into more portable form.

  15. Hey Shaima,
    Every time I try to run this script, I get the problem “Type mismatch: ‘OpenOutlookFolder’ ”
    (Code 800A000D)
    This error comes at line 28 which is “Set olkFld = OpenOutlookFolder(strFolder)”.
    The mailbox path is described correctly as explained by you in examples above.
    Please help.
    Mohinder

  16. Pingback: Exporting Unflagged Message Counts to Excel | TechnicLee

  17. David,
    Sorry for having way too many questions, but this is the first time I use macros or VBScript.
    I’m aiming to export msgs from a folder into an existing excel sheet. what I need is:
    1. Export msgs with certain subject only.
    2. before every new export (which will be done weekly) the whole worksheet should be deleted then the new info should be inserted.
    3. I need the job to be scheduled on weekly basis, so it should be a VBScript, as I understand.

    Now with the help of your posts, this is what I have so far:

    Dim olkMsg, olkSes, olkApp, olkFld, Mailbox, ExcelPath
    
    'Connect to Outlook
    Set olkApp = CreateObject("Outlook.Application")
    Set olkSes = olkApp.GetNamespace("MAPI")
    olkSes.Logon olkApp.DefaultProfileName
    
    'Specify the desired Mailbox and Excel File for the export
    Mailbox = "\\example@example.com\Inbox\Unsatisfied"
    
    ExcelPath = "D:\Users\Desktop\VBMacro.xlsx"
    
    ExportMessagesToExcel Mailbox, ExcelPath
    
    'Disconnect from Outlook
    olkSes.Logoff
    Set olkSes = Nothing
    Set olkApp = Nothing
    WScript.Quit
    
    Sub ExportMessagesToExcel(strFolderPath, strFilename)
        
    	Dim olkFld, excApp, excWkb, excWks, olkMsg, intRow, objFSO, subStart, olkLst
    	Set olkFld = OpenOutlookFolder(strFolderPath)
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Open(strFilename)
    	Set excWks = excWkb.Worksheets(1)    
    	
    	
        'Write Excel Column Headers
            excWks.Cells(1, 1) = "Incident Number - Remedy"
    		excWks.Cells(1, 2) = "Order Number - Peregrine"
            excWks.Cells(1, 3) = "Received"
            excWks.Cells(1, 4) = "Sender"
       
            intRow = 2
    		
                'Write messages to spreadsheet
    				
                For Each olkMsg In olkFld.Items
                        'Add a row for each field in the message you want to export
    					'Export Incident Numbers of Remedy
                        If InStr(1, olkMsg.Subject, "UNSATISFIED Reply about Incident ") Then
    					subStart = replace(olkMsg.Subject, "FW: ", "")
                        excWks.Cells(intRow, 1) = Mid(subStart, 34, 15)
    					excWks.Cells(intRow, 3) = olkMsg.ReceivedTime
                        excWks.Cells(intRow, 4) = olkMsg.Sender
                        End If
    					'Export Order Number of Peregrine
    					If InStr(1, olkMsg.Subject, "UNSATISFIED Reply about Work Order ") Then
    					subStart = replace(olkMsg.Subject, "FW: ", "")
                        excWks.Cells(intRow, 2) = Mid(subStart, 36, 12)
    					excWks.Cells(intRow, 3) = olkMsg.ReceivedTime
                        excWks.Cells(intRow, 4) = olkMsg.Sender
                        End If
                            
                        intRow = intRow + 1
    			Next
       
        excWkb.Close True
        Set olkMsg = Nothing
        Set olkFld = Nothing
        Set excWks = Nothing
        Set excWkb = Nothing
    	excApp.Quit
        Set excApp = 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
    

    ==================
    The above code exports all msgs in the folder path, I need it to export the msgs with the specific subject “UNSATISFIED Reply about ” only.
    Is there a way that I can set the search for msgs to include the msgs with that text in the subject line only?
    I also need the code to delete the previous entries before entering the new ones, because i don’t want any duplicates. do you think that’s possible?

    Thank you so much David. You have been wonderful in helping me! greatly appreciated! :)

    • Shaima,

      1. Export msgs with certain subject only.
        You appear to have that worked out already. If not, then I don’t understand.
      2. before every new export (which will be done weekly) the whole worksheet should be deleted then the new info should be inserted.
        I solved that by deleting the existing spreadsheet and writing a new one each time.
      3. I need the job to be scheduled on weekly basis, so it should be a VBScript, as I understand.
        Correct. The code is already in VBScript. All you need to do is schedule it using Windows built-in Task Scheduler

      Replace the ExportMessagesToExcel subroutine with the one below. This implements item #2, deleting the file and creating a new one on each run.

      Sub ExportMessagesToExcel(strFolderPath, strFilename)
          Dim olkFld, excApp, excWkb, excWks, olkMsg, intRow, objFSO, subStart, olkLst
          Set olkFld = OpenOutlookFolder(strFolderPath)
          Set excApp = CreateObject("Excel.Application")
          Set excWkb = excApp.Workbooks.Add()
          Set excWks = excWkb.Worksheets(1)
      
          'Write Excel Column Headers
          excWks.Cells(1, 1) = "Incident Number – Remedy"
          excWks.Cells(1, 2) = "Order Number – Peregrine"
          excWks.Cells(1, 3) = "Received"
          excWks.Cells(1, 4) = "Sender"
      
          intRow = 2
      
          'Write messages to spreadsheet
          For Each olkMsg In olkFld.Items
              'Add a row for each field in the message you want to export
              'Export Incident Numbers of Remedy
              If InStr(1, olkMsg.Subject, "UNSATISFIED Reply about Incident ") Then
                  subStart = Replace(olkMsg.Subject, "FW: ", "")
                  excWks.Cells(intRow, 1) = Mid(subStart, 34, 15)
                  excWks.Cells(intRow, 3) = olkMsg.ReceivedTime
                  excWks.Cells(intRow, 4) = olkMsg.Sender
              End If
              'Export Order Number of Peregrine
              If InStr(1, olkMsg.Subject, "UNSATISFIED Reply about Work Order ") Then
                  subStart = Replace(olkMsg.Subject, "FW: ", "")
                  excWks.Cells(intRow, 2) = Mid(subStart, 36, 12)
                  excWks.Cells(intRow, 3) = olkMsg.ReceivedTime
                  excWks.Cells(intRow, 4) = olkMsg.Sender
              End If
              intRow = intRow + 1
          Next
      
          Kill strFilename
          excWkb.SaveAs strFilename
          Set olkMsg = Nothing
          Set olkFld = Nothing
          Set excWks = Nothing
          Set excWkb = Nothing
          excApp.Quit
          Set excApp = Nothing
      End Sub
      
    • That’s wonderful!! Thank you David!
      I have another issue now though.
      How can I specify in the script that if the latest “received” date is within the previous week, i do not want the script to run?
      basically, if this week is Week 26, and the latest Received date in the excel sheet is 6/20/2012–which means a day in Week 25, do not run. otherwise, run.
      Do you think that’s doable?

    • Shaima,

      Are we talking about the script for this post, which is about exporting message counts to Excel, or about the script for exporting the messages themselves? If the latter, then I’d like to move the discussion to that post to avoid confusing anyone who is looking at this post.

  18. Pingback: Exporting Outlook Task Counts to Excel | TechnicLee

  19. hello once again Lee,

    the script is working great, thank you very much!
    I have one issue though, and i’m not sure if it’s an outlook issue or a task scheduler one.
    when i schedule the script to run i get no error messages, but when the time of the run comes, i get an input box asking for the email’s password. is there any way to pass in the password in the script itself instead of inputting it every time it runs?

    • Richard,

      Sorry, but I’m confused by your answer. I asked “Are we talking about actual task items?” You say “Not as far as I’m aware”. If we’re not talking about actual tasks, then what are we talking about?

    • Sorry David, I was looking at your answer to the above question!

      I would like to export the following task counts for each day,
      tasks created
      tasks completed
      Active tasks

      I don’t need any of the task details.

      Sorry for the confusion

    • No worries, Richard. What do you want to export the counts to? If you want them in Excel, then do you want a new workbook each time, a new sheet in an existing workbook, or a new entry on an existing sheet in an existing workbook?

    • Hello David,

      No, outlook is not set to always prompt for credentials.
      I found out the problem with the credentials, however, I am now aiming to read the information for the emails from the server side, instead of HAVING to have a machine powered on for the script to run. Could you help me with that?

      I also have another question. I have a number of emails filtered into a mailbox folder titled Unsatisfied. They all have almost the same title, with the last few characters different.
      “Subject: UNSATISFIED Reply about Incident INC000000092749 – ” this is the subject of the emails. What I need to do is write into an Excel sheet the last part of the title; in other words, I need the characters (INC000000092749) to be written into a simple existing excel sheet with the run date. Do you think that’s possible?

    • Hello, Shaima.

      “read the information for the emails from the server side, instead of HAVING to have a machine powered on for the script to run”
      That will require that you either install Outlook on the server, which is not recommended, or write an event sink. Event sinks are somewhat like macros that run at the Exchange server, with one important difference. A poorly written or buggy event sink could bring down the entire server. Developing one is best left to someone with a lot of experience writing them. I’m not that person.

      Yes, you can get that fragment of the subject with something like this

          If InStr(1, olkItm.Subject, "UNSATISFIED Reply about Incident ") Then
              strIncident = Mid(olkItm.Subject, 34)
          End If
      

      This assumes that olkItm is set to the message you’re checking.

    • Hello again David,

      I am not very familiar with VBS, could you please explain to me a little bit where to include those lines for the fragment of the subject?

    • Good morning!

      The code in this post only exports counts. It doesn’t deal with message details. In order to suggest where the fragment should go I need to understand what you’re trying to accomplish. How does the incident number fit into the counting process?

    • It’s not the same code, I guess.
      I am using your original macro code from the original post.

      What I’m trying to do is to get the incident numbers from the subject line in the msgs written into an excel sheet.
      I need the script to run on weekly basis and write every new reading into a new excel sheet.
      It is very close to one of the revision that you had but that was a macro and it read the whole subject, i only want the incident numbers.
      Do you think that’s doable?

    • It’s doable. It’s hard for me to advise on where to place the code without seeing your code or knowing which of the exports you’re using. Looking at the code from this post you would insert the code immediately after line #23. Something like this

                  .Cells(1,3) = "Sender"
                  If InStr(1, olkItm.Subject, "UNSATISFIED Reply about Incident ") Then
      	        .Cells(1,4) = Mid(olkItm.Subject, 34)
                  End If
      
    • David,

      Do I need to declare anything? it gives me an error message saying “Object Required”

    • Good morning David,

      The script works perfectly fine now except for one thing. I need it when it runs to delete the old information first and then write the new information. what function could I use to do that for the whole worksheet?

    • Good morning, Shaima!

      You want to delete the entire contents of the spreadsheet each time? If so, then instead of writing code to delete the contents how about we just overwrite the entire spreadsheet? That’s simpler and faster.

  20. Hi David,

    love the code and the way it works and have it working on my machine.

    I have to report the daily tasks added and completed to my manager, is there a way of breaking the total number of tasks down in this way?

    Your help would br most appreciated.

    Best regards

    Richard

    • Hi, Richard.

      Thanks. I’m glad the code is working out for you.

      Are we talking about actual task items? If so, then “yes” that should be possible.

    • Hi David,

      I would like to have a new entry in an existing sheet for each day the macro is run.

      Best regards

    • Ok. I’m going to do this as a new post. I expect I’ll have it up on Monday morning.

  21. Hello David,

    I tried the code but it gives me this error:

    Line: 39
    Error: Object Required: ‘oldFld’
    Code: 800A01A8

    I executed in XP Pro SP3, Outlook 2007.

    You can help me ?

    Best Regards,
    LLuís

    • Hi, Lluis. The error is probably the result of an invalid folder path. Here’s an explanation of how folder paths work in Outlook.

      A folder path in Outlook is essentially the same as a folder path in the file system. The one difference being that Outlook folder paths do not include a drive letter. The path to a folder is a list of all the folders from the root to the target folder with each folder name separated from the preceding folder name by a backslash (i.e. \). Consider the following folder structure:

      Mailbox - Doe, John
          - Calendar
          - Inbox
          - Tasks
      Personal Folders
          + Marketing
              + Proposals
              + Reviews
          + Projects
              + Project 1
              + Project 2
      
      The path to "Inbox" is "Mailbox - Doe, John\Inbox".
      The path to "Reviews" is "Personal Folders\Marketing\Reviews".
      The path to "Project 1" is "Personal Folders\Projects\Project 1".
      
  22. Hello again,
    It works now, I just had to fix the path name to the mailbox. But it overwrites the previous values instead of going to a new row!

  23. Hello David,
    I tried the code but it gives me an error msg when I run it. The error msg is in line 30, Error: Object required: ‘olkFld’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s