Solved: Extracting groupwise e-mails

[Maurits]

Hello gang,

First time poster here, decided to register here rather than harass individual members with e-mails. (JOrzech and kpuls were particularly lucky in that regard.)

As to my problem, i just know the solution will be painfully simple, but this really isn't my cup of tea (or coffee, or any other beverage, even!)

A while back, i discovered i had to send an e-mail to every user on the network with a list of the applications they have and ask them wether or not they actually use it.

As there are about 600 users here, i adapted a macro found on the web which allows you to send e-mails through groupwise, and saved me a lot of mind-numbing and RSI-inducing work.

Now on to my problem, every one of those 600 users is going to e-mail me back with their list of applications, and since i have never interfaced with groupwise before, i have *no* idea how to put those e-mails into a .word doc.

I spend the majority of two days struggling with VBA trying to get into groupwise's mailbox without any success.

Once i get the data into word, i'll be just fine, i know enough VBA to process all that, but i just can't seem to make the link between Word/Excel and groupwise work.

Any help would be greatly appreciated.

Yours gratefully,
-Maurits
*edit* Rats, forgot to list the specifications: groupwise 6.5.1, word 97 SR2

[Killian]

Hi Maurits and welcome to VBAX

I have to admit to knowing nothing about Groupwise but I can only assume it has some kind of incoming mail processing capability (like Outlook) such as rules, that will allow you to identify the mails you want (by subject or whatever) and save them to a folder, perhaps as text.
Then in Word, you can target that folder and process all the text files into one document. (Even if you can manually select them and save them as text files, that will help - streaming text into Word from a folder is the easy part)

Another option might be to run Outlook as your mail client with a connection addin for using the Groupwise server (i do a similar thing at work where they insist on using Lotus Notes)

Sorry i can't be more help than that

[Ken Puls]

Hi Maurits, and welcome to VBAX!

I've never tried to do what you're after, but I'm sure it can be done. I'm kind of curious now...

I dont' have time to try today, but I may be able to look into this tomorrow. As long as you're not in an urgent hurry for an answer, I'd be pleased to give it a shot. FYI, I also use Groupwise 6.5, and have access to a server that runs Word 97, SR-2.

So, here's some questions.
-How are you segregating the emails to export to Word? All emails in a specific file in your cabinet?
-What format are the emails coming back in? Are they just typing text into the email, or are they attaching a Word doc of their own?
-What do you want to happen once you've exported the contents? Delete the email, move it to another file?

[Ken Puls]

Hi Maurits,

Okay, we may need to tweak this a little to do what you need, but the premise of the code is as follows...

It will create a new Groupwise instance if needed, log in to the user's mailbox, and loop through each email in a specified folder. If it finds an attachement with a word document, it will save it into a folder that you specify in the code.

I made an assumption that you sent out a Word file, but it can very easily be adapted to extract "xls" files. If you need to extract the message body, that will require a bit of adjustment to the code.

Here's the code:

[vba]Option Explicit
Private ogwApp As GroupwareTypeLibrary.Application
Private ogwRootAcct As GroupwareTypeLibrary.Account

Sub Groupwise_SaveAttachToFile()
' Date Created : 4/20/2005 10:25
' Macro Purpose: Save all attachments of specified file type into a
' user specified folder using Groupwise
' NOTE: This code requires a reference to the "GroupWare Type Library"

Dim ogwFolder As Folder, _
ogwFoundFolder As Folder, _
i As Long, _
sCommandOptions As String, _
sMailPassword As String, _
sLoginName As String, _
sFolderToSearch As String, _
sFileType As String, _
sSavePath As String, _
ogwMail As Mail

'Change required variables here!
sLoginName = "Your Login ID Here"
sFolderToSearch = "The Name Of The Folder The Emails Are In"
sSavePath = "C:\Temp" 'do not add trailing \
sFileType = "doc"

'Set application object reference if needed
If ogwApp Is Nothing Then 'Need to set object reference
DoEvents
Set ogwApp = CreateObject("NovellGroupWareSession")
DoEvents
End If

'Create connection/login to email account
If ogwRootAcct Is Nothing Then 'Need to log in
'Login to root account
If Len(sMailPassword) Then 'Password was passed, so use it
sCommandOptions = "/pwd=" & sMailPassword
Else 'Password was not passed
sCommandOptions = vbNullString
End If

Set ogwRootAcct = ogwApp.Login(sLoginName, sCommandOptions, _
, egwPromptIfNeeded)
DoEvents
End If

