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

[bemp87]

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]

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]

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]

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]

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]

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]

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

 

[godzilla185]

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]

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