Temporary File Error with SendMail

[NicB.]

Hi everyone,

Have a stumper. I am trying to finalize some code that emails the active workbook with the click of a button. I want to rename the workbook to a specific format, so I am saving a copy to Excel's temporary location, using .sendmail, and then killing the file.

I am having two problems with my code. First, I tested the code by saving the file to my desktop. This allows me to repeat the procedure indefinitely. However, when I change the savecopyas location to be Excel's default, it causes an error after the first time saying it cannot access the file that was just deleted in the first run. I cannot figure out what is different.

The second problem I have is that I have the renamed workbook open in a new window. When I kill and close it, the window is still present in my taskbar, although clicking on it makes it go away. I have tried screenupdating = true, but that didn't help.

I am pasting the string of code with the static save as location and the string of code with the temporary (dynamic to user settings) save as location. Any thoughts would be greatly appreciated.

NicB.

Failed Code - Works first time but not after that unless I close Excel and reopen.

Sub Failing_Code ()

Dim WB1 As Workbook
Dim WB2 As Workbook
Dim WBName As String
Set UserName = Range("B1")
Set CompanyName = Range("B2")

Set WB1 = ActiveWorkbook
WBName = "Profile from " & UserName & " at " & CompanyName
WB1.SaveCopyAs WBName & ".xls"
Set WB2 = Workbooks.Open(WBName & ".xls")
WB2.Activate
With WB2
.SendMail "[email protected]", _
"Profile from " & UserName & " at " & CompanyName

ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close True

End With

WB1.Activate

End Sub

Working code - works as many times as I run the macro.

Sub Working_Code()

Dim WB1 As Workbook
Dim WB2 As Workbook
Dim WBName As String
Set UserName = Range("B1")
Set CompanyName = Range("B2")

Set WB1 = ActiveWorkbook
WBName = "Profile from " & UserName & " at " & CompanyName
WB1.SaveCopyAs "C:\Documents and Settings\default user\Desktop\" & WBName & ".xls"
'WBName
Set WB2 = Workbooks.Open("C:\Documents and Settings\default user\Desktop\" & WBName & ".xls")
WB2.Activate
With WB2
.SendMail "[email protected]", _
"Profile from " & UserName & " at " & CompanyName

ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close True

End With

WB1.Activate

End Sub

[NicB.]

Nevermind, I found the solution. :-)