Solved: Multiple attachments to GroupWise Email with VBA code

[Tom]

Is there ANYWAY to attach more than one object (a document, a report, an Excel spreadsheet, ETC) to a GroupWise EMail generated by VBA code (Access 2003 and GroupWise 6.5)?
Thanks in advance for ANY assistance,
Tom

[Ken Puls]

Hi there,

I still use this (http://vbaexpress.com/kb/getarticle.php?kb_id=277) to email files using Groupwise 6.5 from Excel 2003. I'm not sure what code you're running, but in my KB entry, there is a section that adds the (single) attachment:

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

You could easily add another variable and modify it to, say:
'Assign Attachment(s)
If Not strAttachFullPathName1 = "" Then .Attachments.Add strAttachFullPathName1
If Not strAttachFullPathName2 = "" Then .Attachments.Add strAttachFullPathName2

If you don't need to test the string (you know you always have two attachments), then drop the If Not = "" Then part.

The KB entry was made for Excel, so you'll need to recode the parts that refer to Excel named ranges (To, CC and BCC lines).

HTH,

[Tom]

Ken,
Thanks beau-coup for the response. I'm going to have to get back to you as this is going to take a bit of tinkering to kick it over to Access VBA. Do you think, if I made the IF statements into the filler of a Public variable and stuck it into a DoCmd statement, it would pick up multiple attachments? Anyhooooooooo, Thanks again for the help.
Tom

[Ken Puls]

Hi Tom,

What I would do, and maybe I should write something for this, is modify the procedure so that it accepts arguments (or arrays of arguments) that are not app specific. You'd then be able to just pass those pieces to the procedure from whatever app (Word, Excel, Access etc...)

If you passed an array of anything, though, you'd need to loop through it to identify if anything was there, and act accordingly.

Does this sound like something you can do, or would you like a little help with it?

[Tom]

I greatly appreciate any help you might have the time to provide. I've been playing with the code that you provided but translating it from Excel to Access is somewhat beyond my abilities. All I'm really looking for is a simple GroupWise mail generation script with the capabilities of multiple attachments to reduce the workload of my co-workers and myself of the huge amount of spreads we have to put out daily.

[Ken Puls]

Hi Tom,

The following code is not Access specific any more. You'll need to fill the appropriate arrays with data from Access. I've used temporary strings fed into the split statement before, or just feed your address straight in there. The Access side, I'm afraid that I'll have to leave to you, as it's not my strong point.

Option Explicit

Private ogwApp As GroupwareTypeLibrary.Application
Private ogwRootAcct As GroupwareTypeLibrary.account
Private Const NGW As String = "NGW"

Sub test()
Dim ArrayTo() As String
Dim ArrayCC() As String
Dim ArrayBCC() As String
Dim ArrayAttach() As String

'Assign your data to the appropriate arrays
'Use a comma separated list which will be split and passed to Email routine
ArrayTo = Split("one@someaddy.com,two@someaddy.com", ",")
ArrayCC = Split("three@someaddy.com,four@someaddy.com", ",")
ArrayAttach = Split("J:\Test.xls,J:\test.doc", ",")

'Call the email routine
'Don't forget to supply your email box name below
Call Email_Multiple_Users_Via_Groupwise("MailboxNameHere", ArrayTo, _
ArrayCC, ArrayBCC, ArrayAttach, "Testing")
End Sub

Sub Email_Multiple_Users_Via_Groupwise(strLoginName As String, strTo() As String, _
strCC() As String, strBCC() As String, strAttachPaths() As String, _
Optional strSubject As String, Optional strBody As String)

'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

'SECTION 1
'Declare all required variables

Dim ogwNewMessage As GroupwareTypeLibrary.Mail
Dim StrMailPassword As String
Dim sCommandOptions As String
Dim lIndex As Long
Dim strTemp As String

'SECTION 2
'Set all required variables
StrMailPassword = "" 'A true password is not required

'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

On Error Resume Next
'Assign "To" recipients
strTemp = strBCC(0)
If Err.Number <> 0 Then

For lIndex = LBound(strTo) To UBound(strTo)
ogwNewMessage.Recipients.Add strTo(lIndex), NGW, egwTo
Next lIndex
Else
MsgBox "No recipients defined!"
GoTo ExitEmail
End If

'Assign "CC" recipients
strTemp = strCC(0)
If Err.Number <> 0 Then
Err.Clear
Else
For lIndex = LBound(strCC) To UBound(strCC)
ogwNewMessage.Recipients.Add strCC(lIndex), NGW, egwCC
Next lIndex
End If

'Assign "BC" recipients
strTemp = strBCC(0)
If Err.Number <> 0 Then
Err.Clear
Else
For lIndex = LBound(strBCC) To UBound(strBCC)
ogwNewMessage.Recipients.Add strBCC(lIndex), NGW, egwBC
Next lIndex
End If

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)
strTemp = strAttachPaths(0)
If Err.Number <> 0 Then
Err.Clear
Else
For lIndex = LBound(strAttachPaths) To UBound(strAttachPaths)
.Attachments.Add strAttachPaths(lIndex)
Next lIndex
End If
'Send the message

