N
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 "(e-mail address removed)", _
"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 "(e-mail address removed)", _
"Profile from " & UserName & " at " & CompanyName
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close True
End With
WB1.Activate
End Sub
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 "(e-mail address removed)", _
"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 "(e-mail address removed)", _
"Profile from " & UserName & " at " & CompanyName
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close True
End With
WB1.Activate
End Sub
