Book1, re-use?

[cmonthetic]

Hi,

Using Excel 97.

I have several form and 1 of the functions I have included is the ability to email a specific worksheet to soemone. This involves copying and pasting the required sheet to a new workbook and then sending the new workbook by email.

All of this works perfectly

The problem I have is that when the user click on the email button it works, but if they then click the button again it returns an Error '9'.

The automatic creation of the workbook to paste the selected sheet into seems to be causing the problem as it is always looking for 'Book1' but I think that with each press of the email button the workbooks.add function is incrementing the 'book' by 1

Any ideas on how to get the workbooks.add function to always use 'Book1' as its name.

Code is posted below:

Private Sub EMail_Click()
Application.ActiveWorkbook.Worksheets("Printout").Activate

Workbooks.Add
Windows("GPCTransLog.xls").Activate
ActiveWindow.WindowState = xlNormal
Range("A1:L200").Select
Selection.Copy
Windows("book1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Application.CutCopyMode = False

Windows("GPCTransLog.xls").Activate
ActiveWindow.WindowState = xlNormal
Range("A1:L200").Select
Selection.Copy
Windows("book1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Application.CutCopyMode = False

Columns("A:A").ColumnWidth = 5
Columns("B:B").ColumnWidth = 9
Columns("C:C").ColumnWidth = 9
Columns("D").ColumnWidth = 25
Columns("E:E").ColumnWidth = 12
Columns("F:F").ColumnWidth = 13
Columns("G:G").ColumnWidth = 10
Columns("H:H").ColumnWidth = 10
Columns("I:I").ColumnWidth = 9
Columns("J:J").ColumnWidth = 10
Columns("K:K").ColumnWidth = 7
Columns("L:L").ColumnWidth = 18
Cells.Find(What:="Cardholder Signature", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate
ActiveCell.RowHeight = 26.25
Range("a1").Select
ChDir "H:\"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="H:\GPCPrintout.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Range("A170").Select
ActiveWindow.ScrollRow = 1
Range("A1:B1").Select
Application.DisplayAlerts = True

(LastName).Value & ", " & ActiveWorkbook.Names(FirstName).Value & " " & ActiveWorkbook.Names(GroupName).Value
SendTo = "email@company.com"
Ebody = "GPC Log"
NewFileName = "H:\GPCPrintout.xls"

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo
.Body = Ebody
.Attachments.Add ("H:\GPCPrintout.xls")
.Display
.send
End With
Set App = Nothing
Set Itm = Nothing
End Sub

TIA

 

[SkipVought]

Hi,

Try changing the FIRST statement...

Application.ThisWorkbook.Worksheets("Printout").Activate

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[cmonthetic]

Hi SkipVought,

Thanks for the reply, the line you have specified is kind of redundant as it just activates the sheet I am copying from and is not really doing much.

As I said the copy/past/email works on the first click of the button, but if you try pressing the button again I get an Error '9' and it points towards the "Book1" reference in the code.

If I exit from Excel and then go back in I can click on the button again and it will work on one occasion again so I think the problem is something to do with how excel assigns a temporary name to each book as its created.

Unfortunatly I have no idea on how to go about getting excel to always use "book1" as its default temp name.

any help is greatly appreciated.

 

[dhulbert]

if you change the workbook.add line to say
set newbook = workbook.add

then where you use Windows("book1").Activate swap it for

newbook.activate

and then the name of th eworkbook doesn't matter and your code should work.

 

[cmonthetic]

Thanks for the reply dhulbert but your suggestion doesn't seem to work for me, getting problems with the 'set' command you suggested.

Any other ideas?

 

[PHV]

set newbook = workbook[highlight]s[/highlight].add

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhulbert]

OOps. sorry for the typo...