Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-10-22
9
133 Views
Last Modified: 2014-11-14
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
0
Comment
Question by:easycapital
  • 5
  • 4
9 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40399393
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
 

Author Comment

by:easycapital
ID: 40400490
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40400739
I will see if I can come up with something tomorrow.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40402580
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
 

Author Comment

by:easycapital
ID: 40402740
Dear Helene,

Do insert this macro in excel or outlook?

What are the steps to have report appear in excel?

Br
JP
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40403475
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
 

Author Comment

by:easycapital
ID: 40404293
Not in work computer, but i will try to get it.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40408714
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
 

Author Closing Comment

by:easycapital
ID: 40443552
Thanks.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question