Emailing active WS as Excel attachment failing

[juscuz419]

	
	

		
Sub createandmailDailySummaryExcel()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim WB As Workbook
    Dim ws As Worksheet
    Dim strbody As String
    Dim sEmailAddress As String
      
   'Makes a copy of the active sheet and saves it to a temporary file
    ActiveSheet.Copy
    Set WB = ActiveWorkbook
    FileName = "Daily Summary.xlsx"
    On Error Resume Next
The following code is failing at the WBSaveAs line.     

 Kill "C:\" & FileName
    On Error GoTo 0
    WB.SaveAs FileName:="C:\" & DailySummary.xlsx
        
   'Create a temporary workbook and copy the active worksheet to it
    Set WB = Workbooks.Add
    ws.Copy Before:=WB.Sheets(1)
    
   'Save the temporary workbook
    WB.SaveAs "C:\Temp\DailySummary.xlsx"
    
   'Create the email object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
   'Compose the email
    With OutMail
        .To = sEmailAddress  'Specify the email address of the recipient
        .Subject = "Daily Summary Excel Sheet"
        .Body = "Attached is an Excel copy of the Daily Summary"
        .Attachments.Add WB.FullName
        .Display
    End With
    
   'Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing
    WB.Close SaveChanges:=False
    Kill "C:\Temp\TempWorkbook.xlsx"
    Set WB = Nothing
    Set ws = Nothing
End Sub
	

 

[NoSparks]

Suggest you use Option Explicit to force declaration of all variables.

FileName is an Excel key word and shouldn't be used for a variable name.

You are not setting ws prior to copying it.

Are you sure you have your workbook names correct,
Daily Summary.xlsx, DailySummary.xlsx and TempWorkbook.xlsx ?

I used Drive D: instead of C: and this works for me

Option Explicit
    
Sub createandmailDailySummaryExcel()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim WB As Workbook
    Dim ws As Worksheet
    Dim strbody As String
    Dim sEmailAddress As String
    Dim Fname As String             '<-----
    
   'Makes a copy of the active sheet and saves it to a temporary file
    'ActiveSheet.Copy
    Set ws = ActiveSheet            '<-----
    ws.Copy
    Set WB = ActiveWorkbook
    Fname = "Daily Summary.xlsx"    '<-----
    
'The following code is failing at the WBSaveAs line

    On Error Resume Next            'incase no file found
    ' Kill "C:\" & FileName
    Kill "D:\" & Fname              'changed to drive I'm using
    On Error GoTo 0                 're-enable error notifications
    
    'WB.SaveAs Filename:="C:\" & DailySummary.xlsx
    WB.SaveAs Filename:="D:\" & Fname   '<-----
    WB.Close SaveChanges:=False         '<-----
    
   'Create a temporary workbook and copy the active worksheet to it
    Set WB = Workbooks.Add
    ws.Copy Before:=WB.Sheets(1)
    
   'Save the temporary workbook
    'WB.SaveAs "C:\Temp\DailySummary.xlsx"
    WB.SaveAs "D:\Temp\DailySummary.xlsx"

   'Create the email object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
   'Compose the email
    With OutMail
        .To = sEmailAddress  'Specify the email address of the recipient
        .Subject = "Daily Summary Excel Sheet"
        .Body = "Attached is an Excel copy of the Daily Summary"
        .Attachments.Add WB.FullName
        .Display
    End With
    
   'Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing
    WB.Close SaveChanges:=False
    'Kill "C:\Temp\TempWorkbook.xlsx"
    Kill "D:\Temp\" & "DailySummary.xlsx"
    Set WB = Nothing
    Set ws = Nothing
End Sub

 

[juscuz419]

I put the name of the actual workbook in the Fname = statement, and the code fails at the WBSave statement as before.

So in the above code, there are three places to specify filenames.

Fname = "Timesheet_Master_Basic.xlsm" '<----- Should this be the name of the active workbook with it's extension (xlsm)?

WB.SaveAs Filename:="D:\" & Fname '<-----This should just pull the Fname from the statement above, correct?

WB.SaveAs "D:\Temp\DailySummary.xlsx"'<-----This is the name that I want the temporary file and the attachment to use and I DO want to save it as an xlsx not xlsm.

 

[NoSparks]

In the code you posted, why the first Kill ? You don't save the file there.
You say the save fails but don't say what the error is.

Using a USB stick in my computer it is Drive D: and has a Temp folder already on it.
I run this macro from a workbook the name doesn't matter, only the active sheet does.
I wouldn't save a DailySummary without a date in the workbook name, the time is included here too.
This works for me.

Sub Email_Daily_Summary()
    
    Dim OutApp As Object, OutMail As Object
    Dim WB As Workbook, ws As Worksheet
    Dim strbody As String, sEmailAddress As String, fname As String
    
    'The name the Summary will be saved as
    fname = "DailySummary " & Format(Now, "ddmmmyyyy hh_mm_ss_") & ".xlsx"
    
   'Makes a copy of the active sheet and saves it to a temporary file
    Set ws = ActiveSheet
    ws.Copy
    Set WB = ActiveWorkbook
    WB.SaveAs Filename:="D:\Temp\" & fname
        
   'Create the email object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
   'Compose the email
    With OutMail
        .To = sEmailAddress  'Specify the email address of the recipient
        .Subject = "Daily Summary Excel Sheet"
        .Body = "Attached is an Excel copy of the Daily Summary"
        .Attachments.Add WB.FullName
        .Display
    End With
    
   'Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing
    WB.Close SaveChanges:=False
    'Kill "D:\Temp\" & fname
    Set WB = Nothing
    Set ws = Nothing
End Sub

 

[juscuz419]

This indeed works for me too if I address a thumb drive (mine is E). When I try to get it to point back to my C drive the macro stops at the WBSaveAs line. It actually creates the sheet with a name of Book1, but the macro does not finish. Must be something with my computer and not the VBA.

 

[NoSparks]

Book1 is the default name Excel gives the newly created file, the name doesn't get changed until it is saved.
When the macro stopped at the WBSaveAs line I guess the error message was too cryptic to know straight away what the issue is.
Any way you now know where the problem is.

Good Luck with the project