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.
- 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)
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 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
- 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.