Solved

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

Posted on 2014-10-22
9
134 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

713 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