Excel macro to create Outlook message not returning focus to cell after completion

[PPettit]

I've been playing with this code that I found which allows me to click a button in an Excel 2010 spreadsheet and generate an email (Outlook 2010) with an attachment. It works fairly well except for one thing: Once the new mail window is closed (either by cancelling or sending the message), I have to select an unprotected cell in the spreadsheet or hit the Escape key before I can interact with the sheet again. I've tried various things to try and programmatically change the focus to one of the cells or ranges, but nothing has worked so far. Anyone have an idea on how to return to the spreadsheet with a cell/range selected and ready to edit?

Sub Mail_ActiveSheet()
'Working in 2000-2010
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    
    Dim strCompany As String
    Dim strLease As String
    Dim strWell As String
    Dim strDate As String
    Dim strEmailrecipients As String

    With Worksheets("Well Optimization Charges")
        strCompany = .Range("range_Company").Value
        strDate = Format(.Range("range_Date").Value, "mm-dd-yyyy")
        strLease = .Range("range_Lease").Value
        strWell = .Range("range_Well").Value
    End With
    
    With Worksheets("Data")
        strEmailrecipients = .Range("table_EmailRecipients")(1)
    End With
    
    If strCompany = "" Then
        MsgBox "You must enter a company name before sending this file.", vbCritical, "Missing Company Name"
    Else
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set Sourcewb = ActiveWorkbook
    
        'Copy the sheet to a new workbook
        ActiveSheet.Copy
        Set Destwb = ActiveWorkbook
    
        'Determine the Excel version and file extension/format
        With Destwb
            If Val(Application.Version) < 12 Then
                'You use Excel 2000-2003
                FileExtStr = ".xls": FileFormatNum = -4143
            Else
                'You use Excel 2007-2010, we exit the sub when your answer is
                'NO in the security dialog that you only see  when you copy
                'an sheet from a xlsm file with macro's disabled.
                If Sourcewb.Name = .Name Then
                    With Application
                        .ScreenUpdating = True
                        .EnableEvents = True
                    End With
                    MsgBox "Your answer is NO in the security dialog"
                    Exit Sub
                Else
                    Select Case Sourcewb.FileFormat
                    Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                    Case 52:
                        If .HasVBProject Then
                            FileExtStr = ".xlsm": FileFormatNum = 52
                        Else
                            FileExtStr = ".xlsx": FileFormatNum = 51
                        End If
                    Case 56: FileExtStr = ".xls": FileFormatNum = 56
                    Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                    End Select
                End If
            End If
        End With
    
        '    'Change all cells in the worksheet to values if you want
        '    With Destwb.Sheets(1).UsedRange
        '        .Cells.Copy
        '        .Cells.PasteSpecial xlPasteValues
        '        .Cells(1).Select
        '    End With
        '    Application.CutCopyMode = False
    
        'Save the new workbook/Mail it/Delete it
        TempFilePath = Environ$("temp") & "\"
        'TempFileName = "Part of " & Sourcewb.Name & " " _
        '             & Format(Now, "dd-mmm-yy h-mm-ss")
        TempFileName = strCompany & " - " & strLease & " - " & strWell & " - " & strDate
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        With Destwb
            .SaveAs TempFilePath & TempFileName & FileExtStr, _
                    FileFormat:=FileFormatNum
            On Error Resume Next
            With OutMail
                .To = "address@domain.com"
                .CC = ""
                .BCC = ""
                .Subject = TempFileName
                .Body = ""
                .Attachments.Add Destwb.FullName
                'You can add other files also like this
                '.Attachments.Add ("C:\test.txt")
                '.Send   'or use .Display
                .Display
            End With
            On Error GoTo 0
            .Close SaveChanges:=False
        End With
    
        'Delete the file you have send
        Kill TempFilePath & TempFileName & FileExtStr
    
        Set OutMail = Nothing
        Set OutApp = Nothing
             
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        
    End If
End Sub

 

[ClulessChris]

maybe

.range("your_range_address").Select

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[PPettit]

Pretty sure I tried that, but I did it again just now. I put it right before the end of the sub. Didn't change anything.

 

[SkipVought]

