- 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.
- 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.
- 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 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:
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:
- Make a temp copy of the file (maybe in C:temp), email that file, and then delete the temp file using the Kill method.
- 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.