VBA Attach sheet unsaved file

[vipett]

I have an Excel sheet where I create an order list, then I have a button that exports the order list in to a new excel sheet, which works fine.
But I would also like to have a option that copies the order in the original document to a new workbook, then attaches that sheet to an e-mail but I don't want to save the file prior to attaching it to the e-mail.

This is my current code, partly taken from Attach Current Workbook into an Email Using Macros | MyExcelOnline
But I get an error, that the file name can't be found..
Any ways around this?

Sub Export_Send()

    Application.ScreenUpdating = False
    
    Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
    
    Sheets("Ordersheet").Select
    Sheets("Ordersheet").Copy Before:=Sheets(4)
    Sheets("Ordersheet (2)").Select
    ActiveSheet.Shapes.Range(Array("Button 3")).Select
    Selection.Delete
    ActiveSheet.Shapes.Range(Array("Button 4")).Select
    Selection.Delete
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.Delete
    ActiveSheet.Shapes.Range(Array("Button 2")).Select
    Selection.Delete
    Range("C3").Select
    Cells.Select
    Range("C3").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Ordersheet (2)").Select
    Sheets("Ordersheet (2)").Name = "OrderToExcel"
    Range("D71").Select
    Sheets("OrderToExcel").Select
    Application.CutCopyMode = False
    Sheets("OrderToExcel").Move
    Sheets("OrderToExcel").Select
    
    With OutlookMail
.To = "support@myexcelonline.com"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With

Set OutlookMail = Nothing
Set OutlookApp = Nothing

End Sub

 

[vipett]

I looked around a little and found another way, where it saves the file in a temp directory, but when I run this, it attaches the main workbook, not the newly created one..
Everytime I run the macro, it creates a new book, "Book1.xls", "Book 2.xls", etc. How do I tell the macro to select the newly created workbook and send only that?

Sub Export_Send()

  
    Dim OlApp As Object
    Dim NewMail As Object
    Dim TempFilePath As String
    Dim FileExt As String
    Dim TempFileName As String
    Dim FileFullPath As String
    Dim MyWb As Workbook


    Set MyWb = ThisWorkbook

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Sheets("Ordersheet").Select
    Sheets("Ordersheet").Copy Before:=Sheets(4)
    Sheets("Ordersheet (2)").Select
    Range("C3").Select
    Cells.Select
    Range("C3").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Ordersheet (2)").Select
    Sheets("Ordersheet (2)").Name = "OrderToExcel"
    Sheets("OrderToExcel").Select
    Application.CutCopyMode = False
    Sheets("OrderToExcel").Move
    Sheets("OrderToExcel").Select
    
TempFilePath = Environ$("temp") & "\"
    'Now get the extension of the file
    'below line will return the extension
    'of the file
    FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
    'Now append a date and time stamp
    'in your new file

    TempFileName = MyWb.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss")

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

    'Now save your currect workbook at the above path
    MyWb.SaveCopyAs FileFullPath

    'Now open a new mail

    Set OlApp = CreateObject("Outlook.Application")
    Set NewMail = OlApp.CreateItem(0)

    On Error Resume Next
    With NewMail
        .To = "victor.pettersson@itab.com"
        .Subject = "Type your Subject here"
        .Body = "Type the Body of your mail"
        .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
    On Error GoTo 0

    'Since mail has been sent with the attachment
    'Now delete the temp file from the temp folder

    Kill FileFullPath

    'set nothing to the objects created
    Set NewMail = Nothing
    Set OlApp = Nothing

    'Now set the application properties back to true
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

Set OutlookMail = Nothing
Set OutlookApp = Nothing

End Sub