Greetings,
I am new to VBA and macros so there might be simple fix to this problem. I have a macro that creates an email from the active sheet. Everything works fine and the macro performs flawlessly, however as soon as it is finished none of the other macros or objects (checkboxes) on the sheet work until you click in a cell. Then everything seems to reset and work fine again. I would like people to be able to email the sheet and then come right back to it and be able to use the print button without having to click anywhere else.
I did try to set the focus at the end back to one of the cells but that didn't seem to work either.
Here is the code for reference:
Private Sub Email_Utility()
Dim oApp As Object
Dim oMail As Object
Dim LWorkbook As Workbook
Dim LFileName As String
Dim cNNumber As String
Dim cSerial As String
Dim cFrmDate As String
Dim htmlBody As String
Dim ws As Worksheet
ActiveSheet.Name = Range("AE50").Value
cNNumber = Cells(3, "AB").Value
cSerial = Cells(3, "AL").Value
cFrmDate = Cells(2, "AL").Value
'Turn off screen updating
Application.ScreenUpdating = False
'Copy the active worksheet and save to a temporary workbook
ActiveSheet.Copy
Set LWorkbook = ActiveWorkbook
'Create a temporary file in your current directory that uses the name
' of the sheet as the filename
LFileName = LWorkbook.Worksheets(1).Name
On Error Resume Next
'Delete the file if it already exists
Kill LFileName
On Error GoTo 0
'Save temporary file
LWorkbook.SaveAs Filename:=LFileName
'Create an Outlook object and new mail message
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = "user@experiment.com"
.Subject = "RTS Flight Request (" & cNNumber & "/" & cSerial & ") " & cFrmDate
.htmlBody = "See attached 'Pending' RTS Flight<br><br>"
.htmlBody = .htmlBody & "<span style='background:yellow'>Estimated maintenance completion date: ______</span><br><br>"
.htmlBody = .htmlBody & "Thank you!"
.Attachments.Add LWorkbook.FullName
.Display
End With
'Delete the temporary file and close temporary Workbook
LWorkbook.ChangeFileAccess Mode:=xlReadOnly
Kill LWorkbook.FullName
LWorkbook.Close SaveChanges:=False
'Turn back on screen updating
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
Set ws = ActiveSheet
ws.Range("AL2").Select
End Sub
Any help is greatly appreciated. Thanks!
Bookmarks