'Send method may fail if recipients don't resolve
.Send
DoEvents
If Err.Number <> 0 Then
MsgBox "Problem sending email!"
Err.Clear
End If
End With

ExitEmail:
'SECTION 5
'Release all variables
On Error GoTo 0
Set ogwNewMessage = Nothing
Set ogwRootAcct = Nothing
Set ogwApp = Nothing
DoEvents
End Sub



If you want clarfication of any of this, however, please let me know. I"m happy to help where I can.

Cheers!

[Tom]

Ken,
I was having trouble with the syntax of the code that you sent me until I realized that I was having a DUMB ATTACK! In the Access 2003 version that I use the DIM or Public statement is BEFORE the literal. SOOOOOOOOOO I got out the books (as we say, "If all else fails..RDB (Read Da Book)), did a little translation and (low and behind) I sent an email with multiple attachments. Still and all, I couldn't have done it without your input. If you don't mind, I'll post the code to share.
Tom
(I did attach it to a button)

Option Compare Database
Private Sub Command0_Click()
Dim omailbox
Dim omessages
Dim oMessage
Dim oRecipients
Dim oAttachments
Dim eaddTo
Dim resp
Dim eadd As String
Dim oRecipient
Dim esub As String
Dim BodyMess As String
Dim dir As String
Dim file1 As String
Dim file2 As String
Dim Comb1 As String
Dim Comb2 As String

omailbox = Folder2
omessages = Messages
oMessage = Message3
oRecipients = Recipients
oAttachments = Attachments
eaddTo = ADDRESS
resp = Recipient
oRecipient = Recipient

Set oapp = CreateObject("NovellGroupWareSession")
Set oAcct = oapp.Login("YOUR MAILBOX NAME")
Set omailbox = oAcct.MailBox
Set omessages = omailbox.Messages
Set oMessage = omessages.ADD("GW.MESSAGE.MAIL", "Draft")
Set oRecipients = oMessage.Recipients
Set oAttachments = oMessage.Attachments
eadd = "YOUR_EMAIL_ADDRESS@YOU.COM"
'Directory name
dir = "C:\output"
'File name and suffix
file1 = "rolls.xls"
file2 = "BREAD.XLS"
Comb1 = dir & "/" & file1
Comb2 = dir & "/" & file2
'Send to who?
Set oRecipient = oRecipients.ADD("DUDE2@ADD.com", "NGW", "egwTo")
Set oRecipient = oRecipients.ADD("DUDETTE@ADD.com", "NGW", "egwTo")
oAttachments.ADD (Comb1)
oAttachments.ADD (Comb2)


oMessage.BodyText = "Message goes here"
oMessage.Subject = "Subject goes here"
oMessage.Send
End Sub

[Ken Puls]

Cool deal, Tom! :)

(Btw, I edited your post to use our VBA tags. Makes the code show a little nicer.)

[Imdabaum]

Just thought I'd say this is a great post. of course it was wierd that I found it when doing a search on error 3188, but it could come in handy since my office uses GroupWise 6.5. Maybe I can make up for my bad synchroniization event.

[Ken Puls]

LOL!

[FrankBall]

