How to bring userform to front or setfocus?

[mexmanz]

Hi All,


I am using the code below to save and email a new copy of a workbook, this all works fine (with thanks to Ron de Bruin!)

It allows the Outlook message box:
'a program is trying to automatically send e-mail on your behalf. Do you want to allow this?....Yes/No/Help'
to appear - I am happy with this!

The problem I have is if the user chooses 'No' by mistake on the Outlook msgbox.
I have a userform that runs if the user selects 'No' but if Outlook is already open during the process the userform runs in the background behind Outlook. Is there a way to bring the userform frmSendEmailError to display on top of Outlook after the user selects 'No'?

----------------------------------------------------------------------

'Code to save file as unique name with date stamp (iCode = Item Code)
iCode = Worksheets("DataChangeRequestForm").Range("C4").Value
fName = ("\\FILE1\Shared Files by User\User\Forms\Change Request\Autosave\Data Change Request - " & iCode)
NewFile = fName & " " & Format(Date, "dd-mm-yyyy")

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=NewFile
Application.DisplayAlerts = True

'Code to email form
'This sends the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error GoTo CancelClick_Error
'On Error Resume Next

Application.ScreenUpdating = False

With OutMail
.To = "procurement.qc@royalberkshire.nhs.uk"
.CC = ""
.BCC = "andrew.rivas@royalberkshire.nhs.uk"
.Subject = "Change Request - " & iCode
.Body = "Pharmacy Data Change Request Form Attached - " & iCode
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Exit Sub

'This is the userform to allow user to retry if No selected from Outlook message, how can I
'bring this in front of Outlook/setfocus for this userform?

CancelClick_Error:
frmSendEmailError.Show
frmSendEmailError.cmdRetry.SetFocus
End Sub

-------------------------------------------------------------------------------


Thank you

 

[Johnny C]

You can swap between Outlook and Excel having focus using the following:

AppActivate ("Microsoft Excel")

or

AppActivate ("Microsoft Outlook")

(I'm not sure if you use "Microsoft Outlook", I think so but as I've got Lotus Notes (hack spit) at the moment I can't check)

 

[mexmanz]

Hi Johnny,

Thanks for this, I have tried both in the code:

CancelClick_Error:
AppActivate "Microsoft Excel"
frmSendEmailError.Show
frmSendEmailError.cmdRetry.SetFocus

and

CancelClick_Error:
AppActivate ("Microsoft Excel")
frmSendEmailError.Show
frmSendEmailError.cmdRetry.SetFocus

I get runtime error '5'
Invalid procedure all or argument.

each time. How should I include the line?

thanks