Printable View
Greetings guys,
I am trying to figure out how to update outlook appointments via the GUID that is produced. Currently, I store the GUID in a database and would like to update outlook via a loop for a number of appointments grouped by individual in my program. Any insight and advice would be appreciated. Thanks
Franco
I'm not an Outlook user, but am a database user,
so I may be of some use. Could you elaborate a
little bit on ..
- what is a GUID
- what database are you using?
- what are you having problems with?
- getting the name
- selecting the group in the database based on that name
- writing the results to Outlook
- something else
- what have you coded so far?
Spoo
Well Spoo,
I have created a application for my company that allow the user to track thier time on various work related projects. Projects consist of various tasks. Once the task is completed they have the option to input this task name and time they worked on the task in to outlook. At the end of the week they send their outlook appointment report to the main boss so that he can evaluate how many hours they actuallly worked. Currently, when the user press the update outlook button, appointments (an Outlook ID (GUID) is created when an appointment is created and saved to a database to facilitate updating) in outlook are created. However, sometimes the task that was just completed can be reopened and ammended in the program itself. Therefore, theres is a need to update outlook by deleting what was placed in their previously with a new time or amended name of the task in which whose GUID was saved in a database. Currently, if the task is changed within the program, the program senses this and when the user press the update outlook button a seperate appointment entry is made with the same Task name and time. My goal is to find some way to update outlook via the Outlook ID (GUID) that I saved in the program database without having it make an all new appointment entry. Thanks again for your insight.
Code:CODE:
'---------------------------------------------------------------------------------------
'
Private Sub cmdUpdateOutlook_Click()
On Error GoTo UpdateError
'If the user didn't select his or her own items he/she will not be able to update outlook.
If (UCase(frmTimeTrackerMain.Userid)) <> UCase((fgProjects.TextMatrix(fgProjects.Row, 2))) Then
Call MsgBox("Selection of one of your projects is necessary to proceed.", vbInformation, "Cannot Update Outlook")
Exit Sub
End If
Dim rsUpdateOutlook As New ADODB.Recordset
Dim ProjectID As Integer
Dim Appt As Outlook.AppointmentItem
Dim Xcounter As Integer
Checker = True
Dim X As Integer
X = 0
Dim sSQL As String
'Warns the user if a project has not been selected.
If fgProjects.TextMatrix(fgProjects.Row, 0) = "ProjectID" Then
Call MsgBox("Please select a project.", vbInformation, "Selection Needed")
Exit Sub
End If
ProjectID = CInt(fgProjects.TextMatrix(fgProjects.Row, 0))
sSQL = "Select * from Tasks where (TaskProjectID = '" + CStr(ProjectID) + "' or TaskOwnerID = '" + frmTimeTrackerMain.Userid + "') and TaskChanged = 'True' and TaskStatus in ('Completed')"
With rsUpdateOutlook
.ActiveConnection = frmTimeTrackerMain.cnTT
.LockType = adLockReadOnly
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.Source = sSQL
.Open
While Not .EOF
' 'Delete the entries from Outlook if they have changed in order to input new ones.
' If apptdel.Start = !TaskActStartDate Then
' apptdel.Delete
' End If
Checker = False
Xcounter = Xcounter + 1
Set Appt = Application.CreateItem(olAppointmentItem)
Appt.Subject = !TaskName
'Time and Date Information
'---------------------
Dim DTPDateStart, DTPTimeStart As String
DTPDateStart = Trim(Left(!TaskActStartDate, 10))
'As a result of completing a task manualy in the edit task form there may not be any time
'This if then is used to prevent errors from occuring when the information is inputed into
'outlook.
If Not DTPTimeStart = Trim(Right(!TaskActStartDate, 11)) Then
DTPTimeStart = Trim(Right(!TaskActStartDate, 11))
End If
'----------------------------
'---------------------
Dim DTPDateEnd, DTPTimeEnd As String
DTPDateEnd = Trim(Left(!TaskActEndDate, 10))
If Not DTPTimeEnd = Trim(Right(!TaskActEndDate, 11)) Then
DTPTimeEnd = Trim(Right(!TaskActEndDate, 11))
End If
'----------------------------
Appt.Start = CStr((DTPDateStart) + " " + DTPTimeStart)
Appt.End = CStr((DTPDateEnd) + " " + DTPTimeEnd)
Appt.AllDayEvent = False
Appt.BusyStatus = olBusy
Appt.Body = ""
Appt.Location = ""
Appt.ReminderSet = False
Appt.Categories = "Time Tracker Task"
Appt.Save
Dim Guid As String
Guid = Appt.EntryID
Call StoreGUIDinDatabase(!TaskID, Guid)
Set Appt = Nothing
.MoveNext
Wend
.Close
End With
If Checker = True Then
lblMessage.Visible = True
lblMessage.Caption = "No completed visible/hidden Task(s) to enter at this time."
Else
lblMessage.Visible = True
lblMessage.Caption = CStr(Xcounter) + " completed visible/hidden Task(s) has been entered/updated."
End If
UpdateError:
Resume Next
End Sub
Franco
This post is just to wrap your code with Code wrapper
... please use it in the future .. makes easier to read .. :)
And, oh yeah ... yikes ... please use indents .. :)Code:Private Sub cmdUpdateOutlook_Click()
On Error GoTo UpdateError
'If the user didn't select his or her own items he/she will not be able to update outlook.
If (UCase(frmTimeTrackerMain.Userid)) <> UCase((fgProjects.TextMatrix(fgProjects.Row, 2))) Then
Call MsgBox("Selection of one of your projects is necessary to proceed.", vbInformation, "Cannot Update Outlook")
Exit Sub
End If
Dim rsUpdateOutlook As New ADODB.Recordset
Dim ProjectID As Integer
Dim Appt As Outlook.AppointmentItem
Dim Xcounter As Integer
Checker = True
Dim X As Integer
X = 0
Dim sSQL As String
'Warns the user if a project has not been selected.
If fgProjects.TextMatrix(fgProjects.Row, 0) = "ProjectID" Then
Call MsgBox("Please select a project.", vbInformation, "Selection Needed")
Exit Sub
End If
ProjectID = CInt(fgProjects.TextMatrix(fgProjects.Row, 0))
sSQL = "Select * from Tasks where (TaskProjectID = '" + CStr(ProjectID) + "' or TaskOwnerID = '" + frmTimeTrackerMain.Userid + "') and TaskChanged = 'True' and TaskStatus in ('Completed')"
With rsUpdateOutlook
.ActiveConnection = frmTimeTrackerMain.cnTT
.LockType = adLockReadOnly
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.Source = sSQL
.Open
While Not .EOF
' 'Delete the entries from Outlook if they have changed in order to input new ones.
' If apptdel.Start = !TaskActStartDate Then
' apptdel.Delete
' End If
Checker = False
Xcounter = Xcounter + 1
Set Appt = Application.CreateItem(olAppointmentItem)
Appt.Subject = !TaskName
'Time and Date Information
'---------------------
Dim DTPDateStart, DTPTimeStart As String
DTPDateStart = Trim(Left(!TaskActStartDate, 10))
'As a result of completing a task manualy in the edit task form there may not be any time
'This if then is used to prevent errors from occuring when the information is inputed into outlook.
If Not DTPTimeStart = Trim(Right(!TaskActStartDate, 11)) Then
DTPTimeStart = Trim(Right(!TaskActStartDate, 11))
End If
'----------------------------
'---------------------
Dim DTPDateEnd, DTPTimeEnd As String
DTPDateEnd = Trim(Left(!TaskActEndDate, 10))
If Not DTPTimeEnd = Trim(Right(!TaskActEndDate, 11)) Then
DTPTimeEnd = Trim(Right(!TaskActEndDate, 11))
End If
'----------------------------
Appt.Start = CStr((DTPDateStart) + " " + DTPTimeStart)
Appt.End = CStr((DTPDateEnd) + " " + DTPTimeEnd)
Appt.AllDayEvent = False
Appt.BusyStatus = olBusy
Appt.Body = ""
Appt.Location = ""
Appt.ReminderSet = False
Appt.Categories = "Time Tracker Task"
Appt.Save
Dim Guid As String
Guid = Appt.EntryID
Call StoreGUIDinDatabase(!TaskID, Guid)
Set Appt = Nothing
.MoveNext
Wend
.Close
End With
If Checker = True Then
lblMessage.Visible = True
lblMessage.Caption = "No completed visible/hidden Task(s) to enter at this time."
Else
lblMessage.Visible = True
lblMessage.Caption = CStr(Xcounter) + " completed visible/hidden Task(s) has been entered/updated."
End If
UpdateError:
Resume Next
End Sub
EDIT:
Sorry .. you already had indents.
As you know by now, without using the Code wrapper, they are all lost
Spoo
Franco
This post is to "decipher" your description...
- app allows user to track time in projects
- projects consist of various tasks
- when completed, user inputs task name and time into Outlook
- at end of week, sent to boss
- currently .. when user presses cmdUpdateOutlook
- appointments is created
- however, task can be reopened by the app
- so, need to delete what was there
- replace it with new info
- currently if task is changed by app..
- app senses this
- a separate appt entry is made
- goal
- find a way to update via Outlook GUID
- without having to make an all new appt entry
Correct me if I misinterpretted anything.
Spoo
Yoda says: Decipher well did you.
Franco
If it's good enough for Yoda, then that's good enough for me
He's one crafty dude.
Could you post some example screenshots of the database:
- "original" -- first time sent to boss
- "extra" -- when task is re-opened, what your new entry looks like
I take it that you would like to
- eliminate the need for an entry 2 ("extra") ... and instead,
- just modify entry 1 ("original") ... and perhaps flag that it was modified
Spoo
I have included attachments of the database and the program form. So when a task is first created the TaskOutlookID is null. When the task is inputted into outlook, the program records the GUID (TaskOutlookID) and the TaskChanged value is set to False. However, if the user changes anything in the same task and saves it (TaskChanged = True), I would like to use the recorded GUID to some how find the GUID in outlook to delete the same task and replace it with the same new amended task.
Franco
OK.. that attachment helps.
However, I'm afraid, as a non-user of Outlook, that I don't
have anything to offer regarding communicating with it.
If you don't get help from any other guys here, you might
consider the Office Development Forum. I see a couple of
threads there regarding Outlook.
Spoo
Ok, Thanks for the help and info.
SOLVED:
So basically, when the updates begin I will first call this procedure to delete all GUID/Entry IDs that are returned. (Those that have changed, has been completed and already have a GUID in the database) Then the program will re-input them back into outlook, thus preventing duplication.
Code:'---------------------------------------------------------------------------------------
Private Sub PerformDeleteFromOutlook(ByVal Guid As String)
Dim objOutlookApp As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim objappts As Outlook.Items
Dim ok As Boolean
ok = False
Dim Item As Integer
Item = 1
Set objOutlookApp = New Outlook.Application
Set objNameSpace = objOutlookApp.GetNamespace("mapi")
Set objFolder = objNameSpace.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderCalendar)
Set objappts = objFolder.Items
While ok = False
Dim objappt As Outlook.AppointmentItem
Set objappt = objappts.Item(Item)
If objappt.EntryID = Guid Then
objappt.Delete
ok = True
End If
If Item = objappts.Count And ok = False Then ok = True
If ok = False Then
Item = Item + 1
End If
Wend
End Sub
I have to say I really like the idea. grabbing the task GUIDs right out of outlook, cool beans.
I'm sure a custom app can provide much more flexibility for you, but I have to wonder...
wouldn't MS Project do much of the same thing... also, if it is just a matter of getting your calendar events (time worked) up to your boss, can't you just setup a public calendar on the network that everyone updates (only able to see thier own of course)?
Just a thought. I still like the GUID aspect of talking with outlook....
We need to do some outlook 'manipulation' in the near future, I may look at doing it this way instead of relying on APIs (and hoping they exist)
Would you have any recommended sites or code or declares that might get us in the right direction?
Quote:
Originally Posted by Golgo1
Well, I did it the following way since this was the way the boss wanted it. Also, we do have a public calender where we mostly input when we will be out for being sick, having an doctors appointment, or are on vacation. However, we also utilize our own private outlook calender to show that we are actually doing work and not f'ing around. And at the end of the week it is either printed or emailed to the boss sectary for his evaluation. Again, that's the way they wanted it so, cool beans. Unfortunately, I don't know of any sites to recommend off hand.