'Search all mail items, and save any matching attachments to the
'specified directory
For Each ogwMail In ogwRootAcct.AllFolders.ItemByName(sFolderToSearch).Messages
With ogwMail
If .Attachments.Count = 0 Then
'No attachments, so do nothing
Else
'Attachments found. Save desired type to specified folder
For i = 1 To .Attachments.Count
If Right(.Attachments(i).Filename, 3) = sFileType Then
.Attachments(i).Save _
sSavePath & "\" & .Attachments(i).Filename
End If
Next i
End If
End With
Next ogwMail

'Release all objects before closing
Set ogwRootAcct = Nothing
Set ogwApp = Nothing
DoEvents
End Sub[/vba]

The first two lines after Option Explicit must sit at the top of the module (after Option Explicit), so I'd recommend you just copy all of the above to the top of a new module.

The parts you need to change are all in this section:
[vba] 'Change required variables here!
sLoginName = "Your Login ID Here"
sFolderToSearch = "The Name Of The Folder The Emails Are In"
sSavePath = "C:\Temp" 'do not add trailing \
sFileType = "doc"[/vba]

sLoginName should be your mailbox ID
sFolderToSearch should be the name of the Groupwise folder where you've stored all the emails in question
sSavePath is where you want all the attachments extracted to
sFileType is currently Word documents. Change it to the last three letters of whatever file type you need.

HTH,

[Maurits]

Thanks a billion, kpuls.

The information i wanted was just in the e-mail body, so i adapted your code a little to extract that instead of attachments.

[VBA]Option Explicit
Private ogwApp As GroupwareTypeLibrary.Application
Private ogwRootAcct As GroupwareTypeLibrary.Account

Sub Groupwise_SaveAttachToFile()
' Date Created : 4/20/2005 10:25
' Macro Purpose: Save all attachments of specified file type into a
' user specified folder using Groupwise
' NOTE: This code requires a reference to the "GroupWare Type Library"

Dim ogwFolder As Folder, _
ogwFoundFolder As Folder, _
i As Long, _
sCommandOptions As String, _
sMailPassword As String, _
sLoginName As String, _
sFolderToSearch As String, _
sFileType As String, _
sSavePath As String, _
ogwMail As Mail, _
boodschap As String, _
wd As Object, _
afzender As String, _
word As Boolean

Set wd = CreateObject("word.application")

'Change required variables here!
sLoginName = "IT1"
sFolderToSearch = "Applicatie"
sSavePath = "C:\Temp" 'do not add trailing \
sFileType = "doc"

'Set application object reference if needed
If ogwApp Is Nothing Then 'Need to set object reference
DoEvents
Set ogwApp = CreateObject("NovellGroupWareSession")
DoEvents
End If

'Create connection/login to email account
If ogwRootAcct Is Nothing Then 'Need to log in
'Login to root account
If Len(sMailPassword) Then 'Password was passed, so use it
sCommandOptions = "/pwd=" & sMailPassword
Else 'Password was not passed
sCommandOptions = vbNullString
End If

Set ogwRootAcct = ogwApp.Login(sLoginName, sCommandOptions, _
, egwPromptIfNeeded)
DoEvents
End If

'Search all mail items, and save any matching attachments to the
'specified directory
For Each ogwMail In ogwRootAcct.AllFolders.ItemByName(sFolderToSearch).Messages
With ogwMail
'a "if it contains this word, then" would have been better, but this
works as well"
If .Subject = "Re: Questionaire" Then

boodschap = .BodyText
afzender = .Sender

With wd
If word = False Then
'Make document visible
wd.Visible = True
'Activate MS Word
AppActivate wd.Name
'Open a new document in Microsoft Word
.Documents.Add
'Insert a paragraph
.Selection.TypeParagraph
.ActiveDocument.SaveAs FileName:="reactie1.doc"
'spelling check always gives me hell when making large
'doc files. So i always de-activate it before it starts
'whining.
With wd.options
.CheckSpellingAsYouType = False
.CheckGrammarAsYouType = False
.SuggestSpellingCorrections = False
.SuggestFromMainDictionaryOnly = False
.CheckGrammarWithSpelling = False
.ShowReadabilityStatistics = False
.IgnoreUppercase = False
.IgnoreMixedDigits = False
.IgnoreInternetAndFileAddresses = False
End With
word = True
End If

.Selection.TypeText Text:=afzender
.Selection.TypeText Text:=Chr(13)
.Selection.TypeText Text:=Chr(13)
.Selection.TypeText Text:=boodschap
.Selection.InsertBreak
End With
'delete the mail, thanks for remembering that!
.Delete
Else
End If
End With
Next ogwMail

'Release all objects before closing
Set ogwRootAcct = Nothing
Set ogwApp = Nothing
DoEvents
End Sub[/VBA]