Printing & Emailing a Worksheet

[golf4]

Hi, everyone -

It's been so long since I've posted anything - have been able to "fumble" my way through projects with so many great ideas from postings here. I have run into a problem that I think I need some ideas to get accomplished.

I've posted my code below - developed so far. The macro below will print a Family Self-Sufficiency Worksheet (Sheet20) out of my Rent Calculation Tool. I have included, at the bottom of the worksheet, boxes formatted with WingDings so that three copies will be printed: one with an checkmark in the box for "Family", one in the box marked "File" and the third marked "Accounting". The problem I'm having (with my crack staff) is that Accounting's copy keeps getting filed in the file and never sent to Accounting. http://www.mrexcel.com/board2/images/smiles/icon_banghead.gif

Wishing to make my Rent Calculation Tool more "user-friendly" (yea, that's it..... http://www.mrexcel.com/board2/images/smiles/icon_wink.gif ), I'm looking to revise my code so that the first two copies are printed out, and the third be emailed directly to Accounting. In the code below, I've included (in bold & italics) something like what I'm looking for although I don't want to email the whole workbook. I would really appreciate any ideas.


Sub PrintFSSWorksheet() 'Print FSS Escrow Worksheet'
Sheet20.Activate
With Sheet20
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
ActiveWorkbook.SendMail "xxxxx@wvpha.org", "FSS Worksheet"
Sheet2.Select
[a1].Select
.Protect "led52not"
End With
End Sub


Thanks for all the help.
Frank

[Ken Puls]

Hi Frank,

Just a conceptual thought... how does this sound: create a new workbook on the fly, copy your sheet to it, hit it with the sendmail, then delete the new book (on the fly as well).

Do you think that would work for you? If you need help with the code, let me know.

[Ken Puls]

I'm thinking something like this:


Sub mailbook()
Dim curWB As Workbook, _
newWB As Workbook
Set curWB = ActiveWorkbook
Set newWB = Workbooks.Add
curWB.Sheets(1).Copy newWB.Sheets(1)
With newWB
.SaveAs Filename:="C:\temp\DearAccounting.xls"
.SendMail Recipients:="someone@somedomain.com"
.Close savechanges:=False
End With
Kill "C:\temp\DearAccounting.xls"
End Sub

The only thing I'm not 100% sure of is if the Sendmail portion will work. I have a test going, but I'm not sure sendmail works with my mail software...

[golf4]

Hi, Ken -
Thanks so much for the response and idea. It would be so much easier if Staff would do what they're supposed to do, huh?!

I'll give your suggestion a shot. Thanks again.

Frank

[Ken Puls]

Hi Frank,

It sure would be, but let's face it... That's why we automate these things!

(You know, one of my staff once asked me if I was going to replace her with a button!) :rotlaugh:

Let me know how it works out. My sendmail test from Groupwise didn't work, but we specifically don't associate any Office plugins with Groupwise either.

Cheers,

[Smitty]

Heya Frank!

Just an FYI, but

.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"

can be shortened to

.Shapes("Text Box 1").Characters.Text = "P"

Eliminating Select statements where possible will help speed things up.

Smitty

(Heya Ken!)

[golf4]

Hey Smitty -
Thanks for the suggestion, bud. Will give it a try.

Stay dry and warm down your way.

Frank

[Ken Puls]

Howdy guys!

Frank, I had the chance to test the code I gave you at home, where I use outlook, and it fired no problem. (Except that it irritatingly asks if I really want to let a program send email on my behalf.) If you find that you want it going without notification, you may want to try Ron DeBruin's CDO found here (http://www.rondebruin.nl/cdo.htm).

HTH,

[golf4]

Hi, Ken -
Haven't had the chance to test your suggestion at work yet. Twisted the ^%$#@ out of my back and was layed up on Tuesday. Planning to give it a shot tomorrow morning. We do use GroupWise at work, so it should be interesting to see what happens.

Thanks again for the help,

Frank

[Ken Puls]

Hi Frank,

Hope you're feeling better now. If you do need a Groupwise mail procedure, let me know, as I have one that works.

Cheers,

[Zack Barresse]

Heya fellas!

How are ya?! Hope ya'll are doin good. :yes

[start=hijack:thread]

Ken, I'd be interested in a Groupwise mail procedure. Trying to incorporate different email clients into an add-in I have distributed. I wrote off those with Groupwise, but if you've got something, would you mind sharing? :conscious

;)

[end=hijack:thread]

[Ken Puls]

Heya,

Nope, wouldn't mind sharing at all. Can't today, though, as a little nutty.

