Solved: Excel VBA Groupwise (File names)

[glover]

Hi there,

Im currently successfully using the vba code from
excelguru dot ca/node/90

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
'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


Sub SendMyMail
Call Email_Via_Groupwise("YourMailBoxIDGoesHere", _
"MrSmith@somedomain.com", _
"This is a test email", _
"I hope you enjoy it!", _
"C:\WorkbookOfInterest\080826 Total list.xls,")
End Sub


This code send and email in excel via groupwise.

My issue is when I try to either attach file with a date in or the the path to the attachment it doesn not work. e.g. "080826 Total list".

I cant work out why it doesnt send when there is a number in the filename.

Any help would be greatly appreciated.

Thank you

[Ken Puls]

Hi there,

Sorry, but I migrated away from Novell Groupwise earlier this year, so I can't really help you troubleshoot this much. I can say that I never had an issue emailing with numbers in the filename though. In fact, when I built the routine, it's main function in my org was to do exactly that. Most files were in the format "2008,08,31 Financials.xls".

One thing that does look a little weird in your routine is the very last line though... should that comma after the file name really be there?

[glover]

Sorry about the extra comma, it was just because I was copying and pasting the example with adding in my file name example.

Ive managed to fix the problem, it turned out that by adding the numbers onto the file it just made the path too long?

So i put the file with the date into "Higher folder" and it worked.

Do you know of a possible work around for this problem?

Any help would be great,

Thank you :)

[Ken Puls]

Is the path too long because it's nested too deep in the OS, or because it's longer than the string variable can hold?

Could you make a temporary copy of the file to a higher directory (say C:\Temp), email it from there, then delete the copy when you're done?

[glover]

Not too sure how much a string can hold, but it is a fair few directorys deep.

Yeah as a temp solution I did just make it email it from where it worked (being the higher directory), then once it had sent, I made it delete the file.

Thanks again :)