Solved

Groupwise - Automatic Mail Reminder

Posted on 2010-11-24
6
850 Views
Last Modified: 2013-11-12
I am automatically generating emails in groupwise through access vba forms.
I would like to set a tag on the mail which i send to pop up reminder in the persons mailbox if they don't open the mail within a set time.
Is it possible to do this whilst generating the original mail from an access form.
0
Comment
Question by:SweetingA
  • 3
  • 3
6 Comments
 
LVL 34

Expert Comment

by:PsiCop
ID: 34223905
I'm a bit behind on GroupWise (my last hands-on was with v6.5), but no, I don't think so. Not easily.

Basically, you need some way for an app to go into the database and check the delivery status of the E-Mail in question. Since you didn't bother to mention precisely how your app submits E-Mail to GroupWise for delivery, I'm assuming that you're doing it via SMTP. Which means that there are no message tracking features available as they are for messages originating natively in the GroupWise system.

However, if your app is working through some sort of API that allows you to submit an E-Mail directly to a POA as an authenticated user, fine, explain how your app works and it might be possible.

0
 

Author Comment

by:SweetingA
ID: 34224325
Attached is an extract of code i commonly use to send mails via Groupwise from vb.

Private Sub SendEmail_Click()
On Error GoTo GWError

    If Me.QualityTeam = "" Then
        MsgBox "Enter a recipient!"
        GoTo CMError
    End If
   
    Dim GW As GroupwareTypeLibrary.Application2
    Dim GWAccount As GroupwareTypeLibrary.Account2
    Dim GWMailbox As GroupwareTypeLibrary.Folder2
    Dim GWMessage As GroupwareTypeLibrary.Message
    Dim GWMessages As GroupwareTypeLibrary.Message2
    Dim GWAttachments As GroupwareTypeLibrary.Attachments
    Dim GWattachment As GroupwareTypeLibrary.Attachment
    Dim strQuality As String
    Dim strFile1 As String
    Dim strFile2 As String
    Dim strFile3 As String
    Dim strText As String
    Dim strText2 As String
    Dim strText3 As String
    Dim strText4 As String
    Dim strcc1 As String
    Dim strcc2 As String
    Dim strcc3 As String
    Dim strcc4 As String
    Dim strcc5 As String
    Dim strcc6 As String
    Dim strcc7 As String
    Dim strcc8 As String
    Dim strcc9 As String
    Dim strcc10 As String
       
    Set GW = CreateObject("NovellGroupWareSession") 'Open GroupWise
    Set GWAccount = GW.Login                        'Pickup login
    Set GWMailbox = GWAccount.MailBox
    Set GWMessage = GWMailbox.Messages.Add
   
    strQuality = "quality@cajaycare.com"
   
    Me.att1.SetFocus
    strFile1 = Me.att1.Value
    Me.att2.SetFocus
    strFile2 = Me.att2.Value
    Me.att3.SetFocus
    strFile3 = Me.att3.Value
   
    strText = "This email is to confirm that a change or concession request form has been saved in your peronalised directory awaiting review and signature"
    strText2 = "Only one signatory from the quality department is necessary, please check if the file has already been signed to avoid double signatures"
    strText3 = "Additional supporting information may be attached to this mail - please check the attachment window before exiting"
    strText4 = "Expediency would greatly be appreciated"
    strcc1 = Me.cc1  'Everyone on copy
    strcc2 = Me.cc2
    strcc3 = Me.cc3
    strcc4 = Me.cc4
    strcc5 = Me.cc5
    strcc6 = Me.cc6
    strcc7 = Me.cc7
    strcc8 = Me.cc8
    strcc9 = Me.cc9
    strcc10 = Me.cc10
     
    GWMessage.Subject = "Change Or Concession Request"
    GWMessage.BodyText = "Dear Colleagues," & vbCrLf & vbCrLf & strText & vbCrLf & vbCrLf & strText2 & vbCrLf & vbCrLf & strText3 & vbCrLf & vbCrLf & strText4
    GWMessage.Recipients.Add strQuality, , egwTo   'Sent to quality department
   
    If strcc1 <> "" Then
    GWMessage.Recipients.Add strcc1, , egwCC  'Copy to other parties
    End If
    If strcc2 <> "" Then
    GWMessage.Recipients.Add strcc2, , egwCC
    End If
    If strcc3 <> "" Then
    GWMessage.Recipients.Add strcc3, , egwCC
    End If
    If strcc4 <> "" Then
    GWMessage.Recipients.Add strcc4, , egwCC
    End If
    If strcc5 <> "" Then
    GWMessage.Recipients.Add strcc5, , egwCC
    End If
    If strcc6 <> "" Then
    GWMessage.Recipients.Add strcc6, , egwCC
    End If
    If strcc7 <> "" Then
    GWMessage.Recipients.Add strcc7, , egwCC
    End If
    If strcc8 <> "" Then
    GWMessage.Recipients.Add strcc8, , egwCC
    End If
    If strcc9 <> "" Then
    GWMessage.Recipients.Add strcc9, , egwCC
    End If
    If strcc10 <> "" Then
    GWMessage.Recipients.Add strcc10, , egwCC
    End If
   
    If strFile1 <> "f:\Quality Systems\Customer Concerns\Associated Documents\blank.doc" Then
    GWMessage.Attachments.Add (strFile1)   'pickup attachments
    End If
    If strFile2 <> "f:\Quality Systems\Customer Concerns\Associated Documents\blank.doc" Then
    GWMessage.Attachments.Add (strFile2)
    End If
    If strFile3 <> "f:\Quality Systems\Customer Concerns\Associated Documents\blank.doc" Then
    GWMessage.Attachments.Add (strFile3)
    End If
   
    GWMessage.Send
    MsgBox "Mail Sent"
