VBA: Delete sheet then mail workbook

[Groovey]

Hi guys!
ATTENTION NEWBIE IN ACTION
So i recently started working with excel and VBA at work, and got hooked right away! However, now i'm standing in neck deep in spreadsheet(... get it?) and need some fresh eyes to point out my error. The goal is to have a macro that once executed, will delete a single sheet, then e-mail the workbook as an attachment. However, at the moment, it only deletes the sheet from the original workbook, and not the attachment...

(Credit to Excel Automation - Ron de Bruin for showing me the way of the spreadsheet)

Sub MyBrokenSub()

Dim MailApp As Object
Dim MailCreate As Object
Dim WB1 As Workbook

Set MailApp = CreateObject("Outlook.Application")
Set MailCreate = OutApp.CreateItem(0)
Set WB1 = ActiveWorkbook

With WB1
Sheets("MySheet").Delete
End With

On Error Resume Next
With OutMail
.to = ”something@something.totallyalegitdomain"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add WB1.FullName
.Send
End With
On Error GoTo 0

Set MailApp = Nothing
Set MailCreate = Nothing
End Sub

 

[CalcSux78]

I think the ActiveWorkbook reference isn't targeting the book you are intending.

[FONT=Arial Narrow]Set MailApp = CreateObject("Outlook.Application")
    Set MailCreate = OutApp.CreateItem(0)
    Set WB1 = ActiveWorkbook

    With WB1
        Sheets("MySheet").Delete
    End With[/FONT]

Try: Set WB1 = Workbooks(filename)

Then in the With WB1 section, put a dot before your Sheets("MySheet").Delete or replace entire with statement with:
WB1.Sheets("MySheet").Delete

 

[Groovey]

I think the ActiveWorkbook reference isn't targeting the book you are intending.



Try: Set WB1 = Workbooks(filename)

Then in the With WB1 section, put a dot before your Sheets("MySheet").Delete or replace entire with statement with:
WB1.Sheets("MySheet").Delete

Unfortunately this did not work. Im thinking about:

MySheet.Cells.Clear

and then

Sheets("MySheet").Visible = xlVeryHidden

Thoughts?

 

[Michael M]

Don't have Excel at the moment, so UNTESTED....but try

Sub MyBrokenSub()

Dim MailApp As Object, outmail As Object, thisWb As Workbook

Set MailApp = CreateObject("Outlook.Application")
Set outmail = OutApp.CreateItem(0)
Set thisWb = ActiveWorkbook
ActiveWorkbook.SaveAs Filename:=thisWb.Path & "\new workbook.xls"
Set tempWB = ActiveWorkbook
Application.DisplayAlerts = False
    tempWB.Sheets("MySheet").Delete
Application.DisplayAlerts = True
On Error Resume Next
With outmail
.to = "" '”something@something.totallyalegitdomain"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add tempWB.FullName
.Send
End With
On Error GoTo 0

Set MailApp = Nothing
Set MailCreate = Nothing
End Sub

 

[Groovey]

Don't have Excel at the moment, so UNTESTED....but try

Sub MyBrokenSub()

Dim MailApp As Object, outmail As Object, thisWb As Workbook

Set MailApp = CreateObject("Outlook.Application")
Set outmail = OutApp.CreateItem(0)
Set thisWb = ActiveWorkbook
ActiveWorkbook.SaveAs Filename:=thisWb.Path & "\new workbook.xls"
Set tempWB = ActiveWorkbook
Application.DisplayAlerts = False
    tempWB.Sheets("MySheet").Delete
Application.DisplayAlerts = True
On Error Resume Next
With outmail
.to = "" '”something@something.totallyalegitdomain"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add tempWB.FullName
.Send
End With
On Error GoTo 0

Set MailApp = Nothing
Set MailCreate = Nothing
End Sub

I fixed it... Kicking myself for not thinking of it...
With WB1
Application.DisplayAlerts = False
WB1.Sheets("MySheet").Delete
WB1.Save
Application.DisplayAlerts = True
End With
Thank you all for the input. Im impressed with how fast people stand ready to help you.