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