Emailing 1 sheet

[Pookiemeister]

This code seems to create a book of the first page of the workbook. I tried the following code:

Sub Email()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim Filename As String
    Dim wbAnswer, EmilAnswer As Integer
        
    Application.ScreenUpdating = False
        
    Worksheets("WO Report Summary 4.0").Copy
    Set wb = ActiveWorkbook
    
    Filename = wb.Worksheets(1).Name
    On Error Resume Next
    
    Kill Filename
    On Error GoTo 0
    wb.SaveAs Filename:="C:\" & Filename
        
   
    EmailAnswer = MsgBox("Do you want to email this file?", vbYesNo + vbDefaultButton1 + vbQuestion, "Email: " & sFileName)
    If EmailAnswer = vbYes Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
            
        With OutMail
'            .To =
'            .CC =
'            .Subject =
            .Attachments.Add [B]I am not sure what to put here. I would assume the file path would go here but I don't see that here in the code. [/B]
            .Display
        End With
        
        Application.ScreenUpdating = True
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    Else
        Exit Sub
    End If
    wbAnswer = MsgBox("Are you sure you want to close this workbook and Excel?", vbYesNo + vbDefaultButton1 + vbQuestion, "WORKBOOK: " & ActiveWorkbook.Name)
    If wbAnswer = vbYes Then
        ActiveWorkbook.Save
        Application.Quit
    Else
        Exit Sub
    End If


End Sub

 wb.SaveAs Filename:="C:\" & Filename

does not display the file path. When I run this code, it asks to "Save 'Book #' with references to unsaved documents?" I don't even know where that is located. I also need to save this file with a specific name. "WO Report" & Format(now, "mm.dd.yyyy"). Thank you

 

[Micron]

Things I would change:
FileName is a parameter/property of the SaveAs method. I'd never use it as a variable name lest I cause ambiguity in the code.

wbAnswer is a variant because you didn't explicitly declare the data type. Probably not an issue here and maybe you meant to do that anyway.

I very seldom use Resume Next with error handling, and never when something isn't working. It will mask the reason for errors and the issues they cause.

I use Option Explicit lest I mis-type a variable name. I can tell that you are not because you didn't declare wb, sFileName or EmailAnswer.

How can you Kill Filename (or sFilename if that's what it should be) if you haven't created it yet? And if you had, how can you add it as an attachment if you kill it first?

If you don't specify file type I believe the default is xlsx. If the sheet you're copying contains code, you'll error because you can't save xlsm files as xlsx. You have to specify the file type, and you will have to cycle DisplayAlerts before and after the save, or you'll get an alert. On that note, you're trying to save a file as whatever the sheet name is; like C:\Sheet1

Still looking; thought I'd post what I've seen so far.
EDIT -SaveAs is for workbooks. You'd have to copy the sheet into a new wb and use the path of the new wb when doing .Attachments.Add

 

[Pookiemeister]

Thank you for the quick response. I made some adjustments to the code. I have Option Explicit at the beginning of the workbook

Sub Email()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim sFilename As String
    Dim wbAnswer, EmailAnswer As Integer
        
    Application.ScreenUpdating = False
        
    Worksheets("WO Report Summary 4.0").Copy
    Set wb = ActiveWorkbook
    
    sFilename = wb.Worksheets(1).Name
        
    Kill sFilename
    On Error GoTo 0
    wb.SaveAs sFilename:="C:\" & sFilename
        
    EmailAnswer = MsgBox("Do you want to email this file?", vbYesNo + vbDefaultButton1 + vbQuestion, "Email: " & sFilename)
    If EmailAnswer = vbYes Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
            
        With OutMail
'            .To =
'            .CC =
'            .Subject =
            .Attachments.Add sFilename
'            .Body =
            .Display
        End With
        
        Application.ScreenUpdating = True
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    Else
        Exit Sub
    End If
    wbAnswer = MsgBox("Are you sure you want to close this workbook and Excel?", vbYesNo + vbDefaultButton1 + vbQuestion, "WORKBOOK: " & ActiveWorkbook.Name)
    If wbAnswer = vbYes Then
        ActiveWorkbook.Save
        Application.Quit
    Else
        Exit Sub
    End If


End Sub

Now on the above code. I receive a compile error message "Named argument not found" The first sFilename is highlighted in blue on this line.

wb.SaveAs sFilename:="C:\" & sFilename

Is that because it was killed before it was saved? Thank you.

 

[Micron]

I'm surprised that you don't raise an error trying to save at that level of C drive. Try this - might need tweaking because I get a prompt to save the workbook that the code is in. Suggest you play with a copy wb, not your current one. You will have to change references, such as the sheet name being copied.

Sub Email()
Dim OutApp As Object, OutMail As Object
Dim wb As Workbook, wbNew As Workbook
Dim ws As Worksheet
Dim sFilename As String
Dim wbAnswer As Integer, EmilAnswer As Integer, EmailAnswer As Integer

Set ws = Worksheets("Sheet1")
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

sFilename = "C:\users\Micron\Documents\Excel\" & ws.Name & ".xlsx"
ws.Copy
With ActiveWorkbook
    .SaveAs Filename:=sFilename, FileFormat:=51
    .Close
End With
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With

EmailAnswer = MsgBox("Do you want to email this file?", vbYesNo + vbDefaultButton1 + vbQuestion, "Email: " & sFilename)
If EmailAnswer = vbYes Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
'       .To =
'       .CC =
'       .Subject =
        .Attachments.Add sFilename
        .Display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
Else
    Exit Sub
End If
wbAnswer = MsgBox("Are you sure you want to close this workbook and Excel?", vbYesNo + vbDefaultButton1 + vbQuestion, "WORKBOOK: " & ActiveWorkbook.Name)
If wbAnswer = vbYes Then
    ActiveWorkbook.Save
    Application.Quit
Else
    Exit Sub
End If

End Sub

You can't kill a file you didn't create. Kill means to delete - best be careful with that.

EDIT - I had so many issues that I might have ended up with unused variables.

 

[Micron]

Dim OutApp As Object, OutMail As Object
Dim ws As Worksheet
Dim sFilename As String
Dim wbAnswer As Integer, EmailAnswer As Integer 'why not just use 1 variable 2x?

maybe more accurate

 

[Pookiemeister]

Thank you for your help. So I tested your code. This is a .xlsm file but I need to send it as a .xls file. So, the people I send it to, don't mess it up. Is it possible to do a "SaveAs" and name the file the same but as an .xls extension? So at work, I know it will automatically as to "Add sensitivity label". Once you set the label it open Outlook with the file attached but when you open the file the first time, it does if you want to enable Macros? If you say yes, all the data on the spreadsheet turns to "#" sign. Thank you again.

 

[Micron]

Got family over. Can't play with it right now. It saves as xlsm even though the path ends in xlsx? Check out

vba saveas file enumeration.

I thought xlsx was type 51. Maybe not. Or find value for xls in the documentation.