Cash Flow Report in MS Project

MS Project is a good tool for managing project costs but it can be a bit tricky if you are trying to pull information from it for reporting.

 

For the projects I manage, each task has a cost category: Deferred and Expense. This is a custom column created to reflect Capex vs. Opex costs for the project.

 

I was trying to find a way to pull a Cash Flow (Cost month-by-month) report for the two Cost Categories which showed my total costs on the project like so:

Cost Category	January	February	March	April	May	June	July
Deferred SW	xx.xx	xx.xx	xx.xx	xx.xx	xx.xx	xx.xx	xx.xx
Expense	xx.xx	xx.xx	xx.xx	xx.xx	xx.xx	xx.xx	xx.xx

 

 

At first I was trying to play around with Views that showed me the information in the format I wanted and then export it in Excel. This obviously didn’t work. Getting project to filter by the Cost Category column wasn’t easy and the Summary Totals reflected a sum of the two categories.

 

That’s when I came across the Visual Reports feature of Project.

This report allowed me to choose the extra column I wanted (Cost Category) and create a pivot table in Excel which I could modify as needed.

 

 

Got exactly what I was looking for.

Export GAL contacts using GetExchangeUser

Was searching the net for a neat and easy way to export GAL contacts from Outlook. There were many options that exported contacts but I couldnt find one that pulled the Exchange Server properties for the user.
Obviously VBA seemed like the most recommended option so I had some exploring to do.

Enter the GetExchangeUser method which is available in Office 2007

1. Create a Email message with the list of contacts you want to export and save it as a Draft


2. Go to the Draft foldert and select the email message


3. Enter the VBA mode in Outlook and create a new module, paste the below code and execute




Sub AddToContactsFromEmail()
    Dim olkContacts As MAPIFolder, olkContact As ContactItem, olkSelected As Selection, olkItem As Object, olkRecipient As Recipient
    'Select the email in outlook from which you want the contacts added
    Set olkSelected = Application.ActiveExplorer.Selection
    If olkSelected.Count > 0 Then
        For Each olkItem In olkSelected
            If olkItem.Class = olMail Then
                Set olkContacts = Application.Session.GetDefaultFolder(olFolderContacts)
                For Each olkRecipient In olkItem.Recipients
                    Set olkContact = Application.CreateItem(olContactItem)
                    With olkContact
                        .FullName = olkRecipient.Name
                        .Email1Address = olkRecipient.Address
                        .BusinessAddress = olkRecipient.AddressEntry.GetExchangeUser.Address
                        .OfficeLocation = olkRecipient.AddressEntry.GetExchangeUser.OfficeLocation
                        .BusinessTelephoneNumber = olkRecipient.AddressEntry.GetExchangeUser.BusinessTelephoneNumber
                        .JobTitle = olkRecipient.AddressEntry.GetExchangeUser.Alias
                        'Enter additional properties required from GAL
                        .Categories = "Exported"
                        .Save
                    End With
                Next
            End If
        Next
    End If
    Set olkContact = Nothing
    Set olkContacts = Nothing
    Set olkSelected = Nothing
    Set olkItem = Nothing
    Set olkRecipient = Nothing
End Sub




4. You will see all contacts in your email message exported to the default Contacts folder in Outlook with the properties you specified in the code


5. From here you can just use the Import/Export feature in outlook to export the Contact list to excel.

Note: There is cvsde utility that exists apparently in the Windows Server that helps you export GAL but I wasnt able to locate the utility on my client machine.