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]

Hi,

Can't see a value for FileNew in the code?

TempFilePath = Environ$("temp") & "\"
TempFileName = "test 06 Jul 20"

FileNew = TempFileName & ".xlsx" '-------------FileNew variable populated

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=TempFilePath & TempFileName, FileFormat:=51 '---.xlsx superfluous. Already specified by using FileFormat

FileFullPath = TempFilePath & FileNew

Application.DisplayAlerts = True

 

[MrsFixIt]

Hi,

Can't see a value for FileNew in the code?

TempFilePath = Environ$("temp") & "\"
TempFileName = "test 06 Jul 20"

FileNew = TempFileName & ".xlsx" '-------------FileNew variable populated

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=TempFilePath & TempFileName, FileFormat:=51 '---.xlsx superfluous. Already specified by using FileFormat

FileFullPath = TempFilePath & FileNew

Application.DisplayAlerts = True

Thanks.
I will give this a try and get back to you.
How can I add another person to the email.

 

[daverunt]

.To = "first person; second person" - for name strings
Or if using cell values.
.To = Range("A1").Value & "; " & Range("B1").Value

 

[MrsFixIt]

Neat and thanks again.
I am about to hit the button, but I am worried that my original file will be gone and the macro will be lost.

 

[MrsFixIt]

Stuck again
It cannot get past MyWb.SaveCopyAs FileFullPath

 

[daverunt]

Hi,

First before running any macro in a file that is important, zip it so you have a backup!

FileFullPath is the full path of the xlsx file. It is not a directory.
To save a file in the temp directory use TempFilePath.

 

[Fluff]

If you use SaveCopyAs the file must the same type of file, so you cannot use it if you want convert an xlsm file to an xlsx file.

 

[daverunt]

Thanks Fluff. Never used it myself but I thought that would be the case.
I assumed the poster wanted to save a copy of the original in with the xlsx version

 

[Fluff]

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