• Extract Novell Groupwise Attachments And Save To A Directory

    Macro Purpose:
    Scan a user specified Novell Groupwise folder, and save all files of a specific type to the directory specified in the code.

    Examples of where this function shines:
    Assume that you have emailed out 600 surveys in a word document, and you want to save them all to a folder for further processing. Rather than have you save each file to the folder manually, this code will extract them all at one time.

    This code can be very easily adapted to work with any type of file, since it examines the last three letters (the extension) of each attachment to an email. This means that you do not need a program capable of opening the file on your hard drive to run this code.

    Macro Weakness(es):
    • Does not make any distinction of whether the files have been extracted before. This means that running the code on a directory will extract all files again.
    • Macro overwrites all files in the "Save To" directory (this is actually by design)

    Versions Tested:
    This function has been tested with Excel 2003 and Novell's Groupwise 6.5. It should not have any issues running from any of the Office applications from 97 or higher, but to date this has not been tested.

    VBA Code Required:
    • A reference must be set to the Groupware Type Library
    • The code following should be placed in a standard code module:

    Option Explicit
    Private ogwApp As GroupwareTypeLibrary.Application
    Private ogwRootAcct As GroupwareTypeLibrary.Account
    Sub Groupwise_SaveAttachToFile()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Save all attachments of specified file type into a
    'user specified folder using Groupwise
    'NOTE:  Reference to Groupware Type Libary required
    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 = "YourMailboxID"
    sFolderToSearch = "FolderToLookIn"
    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
        Set ogwApp = CreateObject("NovellGroupWareSession")
    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)
    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
                'Attachments found.  Save desired type to specified folder
                For i = 1 To .Attachments.Count
                    If Right(.Attachments(i).Filename, len(sFileType)) = 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
    End Sub
    How it works:
    • The code will create a new Groupwise instance if needed (or attach to an existing one).
    • It then logs in to the user's mailbox, and loops through each email in the specified folder.
    • If it finds an attachment with a word document, (or the file type you specify,) it will save it into the folder that you specify in the code.
    • It then releases it's hold on the Groupwise object to save memory.

    The End Result:
    • You will end up with all of the documents saved in the directory specified in the code.


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 1 Comment
    1. danimlucht's Avatar
      danimlucht -
      Do you have any way of doing this in VB? I know they are very similar but I'm getting a specific error when i try to convert so I'm fairly certain I'm doing something wrong.