PDA

View Full Version : Email from excel via groupwise 8 using vba



Keith W
10-28-2010, 02:22 AM
Hi, I am new to this forum and hope I haven't broken any rules here but here goes.
I have used the code below to email an ecel workbook as an attachment via groupwise 7.
We have recently hade a new windows image on our computers and moved to groupwise 8, and the attachements.add statement throws up an automation error any ideas why this might be so ? On erro resume next jumps over the statement when added prior to it, and email is sent properly but without the attachment.
Any clues would be a real help.
Thank you in anticipation.
Option Explicit
Private ogwApp As GroupwareTypeLibrary.Application
Private ogwRootAcct As GroupwareTypeLibrary.account

Sub Email_Multiple_Users_Via_Groupwise()
'Macro purpose: To stand as a self contained procedure for creating and
'sending an email to multiple users (if required)

'This code requires:
' -A reference to the Groupware Type Library
' -The following 2 lines declared at the beginning of the MODULE:
' Private ogwApp As GroupwareTypeLibrary.Application
' Private ogwRootAcct As GroupwareTypeLibrary.account
' -The following named ranges on the spreadsheet
' Email_To
' Email_CC
' Email_BC

'SECTION 1
'Declare all required variables

Const NGW$ = "NGW"
Dim ogwNewMessage As GroupwareTypeLibrary.Mail
Dim StrLoginName As String, _
StrMailPassword As String, _
StrSubject As String, _
StrBody As String, _
strAttachFullPathName As String, _
sCommandOptions As String, _
cl As Range

'SECTION 2
'Set all required variables

StrLoginName = "GroupwiseMailboxName" 'Enter your mailbox ID here
StrMailPassword = "" 'A true password is not required
StrSubject = "Subject goes here"
StrBody = "Body of message goes here" & vbCrLf & _
"Sent at " & Now()
strAttachFullPathName = "" 'Put full path of workbook to be attached between quotes.

'SECTION 3
'Create the Groupwise object and login in to Groupwise

'Set application object reference if needed
If ogwApp Is Nothing Then 'Need to set object reference
DoEvents
Set ogwApp = CreateObject("NovellGroupWareSession")
DoEvents
End If

If ogwRootAcct Is Nothing Then 'Need to log in
'Login to root account
If Len(StrMailPassword) Then 'Password was passed, so use it
sCommandOptions = "/pwd=" & StrMailPassword
Else 'Password was not passed
sCommandOptions = vbNullString
End If

Set ogwRootAcct = ogwApp.Login(StrLoginName, sCommandOptions, _
, egwPromptIfNeeded)
DoEvents

End If

'SECTION 4
'Create and Send the Message

'Create new message
Set ogwNewMessage = ogwRootAcct.WorkFolder.Messages.Add _
("GW.MESSAGE.MAIL", egwDraft)
DoEvents

'Assign "To" recipients
For Each cl In ActiveSheet.Range("Email_To")
If Not cl.Value = "" Then ogwNewMessage.Recipients.Add cl.Value, NGW, egwTo
Next cl

'Assign "CC" recipients
For Each cl In ActiveSheet.Range("Email_CC")
If Not cl.Value = "" Then ogwNewMessage.Recipients.Add cl.Value, NGW, egwCC
Next cl

'Assign "BC" recipients
For Each cl In ActiveSheet.Range("Email_BC")
If Not cl.Value = "" Then ogwNewMessage.Recipients.Add cl.Value, NGW, egwBC
Next cl

With ogwNewMessage
'Assign the SUBJECT text
If Not StrSubject = "" Then .Subject = StrSubject

'Assign the BODY text
If Not StrBody = "" Then .BodyText = StrBody

'Assign Attachment(s)
If Not strAttachFullPathName = "" Then .Attachments.Add strAttachFullPathName

'Send the message
On Error Resume Next
'Send method may fail if recipients don't resolve
.Send
DoEvents
On Error Goto 0
End With

'SECTION 5
'Release all variables
Set ogwNewMessage = Nothing
Set ogwRootAcct = Nothing
Set ogwApp = Nothing
DoEvents
End Sub