Access and Groupwise 1

[Tarnish]

I need to tie in my Access 2003 application with some groupwise functionality, particularly the groupwise calendar but probably also the address book.

I've searched here and there, but don't really see much in the way of helpful info about the process for getting this done. Can anyone suggest a book, or a site or tutorial or anything that can help me get started in this area?

TIA,
T

 

[Remou]

Have you seen:

http://www.granite.ab.ca/access/email/novellgroupwise.htm

 

[Tarnish]

Thanks for the reply, Remou,

That's the one link I have found after doing a recent search for something related.

I've looked around that site and the links but it doesn't seem to give me anything useful...at least nothing that I know is useful. I may have missed something.

I think at the VERY minimum, I need some type of help file that Groupwise apparently doesn't provide that's like the vba help files you can call up and get all kinds of info on various objects/methods/properties. Even with that, I'd struggle, but at least I'd have a chance...

Thanks again,
T

 

[PHV]

some type of help file that Groupwise apparently doesn't provide
Even here ?

http://developer.novell.com/wiki/index.php/GroupWise_Object_API

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tarnish]

Thanks PHV,

I did see that last night but had sort of "misplaced" it. It's something. I guess I hoped there was more out there about the "process" of doing this in addition to the technical info.

I think I have an example of automation with other office programs, so maybe along with the above I can put together a reasonable attempt.

Tahnks again for the info,
T

 

[Eupher]

Tarnish,

I haven't done anything with the GW addressbook, but have done quite a bit with sending appointments to the GW calendar from Access. Some of it is code that I picked up on the Novell GW forums and customized, some of it is original. Depending on what your needs are, the coding can be simple or quite complex. Also, lying around somewhere I have a GW class module that I found - you might be able to customize it for your purposes; it was overkill for my project. Let me know what you want to do and I will try to help.

The GW API is better than nothing, but IMO it is not very friendly.

Ken S.

 

[Eupher]

p.s. never mind about the class module, it's Dmitri Furman's, the same one as at the link post by Remou above.

Ken S.

 

[Tarnish]

Eupher,

I'd be very interested in seeing any code you've used to add appointments to the calendar. I'm far more of a code "modifier" than a programmer, though I know enough to get some small things done on my own.

As for the address book, it's not absolutely essential (meaning just the appointment functionality would be fine on it's own). I already have the db setup with contacts so the address book wouldn't so much add a great deal of functionality as it would make things a bit more integrated with what's already used in the office. I had hoped to setup a situation with a shared address book where all the dbs contacts would go, and then they would be accessible direcly in groupwise, but not the end of the world if that doesn't work out.

Again, very interested in being able to set up appointments on the calendar. Ideally, I could have either a shared calendar or the appointments would be added to just the calendar of the person who is "assigned" the task.

Thanks a ton,
T

 

[Eupher]

Hi, Tarnish,

I'm at home and all my code is at work. I'll try to get something to you tomorrow.

Ideally, I could have either a shared calendar or the appointments would be added to just the calendar of the person who is "assigned" the task.

No reason you can't do both! In my case, I had our GW administrator create a calendar object attached to my account. This became our master calendar - every event was posted to it; but every appointment also parsed the list of names of those assigned to the event, and sent appointments to all of them as well. So we had a master calendar that everyone could proxy into (read-only, of course), and everyone also had a personal calendar to which was sent only the events to which he/she was assigned. I even went a step further: I made it an opt-in system; for users who wanted to receive the calendar entries (some didn't) I set a yes/no flag in my db. So the code parsed the list of users assigned to the event AND who had the flag set to YES.

Again, I'll try to get some code posted tomorrow.

Ken S.

 

[Tarnish]

Ken,

That sounds great! If I could get that accomplished in my project, I'd be gold.

