B
Bob
I noticed there are messages to deal with the Outlook Security message to
Click Yes when automating sending a mail message. This will occur using the
SendMail command in VBA.
This is a work around only. I do not have access to change or want to even
mess with changing Exchange Server settings. I have several Excel 2000
workbooks that do routing and this security message is a real nusiance. I
just put this code together and have not fully applied it.
I did a bit of code and got something to work. It still needs more work but
it does work. What I have done is compile an exe from VB6 and then launch
it from my VBA Excel project. The app runs for approx. five minutes and
then closes. That may be too long or too short depending on your code and
how many prompts you get.
I would like some reposting of anyone having something better or improving
this code.
As normal use at own risk. I don't think you will have a problem though.
Thanks,
Bob
========================================
----------------------------------------------------------------------------
--
Excel VBA used for testing:
'***********************************
'Excel file and compiled Exe are in same folder.
'***********************************
'For the shell command to work I had to change to the dir. Not sure why.
'This code for Excel VBA.
ChDir ActiveWorkbook.path
'start my vb app giving the name I used when compiling.
RetVal = Shell("RunOutlookPrompt.exe",1)
ActiveWorkBook.SendMail Recipients:="Smith, Bob"
----------------------------------------------------------------------------
--
'Use this Code in VB6 project.
'This code requires a reference to:
'Microsoft WMI Scripting
'Form1
'Timer1 added to the form with 3000 as the interval you can change.
'use for pausing sleep [number]
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public mCounter As Long 'counter used to close app after 5 or so minutes
Private Sub Form_Load()
Form1.Visible = False
'don't run twice
'Kill Timer and unload
If App.PrevInstance = True Then
Form1.Timer1.Interval = 0
Form1.Timer1.Enabled = False
Unload Me
End If
End Sub
Private Sub Timer1_Timer()
'Below are pauses before sending keys.
'Depending on your PC speed etc. you may need to alter
'the time of the pause.
'The key pause is the first one that detects the Microsoft Outlook
caption.
'There is a progress bar that runs so timing is real important at
this point.
'add to counter declared as a Public varible.
mCounter = mCounter + 1
'Checking for 100 with 3sec loop ends up around 5 minutes.
If mCounter = 100 Then
'Use this line for testing.
'MsgBox "unloading Outlook Checker"
Unload Me 'close the app
End If
DoEvents
Set wshshell = CreateObject("WScript.Shell")
'Wait until the application has loaded - Check every number of seconds
set on Timer1.
'I am using 3000 for my interval.
While wshshell.AppActivate("Microsoft Outlook") = False
Exit Sub 'exit without running code.
Wend
DoEvents
'found the caption now wait for the Yes key to
'appear.
Sleep 5000 'try pause that works for you if 5 isn't long enough.
DoEvents
'Bring the application to the foreground
wshshell.AppActivate "Microsoft Outlook"
Sleep 200
'Send a Left Arrow key to move to Yes Button
wshshell.SendKeys "{Left}"
Sleep 200
'Bring the application to the foreground
wshshell.AppActivate "Microsoft Outlook"
Sleep 200
'Send key to press Enter Key to continue with sending message.
wshshell.SendKeys "{ENTER}"
Sleep 200
End Sub
Click Yes when automating sending a mail message. This will occur using the
SendMail command in VBA.
This is a work around only. I do not have access to change or want to even
mess with changing Exchange Server settings. I have several Excel 2000
workbooks that do routing and this security message is a real nusiance. I
just put this code together and have not fully applied it.
I did a bit of code and got something to work. It still needs more work but
it does work. What I have done is compile an exe from VB6 and then launch
it from my VBA Excel project. The app runs for approx. five minutes and
then closes. That may be too long or too short depending on your code and
how many prompts you get.
I would like some reposting of anyone having something better or improving
this code.
As normal use at own risk. I don't think you will have a problem though.
Thanks,
Bob
========================================
----------------------------------------------------------------------------
--
Excel VBA used for testing:
'***********************************
'Excel file and compiled Exe are in same folder.
'***********************************
'For the shell command to work I had to change to the dir. Not sure why.
'This code for Excel VBA.
ChDir ActiveWorkbook.path
'start my vb app giving the name I used when compiling.
RetVal = Shell("RunOutlookPrompt.exe",1)
ActiveWorkBook.SendMail Recipients:="Smith, Bob"
----------------------------------------------------------------------------
--
'Use this Code in VB6 project.
'This code requires a reference to:
'Microsoft WMI Scripting
'Form1
'Timer1 added to the form with 3000 as the interval you can change.
'use for pausing sleep [number]
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public mCounter As Long 'counter used to close app after 5 or so minutes
Private Sub Form_Load()
Form1.Visible = False
'don't run twice
'Kill Timer and unload
If App.PrevInstance = True Then
Form1.Timer1.Interval = 0
Form1.Timer1.Enabled = False
Unload Me
End If
End Sub
Private Sub Timer1_Timer()
'Below are pauses before sending keys.
'Depending on your PC speed etc. you may need to alter
'the time of the pause.
'The key pause is the first one that detects the Microsoft Outlook
caption.
'There is a progress bar that runs so timing is real important at
this point.
'add to counter declared as a Public varible.
mCounter = mCounter + 1
'Checking for 100 with 3sec loop ends up around 5 minutes.
If mCounter = 100 Then
'Use this line for testing.
'MsgBox "unloading Outlook Checker"
Unload Me 'close the app
End If
DoEvents
Set wshshell = CreateObject("WScript.Shell")
'Wait until the application has loaded - Check every number of seconds
set on Timer1.
'I am using 3000 for my interval.
While wshshell.AppActivate("Microsoft Outlook") = False
Exit Sub 'exit without running code.
Wend
DoEvents
'found the caption now wait for the Yes key to
'appear.
Sleep 5000 'try pause that works for you if 5 isn't long enough.
DoEvents
'Bring the application to the foreground
wshshell.AppActivate "Microsoft Outlook"
Sleep 200
'Send a Left Arrow key to move to Yes Button
wshshell.SendKeys "{Left}"
Sleep 200
'Bring the application to the foreground
wshshell.AppActivate "Microsoft Outlook"
Sleep 200
'Send key to press Enter Key to continue with sending message.
wshshell.SendKeys "{ENTER}"
Sleep 200
End Sub