CODEWITHSUNDEEP
excelemailvba
user1135218
user1135218

Reputation: 403

Focus back to Excel once email has been created with VBA

I have a VBA macro which does the following:

  1. Creates a report, saves it to main directory. Closes the report
  2. Creates various emails, attaches the report to each (doesn't send the email)
  3. Msgbox shows up showing that process is finished

The thing is that, once the whole process is finished, I want the user to know it is done, but this code doesn't return the focus to excel (which I hoped would be the problem)

Windows(the_current_window).Activate
Worksheets(currentQuoteSheet).Select

The last email created is what stays on screen. The msgbox doesn't come up, only if I click on excel icon on the menu bar it does. So users are there just waiting to finish (when it has, in fact :( )

Any ideas how to make sure the msgbox shows up after last email is created without users having to click on excel?

Upvotes: 3

Views: 26173

Answers (6)

Derek Brown
Derek Brown

Reputation: 1

You can also call the application. When I need to do this I query the application and place it in a variable

ThisFile = ActiveWorkbook.Name

and the activate the file, which activates the Window

AppActivate (ThisFile)

Upvotes: 0

TychaBrahe
TychaBrahe

Reputation: 49

This drove me nuts last night, and I kept going to sites by Microsoft and such that said to use AppActivate("Excel") or AppActivate("Microsoft Excel"), neither of which would work.

Looking at this, I assumed that I was calling the app name. I needed to switch focus back to Microsoft Excel, which is why I was calling it in the AppActive statement. But that's not the case.

You're calling the window title. Now if you open a new Excel workbook it starts (in Office 365) with the name "Book1 - Excel". And it is the word "Excel" in the window title that AppActivate is looking for when you use the code AppActiveate("Excel"). But I already saved my file, and its name was stored in a variable called ShortDocName. So when I switched my code to ready AppActiave(ShortDocName) it worked as intended.

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

I am on MS Office 365 and for some reason I get an Application Error when trying AppActivate "Microsoft Excel" and AppActivate "Excel", however:

AppActivate Application.Caption

Worked for me.

Upvotes: 8

Adam Law
Adam Law

Reputation: 11

Although this works to a degree, it doesn't activate the MSGbox to allow one to press y/n on the keyboard (you can with the mouse).

This allows keyboard interaction if you are doing the task repetitively and don't want to grab your mouse. Tested Office 2013 (64bit) (remove PtrSafe if you are 32bit)

Private Declare PtrSafe Function MessageBox _
        Lib "User32" Alias "MessageBoxA" _
           (ByVal hWnd As Long, _
            ByVal lpText As String, _
            ByVal lpCaption As String, _
            ByVal wType As Long) _
        As Long
Sub Test
        'Call showwindow(ActiveWorkbook.Name)
        AppActivate "Excel"
        'I need this to retain the focus
        retval = MessageBox(&O0, "Do you want to process", "My Question", vbYesNo + vbSystemModal)
End Sub

Upvotes: 1

Sorceri
Sorceri

Reputation: 476

Try 

sub doSomething()
   'do something
   AppActivate "Microsoft Excel"
End Sub

Upvotes: 8

l85m
l85m

Reputation: 838

Have you tried calling your workbook by name? Something like:

Workbooks("EXCEL WORKBOOK NAME.XLS").Activate

Upvotes: 0

Related Questions