Ken, this is great stuff, but I'm having some difficulty getting my variation of the multiple attachment concept to work. I need to be able to hold a list of files in a spdsht and then attach each of those files to the email. I thought that this would be pretty straight-forward, but my code isn't attaching anything:

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)
'Here's where I'm stepping through my list of attachments. The code does hit every file in the list, but nothing is getting attached.
For Each cell In ActiveSheet.Range("Attachment")
.Attachments.Add cell
Next cell

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

[Ken Puls]

Try


For Each cell In ActiveSheet.Range("Attachment")
.Attachments.Add cell
Next cell

Cell should be declared as Range type.

[FrankBall]

Try


For Each cell In ActiveSheet.Range("Attachment")
.Attachments.Add cell
Next cell

Cell should be declared as Range type.
Still no joy, Ken. I can hard code to attach the files without using the For Next and everything works great, but every time I use the For Next, nothing gets attached.

[Tom]

This is an updated sub-routine that I'm using now



Private Sub Command0_Click()
Dim eadd As String
Dim dir As String
Dim file1 As String
Dim file2 As String
Dim file3 As String
Dim file4 As String
Dim file5 As String
Dim Comb1 As String
Dim Comb2 As String
Dim comb3 As String
Dim comb4 As String
Dim comb5 As String

Set oapp = CreateObject("NovellGroupWareSession")
Set oAcct = oapp.Login("your_login")
Set omailbox = oAcct.MailBox
Set omessages = omailbox.Messages
Set oMessage = omessages.ADD("GW.MESSAGE.MAIL")
Set oRecipients = oMessage.Recipients
Set oAttachments = oMessage.Attachments
eadd = "YOUR_EMAIL_ADDRESS"
dir = "G:\A51_development\output"
file1 = "A.xls"
file2 = "B.XLS"
file3 = "C.XLS"
'file4 = ""
'file5 = ""
Comb1 = dir & "/" & file1
Comb2 = dir & "/" & file2
comb3 = dir & "/" & file3
'Comb4 = dir & "/" & file4
'Comb5 = dir & "/" & file5
Set oRecipient = oRecipients.ADD("RECEIVER ADDRESS1")
Set oRecipient = oRecipients.ADD("RECEIVER ADDRESS2")
oAttachments.ADD (Comb1)
oAttachments.ADD (Comb2)
oAttachments.ADD (comb3)
'oAttachments.ADD (Comb4)
'oAttachments.ADD (Comb5)



oMessage.BodyText = "Message goes here"
oMessage.Subject = "Testing Subject"
oMessage.Send
End Sub

[Tom]

FrankBall,
Did the above updated script help with your problem?

[FrankBall]

FrankBall,
Did the above updated script help with your problem?Nope. The problem was that VBA wouldn't complete the attachment process when stepping through the range of cells. I would, however, handle it just fine if I loaded the range into an array and then stepped through the array. WiERd.

[Tom]

Soooooooooo..did you try your idea?

[jackecsr]

Hi Ken,
I was searching the net for an answer to send emails from Excel and found your solution: "Send email using Novell's Groupwise 5.5 (or above)"
I thought that was great, it worked. You indicated you had additional code to add an attachment. I am hoping to be able to send an email from Excel with a copy of the current book attached. Can you help?

[lenhardt1]

This is probably a stupid question....because I am a new guy at this...but if I just want to attach the current workbook how would I do that? Thank you

[Tom]

Are you trying to attached a series of Excell sheets in a workbook? Are you attaching one or multiple items? Is this workbook being generated within a VBA script or is it greated by another application?

[SteveB]

Greetings from New Zealand
I'm new here, and not quite new to Excel/VB.
I was searching for code to complete a task, and saw the above (Search: "sendmail+attachment") discussion, which is quite related.
A user wants to send their current (open) workbook to a list of people, with one or several small attachments.
I can use the
With Application.FileDialog(msoFileDialogOpen)
method to select the files that are to be attached, and the
PathTextString = .SelectedItems(lngCount)
method to retrieve the full path of the files.
I can store all of these ok, ready to use.
I can make the Subject and Address arguments work ok.
What I can't fathom is how to attach each of these to the workbook for sending.
What method do I use to, in effect,
"Attach File1, File2, ... to Workbook, then Send"
Any help greatly appreciated
SteveB