Get Full Path for Unsaved File to attach to Outlook mail

user_6836766 0
Viewed 86

I'm trying to create a copy of a worksheet and email only the copy as an attachment. It will not attach the ActiveWorkbook, and I think it's because it's never saved, so it's in a temporary location.

If I use Activeworkbook.Path, it 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.

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 = "ABC@123.com"
    .Subject = Subject
    .Body = Body
    .Attachments.Add (Attachment)
    .Display 
End With 
End Sub
outlook vba excel
1 Answers

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 = "ABC@123.com"
        .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
user_98140690
Related