I tried to do it with the toolbox controls. Every time I added something from groupwise, I got an error when I opened the form from then on. If I added three groupwise controls, I got three errors when the form opened (referring to a .dll file Access said it couldn't find). Some of it worked. I could, for example, see my groupwise calendar within my access application (after dismissing the error popups), but I couldn't add any appointments to it. I could view the address books, but I didn't have any associated email functionality.

The dll file the error messages reference I can find on my local machine, but access won't let me add it as a reference for some reason and it doesn't tell me why.

At home, I've spent a short while trying to integrate some other pim software (works, haven't tried outlook yet) since I don't have groupwise at home (yet), and it seems like I get some bigger "pieces" into access easier and without the error messages. I'm guessing, maybe, the error messages have soemthing to do with the network setup at work (it's a huge system and, though I don't know it that well at all, I'm assumming it's pretty darn segmented in terms of access to files/drives), but even if I didn't get the error messages I'm not getting enough of the pieces so far to do anything useful. I'm thinking maybe that dll file is also located on a drive in the network where most of groupwise resides, aand that's the one I need....but I dunno. I could be way offbase there.

Anyway, I look forward to seeing what you have. I really need to hit this one out of the park, if you know what I mean...

Thanks again,
Chuck

 

[Eupher]

Chuck,

Here's a bit of code for you to play with:

Private Sub cmdSendToGW_Click()
On Error GoTo Err_cmdSendToGW_Err

Dim myApp As Application2
Dim myAccount As Account3
Dim myCalendar As Folder3
Dim myAppt As Appointment5
Dim i As Integer

[green]'instantiate object and login[/green]
Set myApp = New Application2
Set myAccount = myApp.Login("[blue]myuserid[/blue]", , "[blue]mypassword[/blue]")
Set myCalendar = myAccount.Calendar

[green]'create an empty message with class as appointment[/green]
Set myAppt = myCalendar.Messages.Add("GW.MESSAGE.APPOINTMENT")

[green]'set appointment subject[/green]
myAppt.Subject = Me.EventSubject

[green]'specify start and end dates/times[/green]
myAppt.StartDate = Me.StartDateTime
myAppt.EndDate = Me.EndDateTime

[green]'make appointment appear on calendar[/green]
myAppt.OnCalendar = True

[green]'set location of appointment[/green]
myAppt.Place = Me.EventLocation

[green]'insert text of appointment message[/green]
myAppt.BodyText = Me.EventNotes

[green]'add recipients[/green]
myAppt.Recipients.Add ("[blue]myrecipid1[/blue]")
myAppt.Recipients.Add ("[blue]myrecipid2[/blue]")

[green]'resolve recipient addresses, display message and delete if not found[/green]
For i = myAppt.Recipients.Count To 1 Step -1
    On Error Resume Next
    myAppt.Recipients(i).Resolve
    If Not myAppt.Recipients(i).Resolved = egwResolved Then
        MsgBox "'" & myAppt.Recipients(i) & "' is not a valid address." & vbCrLf _
        & "Appointment will not be sent to that address."
        myAppt.Recipients(i).Delete
    End If
Next i

[green]'send appointment[/green]
myAppt.Send

[green]'release variables[/green]
Set myAppt = Nothing
Set myCalendar = Nothing
Set myAccount = Nothing
Set myApp = Nothing

[green]'display message[/green]
MsgBox "Appointment added!"

Exit_cmdSendToGW:
    Exit Sub

Err_cmdSendToGW_Err:
    MsgBox Err.Number & ", " & Err.Description
    Err.Clear
    Resume Exit_cmdSendToGW:

End Sub

There are several things to remember for this to work:

1) You must install the Groupwise client on the machine that is running the code. I think at a minimum the GW dll's must be registered on the machine.

2) Once GW is installed, you must set references to: a) Groupwise Client Library; and b) GroupWare type library. Once this is done, all the GW objects, functions, events, methods, properties, etc. are available - but NOT necessarily the GW ActiveX objects. If your goal is simply to send appointments, you don't need the ActiveX controls anyway, you can do it all in code.

The code I have posted is a pretty simple example. You may get much more complex, depending on what you wish to do. For instance, you'll note that this example hard-codes the username, password, and recipients (the blue code). Not secure at all, and very impractical if you want to send to a dynamic list of addresses. In my app I have the GW userid and password stored in a table (with a password mask set on the password field), then I open a recordset and assign the userid and password to string variables, then use the variables for the login arguments. Still not terribly secure (anyone with access to the .mdb file could easily find the password with just a tiny bit of code - which is one of the reasons I deploy an .mde file) - but better than hard-coding. And for the address list I open a recordset based on the members assigned, then loop through and put the addresses in an array, then build the recipient list from the array. And of course the body text of the appointment item can be as involved as you want to make it.

