Solved

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

Posted on 2014-10-22
9
121 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
Resolve DNS query failed errors for Exchange
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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: …

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now