Get Full Path for Unsaved File

Question

I'm trying to take a workbook, then create a copy of the worksheet in that workbook, and email only the copy as an attachment. However, it will not attach the ActiveWorkbook, and I think it's because it's never saved it, so it's in a temporary location. I cannot seem to find a way to get it to attach this unsaved copy that I've created. If I use Activeworkbook.Path, it only gives the file name, and not the full path. If I use ThisWorkbook.Path it gives the path of the workbook I created the copy from, and not the copy itself. See code below. Any help is appreciated. Thanks

Sub DemandEM() 
Dim OutApp As Object 
Dim Outmail As Object 
Dim Subject As String 
Dim Body As String
Dim Attachment As String

Subject = "DMND NP" & Sheets("Loading").Cells(4, 2).Value 
Body = "Please see attachment for NP" & Sheets("Loading").Cells(4, 2).Value
Sheets("Demand Input").Copy
Attachment = ActiveWorkbook.Path

Set OutApp = CreateObject("Outlook.Application") 
OutApp.Session.Logon 
Set Outmail = OutApp.CreateItem(0)

With Outmail
    .to = "[email protected]"
    .Subject = Subject
    .Body = Body
    .Attachments.Add (Attachment)
    .Display 
End With 
End Sub

Answer 1

Outlook: Send Newly Created Workbook

• This will save your created one-sheet workbook in the ThisWorkbook's path under the name Demand Input.xlsx and close it. After the remaining outlook code, it will delete the file.

Option Explicit

Sub DemandEM()
    Dim OutApp As Object
    Dim Outmail As Object
    Dim Subject As String
    Dim Body As String
    Dim dFilePath As String
    
    Subject = "DMND NP" & Sheets("Loading").Cells(4, 2).Value
    Body = "Please see attachment for NP" & Sheets("Loading").Cells(4, 2).Value
    ThisWorkbook.Sheets("Demand Input").Copy ' create new one-sheet workbook
    With Workbooks(Workbooks.Count)
        dFilePath = ThisWorkbook.Path & "\" & "Demand Input.xlsx"
        Application.DisplayAlerts = False ' overwrite without confirmation
        .Save dFilePath
        Application.DisplayAlerts = True
        .Close SaveChanges:=False
    End With
    
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set Outmail = OutApp.CreateItem(0)
    
    With Outmail
        .to = "[email protected]"
        .Subject = Subject
        .Body = Body
        .Attachments.Add dFilePath
        .Display
    End With

    ' If you want to keep the file, out-comment the following line.
    Kill dFilePath
    
End Sub