CloseGW:
    Set GWMessage = Nothing
    Set GWAccount = Nothing
    Set GW = Nothing
Exit Sub
GWError:
    MsgBox "Error" & Err.Description & Err.Source
    Exit Sub
CMError:
    Exit Sub

End Sub
0
 
LVL 34

Accepted Solution

by:
PsiCop earned 500 total points
ID: 34226658
OK, if I'm reading the code correctly (never touched VB), you're using an API to login to the POA. Therefore, there exists in the PO a mailbox (Pickup?), and into which your app authenticates. Is that right?

Assuming that is so, then when you submit your E-Mail, it should appear in the Sent Items folder of the mailbox. Can you login to the PO as that mailbox and confirm that the generated E-Mails all exist (or at least the ones that haven't been trimmed by any retention policy enforced)?

If my assumption is correct and the E-Mails exist, then the answer to your Question becomes dependent on the API you're using. I'm going to touch on that with such an assumption in mind.

When your app submits the E-Mail to the POA, the message will be assigned a Message ID that uniquely identifies it within the PO database. The ID is a string of hexadecimal digits - I don't recall how many. If you go into the GroupWise fat client and examine the Properties of a message, one of the bits of information you'll see is that Message ID (I think that's how it's labeled).

I need to digress here a bit and talk about the GroupWise database structure. My knowledge is current through v6.5, but I doubt that they've made many changes since then. Anyway, bear with me.

Fundamentally, GroupWise is a database; its internal record format is dubbed LIN (I don't recall what that means, if anything) and is based on, if I remember correctly, SQLite. The contents of the DB (that is, the records) are encrypted, so even if you know the schema, you'd still need to deal with the encryption. The POA is a database engine that interacts with 25 database files in the OFMSG sub-directory of the PO directory structure.

Mailboxes are distributed pseudo-randomly across the 25 Message databases (MSG1.DB thru MSG25.DB), so unless there are fewer than 25 mailboxes in the Post Office, you can pretty much guarantee that the E-Mail for more than one particular mailbox is contained in a given OFMSG/MSGx.DB file.

In the OFUSER sub-directory, there will be (among other things) User database files, with the name format USERXXX.DB, there XXX is a three-hexadecimal-character ID, assigned by the POA, that uniquely identifies the mailbox within the PO. Those three characters should be part of the Message ID string I mentioned above, for all messages generated under that mailbox.

The individual OFUSER/USERxxx.DB file does NOT contain E-Mail. It contains pointer records to the user's E-Mail, their preferences, Rules, mailbox password, security settings, Folders, Proxy assignments, Folder Sharing and so forth. But the actual E-mail messages are in the MSGx.DB files (or in the OFFILES directory structure, since the body of E-Mail messages over 2KB in size are stored as an attachment).

OK, so where am I going with this. The Message ID is essentially a pointer to a specific record in a MSGx.DB database file. It also allows the POA to track messages within the GroupWise System.

The reason for the underlining is that no matter what, you are only going to be able to track message delivery in that context. If the E-Mail generated by your script ends up going out a GWIA (or any other Gateway, for that matter), message tracking stops there. You can determine that it was Transferred, and when that happened, but any sort of tracking past that point is next-to-impossible.

If your messages are being delivered to other mailboxes within the same GroupWise System, then this becomes a programming task. I don't know the API you're using, but basically what you'd want to do is to capture the Message ID for an E-Mail you send, at the time you generate it. I've never programmed anything like that, but it seems to me that it should be possible. Maybe it's part of the return from the function call, I don't know, but you need to have some way to capture that unique Message ID when your script sends the E-Mail.

After that, it should be a relatively simple case of setting up a cron job (or whatever automatic task scheduler Winblows uses) to login to the Post Office under the mailbox ID, retrieve the status information for that Message ID, and parse it to determine who has or has not opened it. I am assuming that the API you're using has functions to do those things - the GroupWise Client does them, and I would think that Novell would publish a full-featured API (and also guessing that is what you're using), so once you can uniquely ID the message you generated, it's really just a database query (and interpretation).

Has this helped?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:SweetingA
ID: 34250686
It will help if i can access the Message ID, i need to check that i have the authority to do that.

In any case for such a lot of information you deserve the points.

Thanks, i'll let you know if i manage it.
0
 

Author Closing Comment

by:SweetingA
ID: 34250698
The reason for the B is that it is not clear at this stage that the solution will work but its definately a step in the right direction.
0
 
LVL 34

Expert Comment

by:PsiCop
ID: 34430252
It'll be interesting to hear about the eventual solution.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Novell released its latest version of GroupWise a few weeks ago.  The version is 2012 and it only runs on Linux (SUSE Linux Enterprise Server 10/11 is the first choice and I'm not sure about other Linux distributions, such as Red Hat, Debian, etc.) …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now