outlook macro that parses subjects of a mail folder and sends a daily report

Hello all,
following this fantastic macro
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28634016.html
I wish to create another macro that parses the daily received emails subject and sends an email with report with how many email with the same subject I received during the last day (or the current day if it's easier).
do you know how to that?
thank you very much
best regards
Nick
nschwendAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helen FeddemaCommented:
This can certainly be done (using Outlook VBA -- not VBScript, which is only needed for code on Outlook forms).  A few questions:

1.  How do you define "daily received emails"?  Are they gathered together into a specific folder, for example?
2.  Does "the same subject" mean a specific subject (if so, what is it?) or do you want counts of the number of emails for each subject?
3.  What is the email to which the report should be sent?
4.  What format is the report -- plain text in body of email, HTML text in body of email, an attached text file, or something else?
nschwendAuthor Commented:
Hi Helen,
sorry for the delay....
I try to answer as clearer as possible:
1. I've a folder with Archived_Alerts where arrives all the archived issues from the monitoring system. I wish the code looks at this folder for the report
2.  the monitor system normally send an email with a subject like this when there is a problem:
Check Last Transcoded - PROBLEM
and a subject like this when the problem has been solved
Check Last Transcoded - OK
3. I wish that the report email counts how many email I received in this folder the last day and (if possible) how much for each type of issue
4. this is completely indifferent. I use it as an internal report to have a global summary.

hope it helps.
thank you very much
best regards
Nick
Helen FeddemaCommented:
Do you have a list of the problem types?  That would make it easier to check for the number of each type.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

nschwendAuthor Commented:
It's a long list (almost 70 types of different subjects)... Should be more interesting to be dinamically created....
Thank you!
Nick
Helen FeddemaCommented:
OK, will see if I can create a sample procedure later today.
nschwendAuthor Commented:
thank you very much Helen... Really appreciate!
regards
Nick
Helen FeddemaCommented:
I have the procedure done except for the part that counts the number of messages per subject.  This would be much easier to do in Access than in Outlook -- is that a possibility?  If not, I can save the subjects as Outlook NoteItems.  In that case, could they be saved in the default Notes folder?
Helen FeddemaCommented:
Here is a module containing a procedure that will do what you want.  It lets  you specify the date to examine (yesterday is the default) and select the Outlook folder to examine.   NoteItems are created in the default Notes folder to store the subjects.  Let me know if you have any problems with it.

Option Explicit


Public Sub ProcessMail()
'Created by Helen Feddema 7-Apr-2015
'Last modified by Helen Feddema 7-Apr-2015

On Error GoTo ErrorHandler

   Dim strBody As String
   Dim fld As Outlook.Folder
   Dim msg As Outlook.MailItem
   Dim strSubject As String
   Dim nms As Outlook.NameSpace
   Dim itm As Object
   Dim itms As Outlook.Items
   Dim ritms As Outlook.Items
   Dim strFilter As String
   Dim strPrompt As String
   Dim strTitle As String
   Dim strStartDate As String
   Dim strEndDate As String
   Dim strDateRange As String
   Dim dteYesterday As Date
   Dim fldNotes As Outlook.Folder
   Dim nitm As Outlook.NoteItem
   Dim fldSubjects As Outlook.Folder
   Dim nitmTest As Outlook.NoteItem
   
   Set nms = Application.GetNamespace("MAPI")
   dteYesterday = DateAdd("d", -1, Date)
   dteYesterday = CDate(InputBox("Date to examine", "Enter Date", CStr(dteYesterday)))
   
SelectMailFolder:
      Set fld = nms.PickFolder
      Debug.Print "Folder item type: " & fld.DefaultItemType

On Error Resume Next

      If fld Is Nothing Then
         GoTo ErrorHandlerExit
      ElseIf fld.DefaultItemType <> olMailItem Then
         strPrompt = "Please select a Mail folder"
         strTitle = "Folder error"
         MsgBox prompt:=strPrompt, _
            Buttons:=vbExclamation + vbOKOnly, _
            Title:=strTitle
         GoTo SelectMailFolder
      Else
         Debug.Print "Folder name: " & fld.Name
      End If
   
On Error GoTo ErrorHandler

      Set itms = fld.Items
      itms.Sort "[Start]"
   
      'Create exact date range
      strStartDate = Format(dteYesterday, "m/d/yyyy hh:mm AMPM")
      strEndDate = Format(DateAdd("d", 1, dteYesterday), "m/d/yyyy hh:mm AMPM")
      strDateRange = "[Received] >= """ & strStartDate & _
         """ And [Received] <= """ & strEndDate & """"
      Debug.Print "Date range: " & strDateRange
      Set ritms = itms.Restrict(strDateRange)
      Debug.Print "Number of items for yesterday: " & ritms.Count
   
      'Create Notes, one for each unique subject
      Set fldNotes = nms.GetDefaultFolder(olFolderNotes)

On Error Resume Next
      
      Set fldSubjects = fldNotes.Folders("Subjects")
      
      If fldSubjects Is Nothing Then
         Set fldSubjects = fldNotes.Folders.Add("Subjects")
      End If
      
On Error GoTo ErrorHandler

      For Each itm In ritms
         If itm.Class = olMail Then
            Set msg = itm
            strSubject = msg.Subject
            strFilter = "[Subject] = " & Chr(34) & strSubject & Chr(34)
            Debug.Print "Filter: " & strFilter
            
On Error Resume Next
            
            Set nitmTest = fldNotes.Items.Find(strFilter)
            Debug.Print "Item found? " & Not nitmTest Is Nothing
            
            If nitmTest Is Nothing Then
                'Set nitm = fldSubjects.Items.Add(olNote)
                Set nitm = Application.CreateItem(olNoteItem)
                nitm.Body = strSubject
                nitm.Save
            End If
            
            Set nitmTest = Nothing
         End If
      Next itm
      
On Error GoTo ErrorHandler

   'Count number of mail messages with each subject
   Debug.Print "Count of subjects: " & fldNotes.Items.Count
   Set msg = Application.CreateItem(olMailItem)
   strBody = ""
   
   For Each itm In fldNotes.Items
      strSubject = itm.Subject
      strFilter = "[Subject] = " & Chr(34) & strSubject & Chr(34)
      Set ritms2 = ritms.Restrict(strFilter)
      strBody = strBody & "Number of items for " _
         & strSubject & ": " & ritms2.Count & vbCrLf
      msg.Body = strBody
      msg.Save
   Next itm
   
   msg.Display
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in ProcessMail procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nschwendAuthor Commented:
Hi Helen,
first of all THANK YOU for your time and the code you provided...
I think you let something that parses the notes instead of the mailbox folder... it takes the only note I have...

I've a stupid question: How can I do to execute it at midnight?
thank you a lot!
best regards
Nick
Helen FeddemaCommented:
The code parses the mail messages in the folder you select, and then saves the subjects for the selected day as notes.  Then it parses the notes and counts how many messages there are for each subject (ritms is a collection of mail messages for the selected date, and ritms2 is a collection of mail messages with the current subject).

To execute it at midnight, it would have to be converted into a VBScript and run from the Windows Scheduler.  I am not sure that all the VBA code could be translated into VBScript, which is a more limited dialect of VB.  Or (if you have Access), it might be possible to convert the code into Access VBA (only minor modifications would be needed), then run it from an Access macro, which could be called from a VBScript.  Let me know if that would work.

Perhaps there is no need to run it exactly at midnight.  It processes a certain day's messages only, so it doesn't really matter when it is run.
nschwendAuthor Commented:
Hi Helen
I did some small changes and now works perfectly.
following this link http://www.vboffice.net/en/developers/send-emails-automatically/ I also created a task to send it automatically.
kind regards and thank you very much!!!!
NIck
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.