Solved: E-Mail/Command Buttons

[Philcjr]

All,

I want to copy a sheet tab and send it off in an e-mail with the working command buttons - (They do sorting).

The macro works well... I creates the e-mail however, the Command Buttons are pointing back to the Source file for the macro. I tried to re-assign the macros but no luck.

[vba]
Option Explicit

Sub SendEmail()

Dim Filename As String: Filename = "Product Supply Exception.xls"
Dim KillPath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.StatusBar = "Generating Email..."

ThisWorkbook.Sheets("Product Supply Exception").Copy

With ActiveWorkbook
With .ActiveSheet
.Shapes("Button 1").Cut 'Deletes the e-mail button

'assigns the Macro back to the button - which is on sheet05
'it this correct?
.Shapes("Button 2").OnAction = "Sheet05.SubTotalByBrand"
.Shapes("Button 3").OnAction = "Sheet05.SubTotalByBU"
.Shapes("Button 4").OnAction = "Sheet05.SubTotalRemove"
.Shapes("Button 5").OnAction = "Sheet05.SubTotalBySKU"
End With
.SaveAs Filename
KillPath = .FullName

Application.Dialogs(xlDialogSendMail).Show
.Close False
End With

Kill KillPath

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.StatusBar = ""

End Sub

[/vba]

[mark007]

Change:

Sheet05.SubTotalByBrand

to:

Sheet05!SubTotalByBrand

etc.

[Philcjr]

Mark,

Thanks for your input, however that still did not do it. I am at a total loss here as I have no idea why this is blowing-up.

Any other thoughts?

Thanks,
Phil

[geekgirlau]

I've had this problem before. From memory (always hazy at the best of times ) I think what you need to do is add code to the Workbook On Open event to point the command button macros to the current workbook, otherwise they retain the link to the original source.

[johnske]

Have you tried: .Shapes("Button 2").OnAction = "ThisWorkbook.Sheet05.SubTotalByBrand" (etc) or, .Shapes("Button 2").OnAction = "'Book1.xls'!Sheet05.SubTotalByBrand" (substitute the name of the active workbook for Book1)

[Philcjr]

Johnski,
I have tried your suggestions and that did not work, THANK YOU anyway. I tried all possible combination to no avail

Geekgirl,
Thanks, I think you are on the right track. I placed code in the workbook open event and it worked. Now I just have to figure out how to add code to the workbook open event progamatically.

Sorry it took so long to reply, but I was away on business and had no time to play.

[johnske]

Try along this line
[vba].OnAction = "'" & ThisWorkbook.Name & "'!Sheet05.SubTotalByBrand"[/vba]


EDIT: I'm also assuming here that Sheet05 is not the name of the workbook, but is referring to the sheet module that contains the code you want to run, and that SubTotalByBrand is the name of the procedure that would actually be written as Sub SubTotalByBrand() and that you're not trying to refer to some procedure called Sub TotalByBrand()

Which leads to a second thought... in the type of addressing that is used here, the sheets are referred to by their code name and with code names there can be no Sheet05, but there could be a Sheet5, giving the correct address as[vba].OnAction = "'" & ThisWorkbook.Name & "'!Sheet5.SubTotalByBrand"[/vba]

[johnske]

OK, it's raining and had some time to have a look at this, try this:
[vba]Option Explicit

Sub SendEmail()

Const Filename As String = "Product Supply Exception.xls"
Dim KillPath As String, FirstLine As Long

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.StatusBar = "Generating Email..."

ThisWorkbook.Sheets("Product Supply Exception").Copy

'//change the Sheet2 codename below to suit
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
FirstLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines FirstLine + 0, ""
.InsertLines FirstLine + 1, vbTab & "With Activesheet"
.InsertLines FirstLine + 2, vbTab & vbTab & ".Shapes(""Button 2"").OnAction = ""Sheet2.SubTotalByBrand"""
.InsertLines FirstLine + 3, vbTab & vbTab & ".Shapes(""Button 3"").OnAction = ""Sheet2.SubTotalByBU"""
.InsertLines FirstLine + 4, vbTab & vbTab & ".Shapes(""Button 4"").OnAction = ""Sheet2.SubTotalRemove"""
.InsertLines FirstLine + 5, vbTab & vbTab & ".Shapes(""Button 5"").OnAction = ""Sheet2.SubTotalBySKU"""
.InsertLines FirstLine + 6, vbTab & "End With"
End With

With ActiveWorkbook
.ActiveSheet.Shapes("Button 1").Cut 'Delete the e-mail button
.SaveAs Filename
KillPath = .FullName
Application.Dialogs(xlDialogSendMail).Show
.Close False
Kill KillPath
End With

.VBE.MainWindow.Visible = False
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.StatusBar = ""
End With

End Sub[/vba]Example attached...

[Philcjr]

John,
THANK YOU! THANK YOU!

Your code works like a charm. I changed the ".OnAction = ""Sheet2..." to ".OnAction = ""Sheet05..."

I will study your coding. It seems that applying the code when the workbook opens was/is the way to go.

Thanks agian,
Phil

[johnske]

So can we mark this solved Phil?

[Philcjr]

Yes, I looked for the "Solved" but still cant find it.

[johnske]

It's temporarily out of action, I'll do it...