One word of warning though... my proc requires an early bind. No big deal if you have the groupwise library files, but if you don't, you're dead. I've been wanting to convert it to a late bind, but someone told me that you couldn't, and that it required it to be early. I personally doubt that, but haven't had the time to prove/disprove.

FYI, XL-Dennis also has a Gropuwise proc on his website already... which looks like it uses a late bind. You can find a bunch of XL-Dennis's mail stuff here (http://www.xldennis.com/Program%28vba%29.htm). It seems that Excel programming in Sweden is done in english, but the commentary is all in Swedish. Dennis could correct me on that, though.

Cheers,

[golf4]

Hi, Zack & Ken -
After attempting a couple different things as far as emailing the worksheet, I've decided to give the staff person one more shot to "do what she is supposed to do". I've decided to insert a wav file and a msgbox to remind the staff person to properly distribute the worksheets to the appropriate departments. We'll see what happens.

Thanks for the help.

Frank

[Ken Puls]

Hey Frank and Zack,

I did a little digging around in a code library I have, and came across this procedure... Sorry it's a little late, Frank, :( but Zack expressed interest too, so if either of you need it, here it is:


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"
StrMailPassword = ""
StrSubject = "Subject goes here"
StrBody = "Body of message goes here" & vbCrLf & _
"Sent at " & Now()
strAttachFullPathName = ""
'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
For Each cl In ActiveSheet.Range("Email_CC")
'Assign "CC" recipients
If Not cl.value = "" Then ogwNewMessage.Recipients.Add cl.value, NGW, egwCC
Next cl
For Each cl In ActiveSheet.Range("Email_BC")
'Assign "BC" recipients
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

I originally used this with Groupwise 5.5, but it has also worked in 6.0 and 6.5 as well.

[golf4]

Thanks, Ken -

I'll try it out Monday morning.

Take care,
Frank

[johnske]

Hi Ken, Zack,

Not sure what you're meaning about "Group-wise" - I assume it's a program of some sort to send attachments to multiple recipients?

Thanx to you guys (and Jake) for originally getting me started with code for emailing attachments - but I came up with using an array for the recipients when I have to send to a number of them (their email addys are on the spread sheet). Dunno if it's any use, but here it is:


Option Explicit
Sub EmailActiveSheet()
Dim ThisDate$, Recipient(1 To 2), N%
Dim SendersBook As Workbook
Dim RecipientsBook As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Set a Codename for the senders book
Set SendersBook = Workbooks.Item(ActiveWorkbook.Name)
'Get the list of recipients - (in A1 & A2 for this example)
With Worksheets(1)
Recipient(1) = .Range("A1")
Recipient(2) = .Range("A2")
End With
'Now create a new workbook to send
Workbooks.Add
ThisDate = Format(Date, "dd mmm yy")
ActiveWorkbook.SaveAs "File for " & ThisDate & ".xls"
'Set the Codename for this new workbook as "RecipientsBook"
Set RecipientsBook = ActiveWorkbook
SendersBook.Activate
'Copy the active sheet and paste it into the recipients book.
SendersBook.ActiveSheet.Copy Before:=RecipientsBook.Worksheets(1)
RecipientsBook.Activate
'Delete blank sheets in the recipients book
For N = 1 To 3
Sheets("Sheet" & N).Delete
Next N
'<< OPTION ONE: Send the new workbook without a message >>
'ActiveWorkbook.SendMail Recipient, "Files for " & ThisDate
'<< END OF OPTION ONE >>
'<< OPTION TWO: Send the new workbook with a message
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Recipients = Recipient()
.Subject = "Files for " & ThisDate
.Message = "Hi," & vbLf & _
"" & vbLf & _
"Attached files are for...crap on..." & vbLf & _
"...more crap here.... " & vbLf & _
"" & vbLf & _
"Regards," & vbLf & _
"Senders name" & vbLf & _
"" & vbLf & _
"" & vbLf & _
"" & vbLf & _
"".Delivery = xlAllAtOnce
.ReturnWhenDone = False
End With
ActiveWorkbook.Route
'<< END OF OPTION TWO >>
'Delete the senders copy of the recipients book
'(this was only a temp book for the sender)
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
'Let user know what's happened
MsgBox "File sent by email ", , "Emailed..."
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Worksheets(1).Activate
End Sub

Regards,
John

[Ken Puls]

Hey John,

Yep. Groupwise is Novell's email client. It's actually a full document management system, calendar, contact... bigger and better than Outlook in many ways.

Cheers,

[Ken Puls]

Hi again,

It just occured to me that I only mentioned Groupwise's front end... Groupwise also has a full back end side to it as well, and runs a full email server. (Just a small oversight!)

Cheers,