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:

Code:
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
        DoEvents
        Set ogwApp = CreateObject("NovellGroupWareSession")
        DoEvents
    End If

    'Login to root account if required
    If ogwRootAcct Is Nothing Then
        Set ogwRootAcct = ogwApp.Login(sLoginName, vbNullString, _
                , egwPromptIfNeeded)
        DoEvents
    End If

    'Create new message
    Application.StatusBar = "Building email to " & sEmailTo & "..."
    Set ogwNewMessage = ogwRootAcct.WorkFolder.Messages.Add _
            ("GW.MESSAGE.MAIL", egwDraft)
    DoEvents

    '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
        .Send
        DoEvents
        If Err.Number = 0 Then Application.StatusBar = "Message sent!" _
            Else: Application.StatusBar = "Email to " & sEmailTo & " failed!"
        On Error GoTo 0
    End With

EarlyExit:
    'Release all variables
    Set ogwNewMessage = Nothing
    Set ogwRootAcct = Nothing
    Set ogwApp = Nothing
    DoEvents
    Application.StatusBar = False
End Sub

To use the code:

  • Call if from another procedure (in any module) as shown in the following:

Code:
Sub SendMyMail
     Call Email_Via_Groupwise("YourMailBoxIDGoesHere", _
        "[email protected]", _
        "This is a test email", _
        "I hope you enjoy it!", _
        "C:WorkbookOfInterest.xls,C:AndAnotherOne.xls")
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.

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts