• 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:

    Code:
    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
        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, 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
    DoEvents
    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.
  • MVP Logo
  • Recent Forum Posts

    Logit

    unduplicated list

    If you also include the "Check Run Date" column in the table as well, that will provide a means of selecting which month.

    Code:
    
    
    ...

    Logit Today, 04:35 PM Go to last post
    BuddyGSD

    unduplicated list

    Is there a way for excel to gather the names of the families from the month of April and May to appear on the Undup Clients tab then?...

    BuddyGSD Today, 04:08 PM Go to last post
    NoS

    Sum cells in each generated row

    Along with writing the form data to the sheet you could write a formula into the O column

    Code:
    Cells(whateverrow, 15).FormulaR1C1="=SUM(RC[-9]:RC[-1])"
    ...

    NoS Today, 02:27 PM Go to last post
    p45cal

    If overide formulae

    Array-enter (Ctrl+Shift+Enter, not just Enter) and copy down:
    =INDEX({"Fail";"Risk";"Pass"},MIN(MATCH(T2:U2,{"Fail","Risk","Pass"},0)))...

    p45cal Today, 12:14 PM Go to last post
    jonespandrew

    If overide formulae

    Sorry I have read you last response wrong Write

    Fail Risk would be a fail
    Risk pass would be a Risk
    Risk Fail would be a fail...

    jonespandrew Today, 11:14 AM Go to last post