# Create Temp Copy of Workbook to Email (Ron De Bruin Code) - TempFilePath Environ Issue



## bemp87 (Feb 22, 2019)

Hi Community, 

Hoping you can help with an error i'm encountering when running a script to create a temporary copy of the workbook > attach it to email and send using outlook. I get an error on:

TempFilePath = Environ$("temp") & ""

The exact error message is: "Compile Error Can't Find project or library"

code provide below, and any help would be appreciated. 

Private Sub Mail_workbook_Outlook_2()
'Working in Excel 2000-2016
'Mail a copy of the ActiveWorkbook with another file name
    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object


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


    Set wb1 = ActiveWorkbook


    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & ""
    TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))


    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .to = "email@email.com"
        '.CC = "email@email.com"
        .BCC = ""
        .Subject = "Complaint Form"
        .Body = "Testing VBA Email Script"
        .Attachments.Add TempFilePath & TempFileName & FileExtStr
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0


    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr


    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub


----------



## Jeffrey Mahoney (Feb 22, 2019)

First of all, when you post code, can you please post it with the CODE tags around it.

Problem could be that you don't have a Temp folder in your "Users" folder.  Environ$ is used by Windows API to find a folder name that gets set when you login to the computer.  I would change that to wb1.Path instead.  It doesn't matter because the file is going to get deleted anyway.

Jeff


----------



## bemp87 (Feb 22, 2019)

Thanks for the feedback. I'm still getting a Compile Error Can't find project or library. Based on your suggestion i updated the code as follows:


```
TempFilePath = wb1.Path
```

Assuming this is correct, the compile error continues.  Any other guidanc


----------



## Jeffrey Mahoney (Feb 22, 2019)

In your VBA window choose TOOLS - REFERENCES on the menu.  If you see a library error, you need to solve it.  Usually by checking the box next to it.


----------



## bemp87 (Feb 22, 2019)

I checked the Rerences earlier prior to submitting the post, and there doesn't appear to be any errors. Just not sure how to get around this issue.


----------



## Jeffrey Mahoney (Feb 22, 2019)

I have the following libraries and it works for me:

* Visual Basic for Applications
* Microsoft Excel 15.0 Object Library
* OLE Automation
* Microsoft Office 15.0 Object Library
* Microsoft Forms 2.0 Object Library

Jeff


----------



## bemp87 (Feb 25, 2019)

Thanks Jeff. It was an oversight on my end, but this took care of it.


----------



## godzilla185 (Jul 13, 2022)

HI,

I like rondebruin's code, but only difference I would like to know from the above--- how do I save the file in a diff/permanent location instead of this code which uses a temp and then deletes?


----------



## Jeffrey Mahoney (Jul 15, 2022)

You can edit the 3 lines below similar to what I did.

```
TempFilePath = "C:\SomeFolderName\"
TempFileName = "FredFlintstone"
FileExtStr = ".xlsx"
```

Then remove this line

```
Kill TempFilePath & TempFileName & FileExtStr
```


----------

