I am running Windows 7 x64 bit, Outlook 2010, Microsoft Project 2010, and SharePoint WSS 3.0. I am not an admin on any of the systems; however, am working with them on the development of this.
What I am looking to do is copy the tasks from MS Project to my Shared SharePoint Outlook Calendar. I would only like to do this periodically and do not need them actively synced. I have “trolled” the internet and have not found a way to do this without a project server. I was wondering if there was a VBA script that someone could point me at or an export/import method I could use. I would be up even for a cheap utility that could do it for me; however, free is always better.
All I need is the task name to transfer with the correct dates. I do not need any other information.
Look at this software as it should do what you are trying to achieve.
It synchronizes MS Project with a calendar or to-do list and automatically sends updates.
I hope it helps.
The code I would like to place in is from Craig, and this would allow for the updates instead of a new task being recreated each time. Here is the post.
When updating the project file with new start dates I would like to update the Outlook appointment relating to that task.
Currently when I update it creates a whole new task and I am having to go through and delete all the previous appointments.
Running Outlook 2003 and Project 2003.
Is there any way I could get the code?
- Create a new appointment in Outlook where it is a new task added in project (as it does now)?
- Check the appointment against the task and if changed update the existing appointment rather than creating a whole new one?
- Leave unchanged tasks and appointments as they were?
Thanks in advance if you can do anything for me.
I have placed my current code below:
Sub OutlookLinkAppt()
Dim appOL As Outlook.Application
Dim mspTask As MSProject.Task
Dim olAppt As Outlook.AppointmentItem
Dim i As Integer
On Error GoTo objerror
Set appOL = GetObject(, “Outlook.Application”) ’ if Outlook is running, this line will work
resumeplace:
For Each mspTask In MSProject.ActiveProject.Tasks
If Not (mspTask Is Nothing) And Not (mspTask.Summary = True) Then
Set olAppt = appOL.CreateItem(olAppointmentItem)
'note that you can capture other Project fields into Outlook fields
olAppt.Subject = mspTask.Name & " -" & mspTask.Text1 & “-” & MSProject.ActiveProject.Name & “-” & “(ID)” & mspTask.ID
olAppt.Body = mspTask.Notes & “-” & “(MS Project Task ID)” & mspTask.ID & "-(Baseline Start/Finish) " & mspTask.BaselineStart & “/” & mspTask.BaselineFinish & " "
olAppt.Start = mspTask.EarlyStart
olAppt.End = mspTask.LateStart
olAppt.Categories = Left(MSProject.ActiveProject.Name, Len(MSProject.ActiveProject.Name))
olAppt.Mileage = mspTask.ID
olAppt.BusyStatus = Free
olAppt.ReminderSet = False
olAppt.AllDayEvent = True
olAppt.Save
Set olAppt = Nothing
End If
i = i + 1
If (mspTask Is Nothing) And Not (mspTask.Summary = True) Then
End If
Next
MsgBox i & " tasks were exported or updated to Outlook as appointments"
Exit Sub
objerror: ’ if Outlook is not running, this will work
Err.Clear
Set appOL = CreateObject(“Outlook.Application”)
GoTo resumeplace
End Sub
Macro Name: Various Buttons
Sub Export_Selection_To_OL_Appointments()
Dim myTask As Task
Dim myItem As Object
Dim myOLNameSpace As Object
Dim myOLFolder As Object
On Error Resume Next
Set myOLApp = CreateObject(“Outlook.Application”)
Set myOLNameSpace = myOLApp.GetNamespace(“MAPI”)
Set myOLFolder = myOLNameSpace.PickFolder
For Each myTask In ActiveSelection.Tasks
Set myItem = myOLFolder.Items.Add(1)
With myItem
.Start = myTask.Start
.End = myTask.Finish
.Subject = myTask.Name & " (Project Task)"
.Categories = myTask.Project
.Body = myTask.Notes
.Save
End With
Next myTask
End Sub
Sub Export_Selection_To_OL_Tasks()
Dim myTask As Task
Dim myItem As Object
On Error Resume Next
Set myOLApp = CreateObject(“Outlook.Application”)
For Each myTask In ActiveSelection.Tasks
Set myItem = myOLApp.CreateItem(3)
With myItem
.StartDate = myTask.Start
.DueDate = myTask.Finish
.Subject = myTask.Name & " (Project Task)"
.Body = myTask.Notes
.Categories = myTask.Project
.Save
End With
Next myTask
End Sub
Sub Export_Selection_To_OL_Notes()
Dim myTask As Task
Dim myItem As Object
Dim myNotesText As String
On Error Resume Next
Set myOLApp = CreateObject(“Outlook.Application”)
For Each myTask In ActiveSelection.Tasks
Set myItem = myOLApp.CreateItem(5)
myNotesText = myTask.Name & " (Project Task)" & Chr(13) & _
" Name: " & myTask.Name & Chr(13) & _
" Start: " & myTask.Start & Chr(13) & _
" End: " & myTask.Finish & Chr(13) & _
" Note: " & myTask.Notes
With myItem
.Categories = myTask.Project
.Body = myNotesText
.Save
End With
myNotesText = “”
Next myTask
End Sub
Macro Name: Create_Menus
Option Explicit
Sub CreateMenus()
Dim cbrMain As CommandBar
Dim ctlMain As CommandBarControl
Dim ctlOLExport1 As CommandBarControl
Dim ctlOLExport2 As CommandBarControl
Dim ctlOLExport3 As CommandBarControl
Set cbrMain = Application.CommandBars.ActiveMenuBar
Set ctlMain = cbrMain.Controls.Add(Type:=msoControlPopup, Temporary:=True)
ctlMain.Caption = “Export to Outlook”
Set ctlOLExport1 = ctlMain.CommandBar.Controls.Add(Type:=msoControlButton)
With ctlOLExport1
.Caption = “Selection to Outlook tasks”
.OnAction = “Macro “”” & “Export_Selection_To_OL_Tasks”“”
End With
Set ctlOLExport2 = ctlMain.CommandBar.Controls.Add(Type:=msoControlButton)
With ctlOLExport2
.Caption = “Selection to Outlook appointments”
.OnAction = “Macro “”” & “Export_Selection_To_OL_Appointments”“”
End With
Set ctlOLExport3 = ctlMain.CommandBar.Controls.Add(Type:=msoControlButton)
With ctlOLExport3
.Caption = “Selection to Outlook notes”
.OnAction = “Macro “”” & “Export_Selection_To_OL_Notes”“”
End With
End Sub
I cannot get the code to work. It keeps giving me errors (specifically at Sub ProjUpdateFromOutlook()). It looks similar to the code I got from Clearly and Simple. I have run the code from clearly and simple; which allows me to do all that I need.
There is the update though. Someone did submit the code to get the Outlook to update when exporting from MS Project; however, I am a little over head on this code. I am going to talk to a college about this and see if he can help me. I will post the code as separate posts below to allow for threads off of them.
Thank you for the response John. I will be trying this on Monday and let you know. From my quick try some errors popped up; however, I think it is because it didn’t copy right.
Sub OutlookLink()
Dim appOL As Outlook.Application
Dim mspTask As MSProject.Task
Dim olTask As Outlook.TaskItem
Dim i As Integer
On Error GoTo objerror
Set appOL = GetObject(, “Outlook.Application”) ’ if Outlook is running, this line will work
resumeplace:
For Each mspTask In MSProject.ActiveProject.Tasks
If Not (mspTask Is Nothing) Then
Set olTask = appOL.CreateItem(olTaskItem)
'note that you can capture other Project fields into Outlook fields
olTask.Subject = mspTask.Name
olTask.Body = mspTask.Name
olTask.DueDate = mspTask.EarlyFinish
olTask.StartDate = mspTask.EarlyStart
olTask.Role = mspTask.ID
olTask.PercentComplete = mspTask.PercentComplete
olTask.Save
Set olTask = Nothing
End If
i = i + 1
Next
MsgBox “Cheryl, " & i & " tasks were exported to Outlook!”
Exit Sub
objerror: ’ if Outlook is not running, this will work
Err.Clear
Set appOL = CreateObject(“Outlook.Application”)
GoTo resumeplace
End Sub
Sub ProjUpdateFromOutlook()
Dim appOL As Outlook.Application
Dim mspTask As MSProject.Task
Dim objTask As Outlook.TaskItem, objTaskFolder As
Outlook.MAPIFolder
Dim objTaskItems As Outlook.Items, objNS As
Outlook.NameSpace
Dim i As Integer
i = 0
On Error GoTo objerror
Set appOL = GetObject(, “Outlook.Application”) ’ if Outlook is running, this line will work
resumeplace:
Set objNS = appOL.GetNamespace(“MAPI”)
Set objTaskFolder = objNS.GetDefaultFolder(olFolderTasks)
Set objTaskItems = objTaskFolder.Items
For Each objTask In objTaskItems
If objTask.Role? " Then
SelectTaskField Row:=objTask.Role, Column:=“Start”,
RowRelative:=False
SetTaskField Field:=“Start”,
Value:=objTask.StartDate, AllSelectedTasks:=True
SetTaskField Field:=“PercentComplete”,
Value:=objTask.PercentComplete, AllSelectedTasks:=True
SetTaskField Field:=“Finish”, Value:=objTask.DueDate,
AllSelectedTasks:=True
i = i + 1
Else
Debug.Print “Outlook task " & objTask.Subject & " has
no Role assigned for some reason.”
End If
Next
Application.CalculateAll
Set objTask = Nothing
MsgBox i & " Tasks Updated"
Set objTaskItems = Nothing
Set objTaskFolder = Nothing
Set objNS = Nothing
Exit Sub
objerror: ’ if Outlook is not running, this will work
Err.Clear
Set appOL = CreateObject(“Outlook.Application”)
GoTo resumeplace
End Sub
But you have other options for example:
- a version that exports the Project tasks as Outlook appointments
- another version of the Project Task/Outlook task code. It contains a “fix” for people who enter Outlook tasks manually in addition to those exported from Project to Outlook
This code is not mine. The credit goes to Rick Williams and if you need more info you can go to this website:
http://www.outlookcode.com/codedetail.aspx?id=1165
John Twiname
Although outside the scope of your original question, you can import an Excel list (from a Project file) to a calendar or use a VBA macro (in Project, Excel, or Access) to update an Outlook calendar.
By the way, when you save a project with the supplied data maps as an Excel file, the ID (record number) in your project file is included. This has been by design to let you export, update the Excel file, then, import the file to update your Project file. (This has been a feature for many versions.)
If you want all of your tasks to be in the SharePoint file, save the file as an Excel file and store the file in SharePoint.
If you want some of your tasks to be in the file, highlight the tasks and copy/paste to an Excel file that you store in SharePoint.
That is a very good point. I would also like to be able to send this task to a specific calendar and not my main one.
My only thought is that record identification and reconciliation will be a challenge. For example, how will you prevent duplicates of records being pushed into the shared calendar? How will the code know to perform an update to a record already pushed to the calendar versus adding a new record?
Best of luck with this. Keep us informed of your progress.
Friar
I have posted this question on Microsoft Answers as well.
I found a site (Clearly and Simply) that has the VBA code bundle; however, for an older version. I am going to test this out and see if I may gain the desired results.
