Macro list of company names in tasks field (outlook 2010)

Dear Expert,

Tasks in general have a field labeled "Company".

Needed:
Provide a list with two sections. First section with a header of "´Projects with non-completed tasks" this section composed of companies listed under point #1 below followed by a header for next section of "Project with all tasks all marked complete" followed by the information listed in point #2 below.

1. Lists all Companies (only once per company, and sorted in alphabetical order) which contain at least one task with a flag status criteria "does not" equal to "completed".
2. List all Companies (only once per company, and sorted in alphabetical order) which contain all taskss with a flag status criteria "equal to"  "completed".

Br,
JP
easycapitalAsked:
Who is Participating?
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:
Are these tasks all in the main Tasks folder, or in a subfolder?  It would be a lot easier to create this listing from Access.  Would that work for you?  Also, what output is desired?   A text file, Access table, worksheet, or what?
0
easycapitalAuthor Commented:
Good question - about the desired output. I assumed some Outlook screen output. Ok, into excel. Could it just update the list in a specific worksheet, of a workbook stored on a location in my documents folder?
Humm. I think it is main task folder.
Br,
Juan
0
Helen FeddemaCommented:
I will see if I can come up with something tomorrow.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Helen FeddemaCommented:
I am attaching a little database that will import standard Outlook tasks from a selected folder, and open a report displaying them grouped by Company and Status.  The procedure is based on one from my Working with Outlook ebook:

Public Function ImportStandardTasks()
'Created by Helen Feddema 25-Dec-2011
'Last modified by Helen Feddema 24-Oct-2014
On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim nms As Outlook.NameSpace
   Dim fld As Outlook.Folder
   Dim lngItemCount As Long
   Dim rst As DAO.Recordset
   Dim itm As Object
   Dim tsk As Outlook.TaskItem
   Dim itms As Outlook.Items
   Dim strStatus As String
   Dim strSQL As String
   
   Set nms = appOutlook.GetNamespace("MAPI")

SelectTaskFolder:
   'Let the user select an Outlook folder to process
   
On Error Resume Next
   
   Set fld = nms.PickFolder
   Debug.Print "Folder item type: " & fld.DefaultItemType
   
   If fld Is Nothing Then
      GoTo ErrorHandlerExit
   ElseIf fld.DefaultItemType <> olTaskItem Then
      strPrompt = "Please select a Task folder"
      strTitle = "Folder error"
      MsgBox prompt:=strPrompt, _
         Buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo SelectTaskFolder
   End If

On Error GoTo ErrorHandler
   
   lngItemCount = fld.Items.Count
   Debug.Print "Number of items in folder: " _
      & lngItemCount
   
   If lngItemCount = 0 Then
      MsgBox "No tasks in selected folder; exiting:"
      GoTo ErrorHandlerExit
   Else
      Set itms = fld.Items
      strSQL = "DELETE * FROM tblTasks " _
         & "WHERE [StartDate] < Date()"
      DoCmd.SetWarnings False
      CurrentDb.Execute strSQL
      
   End If
   
   'Process items in selected folder
   Set rst = CurrentDb.OpenRecordset("tblTasks", dbOpenDynaset)
   lngItemCount = 0
   
   For Each itm In itms
      If itm.Class = olTask Then
         Set tsk = itm
         
         With rst
            .AddNew
            ![TaskName] = Nz(tsk.Subject)
            ![CompanyName] = Nz(tsk.Companies)
            ![StartDate] = tsk.StartDate
            ![DueDate] = tsk.DueDate
            Debug.Print "Task status: " & tsk.Status
            ![StatusNo] = tsk.Status
            
            If tsk.Status = olTaskComplete Then
               strStatus = "Completed"
            Else
               strStatus = "Not Completed"
            End If
            
            ![Status] = strStatus
            
            lngItemCount = lngItemCount + 1
            .Update
         End With
      End If
      
NextTask:
   Next itm
   rst.Close
   
FinalMessage:
   If lngItemCount = 0 Then
      strPrompt = "No qualifying tasks to add to table"
   ElseIf lngItemCount = 1 Then
      strPrompt = "1 task added to table"
   ElseIf lngItemCount > 1 Then
      strPrompt = lngItemCount & " tasks added to table"
   End If
      
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in ImportStandardTasks procedure" _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Open in new window

Outlook-Import.accdb
0

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
easycapitalAuthor Commented:
Dear Helene,

Do insert this macro in excel or outlook?

What are the steps to have report appear in excel?

Br
JP
0
Helen FeddemaCommented:
I could do an export to Excel.  The macro (which just runs a procedure and opens the report) wouldn't work in Excel or Outlook.  Do you have Access?
0
easycapitalAuthor Commented:
Not in work computer, but i will try to get it.
0
Helen FeddemaCommented:
This kind of report is easy to do in Access, since it supports one-to-many relationships.  Outlook has a flat-file MAPI database, so if you want to do something with relationships between Companies and Tasks (or anything else), Access is the tool to use.
0
easycapitalAuthor Commented:
Thanks.
0
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
Outlook

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.