[RESOLVED] Updating outlook with via the GUID

[franconomic]

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

[Spoo]

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

[franconomic]

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:

'---------------------------------------------------------------------------------------
'

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

[Spoo]

Franco

This post is just to wrap your code with Code wrapper
... please use it in the future .. makes easier to read ..

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

And, oh yeah ... yikes ... please use indents ..

EDIT:
Sorry .. you already had indents.
As you know by now, without using the Code wrapper, they are all lost

Spoo

[Spoo]

Franco

This post is to "decipher" your description...

1. app allows user to track time in projects
2. projects consist of various tasks
3. when completed, user inputs task name and time into Outlook
4. at end of week, sent to boss
5. 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
6. currently if task is changed by app..
   
   • app senses this
   • a separate appt entry is made
7. 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

[franconomic]

Yoda says: Decipher well did you.

[Spoo]

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:

1. "original" -- first time sent to boss
2. "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

[franconomic]

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.

[Spoo]

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

[franconomic]

Ok, Thanks for the help and info.

[franconomic]

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.

'---------------------------------------------------------------------------------------

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

[Golgo1]

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?

[franconomic]

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?

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.