PDA

View Full Version : Sleeper: Emailing Sheets In Same Spreadsheet To Different Addresses



Xndman
10-30-2006, 12:50 PM
Hello Everyone,
I'd like to email the five sheets of a workbook to five different managers. I have Groupwise for email, and have gotten Excel to email me directly. Any thoughts on how to now attach the correct sheet to the correct manager? Thanks in advance.

Bob Phillips
10-30-2006, 01:30 PM
There is some code at http://www.rondebruin.nl/mail/folder1/mail2.htm

Xndman
10-30-2006, 01:52 PM
That code doesn't work with Groupwise, what pieces of the code tell it to email only the specific sheet to the specific user? Maybe I could then integrate that into my Groupwise code. The Groupwise code (which I copied from here) is:

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

Bob Phillips
10-30-2006, 01:56 PM
You can't mail a single sheet per se, so Ron copies it to a new workbook, and mails that



'Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

Pinokkio
10-31-2006, 09:35 AM
Mayby you can't try this ?

P.

Xndman
10-31-2006, 11:22 AM
I like the way the sheet is set up, by I could use some help incorportating the Groupwise code into the macro.

Thanks!!

Xndman
11-02-2006, 12:27 PM
Bump for any additional thoughts about emailing individual sheets in Groupwise.

Thanks!!T