• Send Email Via Groupwise

    Macro Purpose:
    • This is a stand-alone routine that will allow a user to automate the process of sending an email via the Novell Groupwise email client using VBA.
    Examples of where this function shines:
    • Assume that you want to email out a notifcation to a mailing list that you maintain in Excel or Access. This routine can be configured to send to one or more users at the same time, including one or more attachments.
    Macro Weakness(es):
    • Passing a file with commas in the filename will fail since arguments are passed to the function as a comma separated string.
    • There is a maximum numbers of characters that can be passed for the attachment file path. (See "Issues under investigation", at the end of this article, for more on this problem.)
    • In early 2008, I migrated from Groupwise to Exchange, so I will only be able to offer limited advice and help on issues with this routine.
    Versions Tested:
    • Excel versions tested:97, 2003 (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.)
    • Novell Groupwise versions tested: 6.5, 7.0.2

      If you have run this code on any other configurations, please post a note in the comments to let us know.
    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
    Public Sub Email_Via_Groupwise(sLoginName As String, _
            sEmailTo As String, _
            sSubject As String, _
            sBody As String, _
            Optional sAttachments As String, _
            Optional sEmailCC As String, _
            Optional sEmailBCC As String)
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro purpose: To stand as a self contained procedure for creating and
    '               sending an email via groupwise
    'NOTE:          You can feed a comma separated string of address to all
    '               address and attachment fields
        On Error GoTo EarlyExit
        'Required variable declarations
        Const NGW$ = "NGW"
        Dim ogwNewMessage As GroupwareTypeLibrary.Mail
        Dim aryTo() As String, _
                aryCC() As String, _
                aryBCC() As String, _
                aryAttach() As String
        Dim lAryElement As Long
        'Split the emails into an array if necessary
        aryTo = Split(sEmailTo, ",")
        aryCC = Split(sEmailCC, ",")
        aryBCC = Split(sEmailBCC, ",")
        aryAttach = Split(sAttachments, ",")
        'Set application object reference if needed
        Application.StatusBar = "Logging in to email account..."
        If ogwApp Is Nothing Then
            Set ogwApp = CreateObject("NovellGroupWareSession")
        End If
        'Login to root account if required
        If ogwRootAcct Is Nothing Then
            Set ogwRootAcct = ogwApp.Login(sLoginName, vbNullString, _
                    , egwPromptIfNeeded)
        End If
        'Create new message
        Application.StatusBar = "Building email to " & sEmailTo & "..."
        Set ogwNewMessage = ogwRootAcct.WorkFolder.Messages.Add _
                ("GW.MESSAGE.MAIL", egwDraft)
        'Assign message properties
        With ogwNewMessage
            'To field
            For lAryElement = 0 To UBound(aryTo())
                .Recipients.Add aryTo(lAryElement), NGW, egwTo
            Next lAryElement
            'CC Field
            For lAryElement = 0 To UBound(aryCC())
                .Recipients.Add aryCC(lAryElement), NGW, egwCC
            Next lAryElement
            'BCC Field
            For lAryElement = 0 To UBound(aryBCC())
                .Recipients.Add aryBCC(lAryElement), NGW, egwBC
            Next lAryElement
            'Subject & body
            .Subject = sSubject
            .BodyText = sBody
            'Attachments (if any)
            For lAryElement = 0 To UBound(aryAttach())
                If Not aryAttach(lAryElement) = vbNullString Then _
                        .Attachments.Add aryAttach(lAryElement)
            Next lAryElement
            'Send the message (Sending may fail if recipients don't resolve)
            On Error Resume Next
            If Err.Number = 0 Then Application.StatusBar = "Message sent!" _
                Else: Application.StatusBar = "Email to " & sEmailTo & " failed!"
            On Error GoTo 0
        End With
        'Release all variables
        Set ogwNewMessage = Nothing
        Set ogwRootAcct = Nothing
        Set ogwApp = Nothing
        Application.StatusBar = False
    End Sub
    To use the code:
    • Call if from another procedure (in any module) as shown in the following:
    Sub SendMyMail
         Call Email_Via_Groupwise("YourMailBoxIDGoesHere", _
            "MrSmith@somedomain.com", _
            "This is a test email", _
            "I hope you enjoy it!", _
    End Sub
    How it works:
    • The code will split the To, CC, BCC and attachment fields into arrays.
    • Next, it 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 array adding the values (if any) to the appropriate address, subject and attachment fields.
    • It then sends the email out.
    • Finally, the code releases it's hold on the Groupwise object to save memory.
    The End Result:
    • Your email will be sent to the intended recipient.
    Issues Under Investigation:
    In the summer of 2008, I began receiving reports that very long file attachment paths would cause the sending of the email to fail. Unfortunately, since I migrated from Groupwise to Exchange earlier in the year, I was unable to test this issue. To my understanding, there would seem to be a character limit to attachment paths. Fortunately, James K has emailed me to let us know that his testing reveals the limit to be 126 characters for a single attachment. Thanks James!

    This gives us two (related) potential workarounds:
    1. Make a temp copy of the file (maybe in C:\temp), email that file, and then delete the temp file using the Kill method.
    2. Test the length of the attachment path to see if it exceeds 126 characters. If it does, use #1 above and if not, just attach it.


    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!