If the events sometimes change (date/time change, personnel change) you will want to avail yourself of the MessageID property of the sent item - that is, store it in your table, so you have a way to search the message store to retract/delete/empty and resend with the new data.

Although there is no help with the GW dll's, remember that the Object Browser and Intellisense are your friends.

Good luck. If you get stumped, post back and I will try to help.

Ken S.

 

[Tarnish]

Ken,

Thanks a ton! I can't really do much with this until work on Monday, but I'm really greatful for the help.

I'm curious about the groupwise dlls you mentioned. While I know the type library is listed on my office box (along with maybe 6-8 more or so), I don't recall seeing anything that stuck out as being related to activeX objects. To be honest, it never really crossed my mind that the activeX (which I assume refers to the toolbox controls) required any particular references but now that I think about it it makes perfect sense.

Anyway, thanks again for the code. Look forward to giving it a spin and seeing what I can do with it. If it works out, hopefully I can use it to learn to extend into other areas in the same manner. I'm concerned about the whole password thing, but I'll cross that bridge at a later time.

Thanks,
Chuck

 

[Eupher]

Chuck,

I believe the userid and password arguments are optional. I think if you leave the password argument out, GW will prompt you for it.

Ken S.

 

[Tarnish]

Ken,

Have another question or two for you if you are still keeping up with this thread, but first let me say thanks again.

