glover
08-26-2008, 04:03 AM
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
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