Save a macro file in a new file without the macros

[MrsFixIt]

I am stuck.
I need to save the file without macros and attach it to an email/
I cannot get any of your examples to work/
If you have another option, I will take it.

Dim OlApp As Object
Set OlApp = CreateObject("Outlook.Application")
Dim NewMail As Object
Set NewMail = OlApp.CreateItem(0)
Dim TempFilePath As String
Dim FileFullPath As String
Dim MyWb As Workbook

Dim TempFileName As String 'A portion of the file is hardcoded below
Dim CName As String 'Replaces initials in the file name
       CName = Sheets("Script").Range("K1").Value
Dim xStrDate As String 'Date/time custom format stamp for file
       xStrDate = Format(Now, "_hh-mm_yyyymmdd")
Dim xStrDate2 As String 'Date/time custom format stamp for email text
      xStrDate2 = Sheets("Script").Range("L1").Value
Dim FileNew As String
Dim CallNums As String 'Number of calls in email text
       CallNums = Sheets("Script").Range("J1").Value

Set MyWb = ThisWorkbook



With Application
              .ScreenUpdating = False
             .EnableEvents = False
             .DisplayAlerts = False
End With





'Save your workbook in your temp folder of your system below code gets the full path of the temporary folder

'in your system

TempFilePath = Environ$("temp") & "\"
TempFileName = "Class" & CName & xStrDate

ActiveWorkbook.SaveAs Filename:=TempFilePath & TempFileName & ".xlsx", FileFormat:=51

'Complete path of the file where it is saved
FileFullPath = TempFilePath & FileNew

'Now save your current workbook at the above path
MyWb.SaveAs FileFullPath

With NewMail

.To = "first person"
.CC = "second person"
' .BCC = "third person "

.Subject = "class file " & Date

.Body = "Good morning. Here is my file with " & CallNums & " class from last night's session from " & xStrDate3 & " to " & xStrDate2 & ". " & vbNewLine & vbNewLine_

.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Display 'or use .Display to show you the email before sending it.

End With

 

[daverunt]

I think you're right, the original code was using SaveAs not SaveCopyAs.

I meant to ask. I assume as it's saving a copy of the current file that the directory to save it in is all that is required.

 

[MrsFixIt]

Thanks. I changed the xlsm into a xtlm and everything is working fine. the new xlsx is macro-free and the template is ok.
the only thing left is the Kill FileFullPath is not working and for security reasons, the data cannot be left in the temp file

 

[daverunt]

Not working isn't really helpful. Is there an error message generated?

Some points to check.
Is the file open.
Can you manually delete it when the kill fails. That will give you a clue to whether Outlook has locked the file. It would be released when the mail is sent.

 

[MrsFixIt]

hi.
you guys gave me enough help that made it possible for me to leave and visit my grandchildren. THANKS so much.
You are correct and the file is still open as an email attachment.
Also, the file is created by a night shift and they are borrowing a desk which is why I cannot do any manual cleanup.
It stalls at the FullPathKill with no message and no data in the variable.
I will try the TempPath and see how it goes.

And again, thanks.

 

[daverunt]

I'll take a look if you post the latest code you are using now.