The SHEET must already be active before you can select a cell on the SHEET.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[UnsolvedCoding]

Try this

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

Thisworkbook.activate


If that doesn't work, look over this link to see how it reverts back to the desired Excel sheet.

http://www.tek-tips.com/viewthread.cfm?qid=1667608

 

[SkipVought]

Anyone have an idea on how to return to the spreadsheet with a cell/range selected and ready to edit?

        'Delete the file you have send
        Kill TempFilePath & TempFileName & FileExtStr
    
        [b]Sourcewb.Sheets("[highlight]WhatSheet[/highlight]").Cells(1, 1).Select[/b]
    
        Set OutMail = Nothing
        Set OutApp = Nothing
             
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        
    End If
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PPettit]

Same behavior. Still can't interact with anything until I select an unprotected cell or hit the Escape key. I don't know if this helps any, but I can't even see which cell/range is selected until I hit the Escape key.

 

[SkipVought]

sorry, try this...

with Sourcewb
  .activate
  .Sheets("WhatSheet").activate
  .Cells(1, 1).Select
end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PPettit]

The cell selection errored out, so I tried some variations which also didn't work. However, I also tried this:

With Sourcewb
    .Activate
    .Sheets("Well Optimization Charges").Activate
End With
        
With ActiveSheet
    .Unprotect
    .Range("range_Company").Select
    .Protect
End With

and it appears to work more or less like I want. I can now interact with the sheet (i.e. press a button or begin entering data) without manually selecting an unprotected cell or hitting the Escape key. If I remove the protection statements, I get the old behavior. Any idea why protecting and unprotecting the sheet makes a difference?

 

[SkipVought]

It depends what properties your sheet protection has. For instance, it is possible to protect a sheet so that no cells can be selected.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PPettit]

Hmm... These are the properties that I see:

Protect sheet window:
Protect worksheet and contents of locked cells (checked)
Password: (none)
Allow all users of this worksheet to:
Select unlocked cells (checked)
everything else is unchecked

Format Cells (related to the range):
nothing checked

 

[SkipVought]

Select unlocked cells (checked)

Well, are there ANY cells that are UNLOCKED?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PPettit]

Yes, of course there are cells that are unlocked (as mentioned earlier in this post). To be more specific, the ranges (and the cells they contain) that I've been trying to select were never locked. I've even tried single cells that weren't locked. In addition to this, I've tried running the macro without the sheet protection turned on. This has similar issues to the original problem. The most noticeable difference being that if I click one of the macro buttons, the button is selected for editing as if I were going to modify the button itself (i.e. the Drawing Tools tab appears). Perhaps, this is the root of the problem.

 

[Hoaokapohaku]

PPettit...I don't have all the answers, but I think you are on the right track in your last post. I created a workbook and added the relevant sheets, named ranges, etc and ran your macro but could not duplicate your problem UNTIL I added an ActiveX Command Button to initiate the macro. When I did that, I duplicated your problem, except the Command Button was not selected as in design mode, but it was highlighted as a button with the focus with no cell selected. Then I added a form control button and also assigned your macro to that button. When I run the macro from the Form control, everything runs fine and the problem is not duplicated. I can't tell you why, but I think if you replace your ActiveX Command Button with a Form Control Button, your problem may disappear.

 

[PPettit]

I double-checked my buttons and they're Form Controls, not ActiveX Controls. Looks like my issue is the reverse. The ActiveX button works without unprotecting and protecting the sheet. Odd...

Side question: In general, is one type of control better to use than the other?

 

[PPettit]

I looked into the differences between the control types and think I'm going to switch to the ActiveX controls. My understanding is that the Forms Controls are the old way of doing things and lack some flexibility.

For further reading:
[URL unfurl="true"]http://answers.microsoft.com/en-us/office/forum/office_2003-customize/guidelines-for-when-to-use-activex-control-versus/c6924148-72e2-4ce2-8686-5739751ce7f1[/url]

[URL unfurl="true"]http://office.microsoft.com/en-us/excel-help/overview-of-forms-form-controls-and-activex-controls-on-a-worksheet-HA010237663.aspx[/url]