I managed to get that code working at least to the extent I can now push an issue into groupwise as an appointment. That's further than I thought I would get when I started. It's raised several issues, because it uses the groupwise address book to send the addresses and my application isn't, as of now, using the address book to store names. I'd still love to be able to set a default address book for people to add names to and to otherwise use as the basis for combo boxes in forms and for just storing info about people related to the application, but I'm not sure I can extrapolate what you've given me into something like that (but I'm going to try).

Anyway, the question: You said this in one of your posts:

"If the events sometimes change (date/time change, personnel change) you will want to avail yourself of the MessageID property of the sent item - that is, store it in your table, so you have a way to search the message store to retract/delete/empty and resend with the new data."

Well, that's where I am now on this. I can send the appointments, but if I change the date or any other info it doesn't delete the first appointment.

I don't suppose you can be any more specific about how I can accomplish the above. I'm thinking what you are saying is to add a field to my table called messageID to store the first (or current) appointment, but I really have no idea what else I'd do.

Any insight (or code) would be helpful. Regardless, thanks again for getting me going in the right direction. It's been most helpful.

Chuck

 

[Eupher]

Hi, Chuck,

I'm thinking what you are saying is to add a field to my table called messageID to store the first (or current) appointment

Exactly! Add a field to your table, name it whatever you wish. Mine is called "GWMessageID". Then change your code as follows to capture the message ID and save it in your table for future reference:

Private Sub cmdSendToGW_Click()
On Error GoTo Err_cmdSendToGW_Err

Dim myApp As Application2
Dim myAccount As Account3
Dim myCalendar As Folder3
Dim myAppt As Appointment5
[highlight]Dim mySentAppt As Message3[/highlight]
Dim i As Integer

[red]...intervening code is unchanged until...[/red]

[green]'send appointment[/green]
[highlight]Set mySentAppt = myAppt.Send
Me![blue]GWMessageID[/blue] = mySentAppt.MessageID
DoCmd.RunCommand acCmdSaveRecord[/highlight]

[green]'release variables[/green]
[highlight]Set mySentAppt = Nothing[/highlight]
Set myAppt = Nothing
Set myCalendar = Nothing
Set myAccount = Nothing
Set myApp = Nothing

[red]...the rest of the sub is the same...[/red]

Then it's a matter of searching the message store for that message ID, retracting/deleting and sending the new appointment. The code I'm using for that is a little involved. I'm trying to distill it down into something simpler for you. I don't know if I will get to it before Thanksgiving, but if not I'll try to post some more early next week.

Ken S.

 

[Tarnish]

Ken,

Awesome! The other code was pretty easy to modify, though I sort of got lucky on one "educated" guess. Other than that and a tad of 'varible' brainlock, it worked right away.

I found this too in all my travels via google...havent' had much time to look at it yet but maybe it will help in the future:

http://developer.novell.com/wiki/images/6/61/GWDevGuide.pdf

Then it's a matter of searching the message store for that message ID, retracting/deleting and sending the new appointment. The code I'm using for that is a little involved. I'm trying to distill it down into something simpler for you. I don't know if I will get to it before Thanksgiving, but if not I'll try to post some more early next week.

That would be great! After Thanksgiving is fine...just whenever you can, if you can.

I've got a lot of "small" things that have accumulated over the course of development that I need to address anyway...plenty of stuff to keep me busy

Thanks again and I'll keep an eye on the thread. Have a great Thanksgiving!

Chuck

 

[Eupher]

Chuck, here ya go...

Private Sub cmdSendToGW_Click()
On Error GoTo Err_cmdSendToGW

Dim myApp As Application2
Dim myAccount As Account3
Dim myCalendar As Folder3
Dim myAppt As Appointment5
Dim mySentAppt As Message3
Dim myApptMessages As MessageList
Dim myCurAppt As Message3
Dim i As Integer

[green]'save the current record[/green]
DoCmd.RunCommand acCmdSaveRecord

[green]'instantiate Groupwise object and login[/green]
Set myApp = New Application2
Set myAccount = myApp.Login("[blue]myuserid[/blue]", , "[blue]mypassword[/blue]")
Set myCalendar = myAccount.Calendar

[green]'check for existing GW message ID, and if found, retract and delete appointment[/green]
If Not IsNull(Me!GWMessageID) Then
    [green]'set the message list to only appointment items[/green]
    Set myApptMessages = myCalendar.FindMessages("APPOINTMENT")
    [green]'loop through appointments, find the one that matches the GWMessageID[/green]
    For i = 1 To myApptMessages.Count
        Set myCurAppt = myApptMessages.Item(i)
        If Me!GWMessageID = myCurAppt.MessageID Then
            [green]'retract appointment item from all mailboxes[/green]
            myCurAppt.Retract
            [green]'delete appointment item from my calendar[/green]
            myCurAppt.Delete
            Exit For
        End If
    Next i
End If

[green]'create an empty message with class of appointment[/green]
Set myAppt = myCalendar.Messages.Add("GW.MESSAGE.APPOINTMENT")

[green]'set appointment subject[/green]
myAppt.Subject = Me.EventSubject

[green]'specify start and end times[/green]
myAppt.StartDate = Me.StartDateTime
myAppt.EndDate = Me.EndDateTime

[green]'make appointment appear on calendar[/green]
myAppt.OnCalendar = True

[green]'set location of appointment[/green]
myAppt.Place = Me.EventLocation

[green]'insert text of appointment message[/green]
myAppt.BodyText = Me.EventNotes

[green]'add recipients[/green]
myAppt.Recipients.Add ("[blue]userid1[/blue]")
myAppt.Recipients.Add ("[blue]userid2[/blue]")

[green]'resolve recipient addresses, display message and delete if not found[/green]
For i = 1 To myAppt.Recipients.Count
    On Error Resume Next
    myAppt.Recipients(i).Resolve
    If Not myAppt.Recipients(i).Resolved = egwResolved Then
        MsgBox "'" & myAppt.Recipients(i) & "' is not a valid address." & vbCrLf _
        & "Appointment will not be sent to that address."
        myAppt.Recipients(i).Delete
    End If
Next i

[green]'send appointment[/green]
Set mySentAppt = myAppt.Send
[green]'capture message ID and save the record[/green]
Me!GWMessageID = mySentAppt.MessageID
DoCmd.RunCommand acCmdSaveRecord

[green]'release variables[/green]
Set myCurAppt = Nothing
Set myApptMessages = Nothing
Set mySentAppt = Nothing
Set myAppt = Nothing
Set myCalendar = Nothing
Set myAccount = Nothing
Set myApp = Nothing

[green]'display message[/green]
MsgBox "Appointment added!"

Exit_cmdSendToGW:
    Exit Sub

Err_cmdSendToGW:
    MsgBox Err.Number & ", " & Err.Description
    Err.Clear
    Resume Exit_cmdSendToGW:

End Sub

Let me know how this works for you.

Ken S.

p.s. in your references, you only need the reference to the Groupwise type library, not the Groupwise Client Library (my goof).

 

[Eupher]

Chuck,

I looked at the GW developer's guide you linked to. It looks like it would be very useful. There are a lot more explanations and code samples than I found in the API reference I was using. Too bad my office has moved to a different groupware solution - now I'm redesigning my db to work with Outlook/SynQ/Mirapoint. :-(

Ken S.

 

[Tarnish]

Ken,

Thanks for the code! I've got it to work, so I've reached that plateau. I'm really greatful for that. Had a little trouble with one error message but I remembered you could comment out the error handler and at least you'd see where in the code things were breaking (if, as in this case, you were just getting the error message and no vbe window jump).

I've got two other related (to each other) issues, but I may be able to figure out something to tidy them up a little. Even without it I'm in much better shape than I was.

If I delete a record, or I change the "assigned" person's name (the one that gets the appointment on their calendar), then the appointments aren't removed before the delete (or the repost to the new "assigned").

I think maybe I can pull out parts of the code and add it to the "delete record" button to remove the old appointment when the record is deleted, and maybe add a "delete appointment" type button that could be used when changing the "assigned" person, but I haven't thought much about the ramifications of either yet. I'm probably going to end up making some design changes to my main form anyway (which is a pain) in order to accomodate more information in the appointments that are sent to GW as well.

The only other "issue", if you can call it that, is the number of pop-up "data has changed, save?" windows I get during posting an appointment. The way I've set things up, you have to confirm all changes. I don't want to change that, but that means both the acsaverecords in that code trigger the msgbox window that I added in to my form to make sure people were always aware if they made a change or not. I'm hoping to find some way to auto-respond via code to those message boxes just in the case of this GW code running, but I haven't found an answer yet.

Too bad you couldn't get any benefit from that link I posted. I was hoping to give a little something back for all your great help. I firebombed the "give poster a star" links when I noticed them today, for what that's worth

I'm still going to give a go at the address book (hopefully) before I have to wrap this thing up and start cleaning up things for institutional testing. That's probably well above my head, but I'm hoping with this as a roadmap and anything I can find in that developer's guide from the link I posted maybe I can get something accomplished. That would assure the project's success, imo, but I think it's a fairly robust solution from a novice like myself as it is.

Man, again, I can't tell you how much you've done for me. I'm even thinking about making a career out of dba or design or something (a second career, that is), but I need some training/credentials for that to work so we'll see.

It's late and it's been a long week (already), but I'll keep an eye on the thread, still. Once I get a chance to look at the addressbook situation, I may throw another post here just to update things. I wouldn't ask for anything else, you've gone way past the "call of duty" as it is, but if I get it to work it will be largely thanks to the help you've already given me.

Again, thanks...if there's anything I can ever do, let me know. I'm a jouneyman web designer, pphotoshop, flash, Frontpage, Dreamweaver, psp, dhtml, xml, xhtml, yada yada yada guy. If it can be copied, clicked, or grifted, I've likely done it at least once hehe.

Chuck

PS: too whipped to proofread..hope it's "legible

 

[Eupher]

Hi, Chuck,

Glad I could help. Stars are always appreciated. And I plan on keeping the GW resource you posted anyway, just in case. I haven't talked to anyone who is real happy about the switch to Mirapoint, so who knows, maybe we will switch back. Not holding my breath, though.

Yes, you will want to implement a method for deleting the associated appointment when you delete a record. As you suggested, you could just use the part of the code that searches the message store and deletes, and add that to your delete record button. The code I posted should cover instances where parameters of the event change and you need to resend the item - the Retract method removes the original item from all recipients' calendars.

I posted the GW code as a single sub, but parts of it could be (and arguably should be) coded as separate subs - for instance, the bit that searches the message store and retracts/deletes could be moved into a separate routine. Depending on how many variations on the theme you need, you may wish to move some of the code off the form and into a module.

How is your db warning users that data has changed? Are these standard Access warnings? Or message boxes that you have created? If they are standard Access warning messages, you can turn them off and back on again in code:

DoCmd.SetWarnings False
[red]...warnings are suppressed until...[/red]
DoCmd.SetWarnings True

Good luck with your project. As I said, I haven't messed with the address book much (at all, really) but I'm not against giving it a try. Let me know what you are trying to do, if you want, and I will see what I can come up with.

Ken S.