So, I'm not even sure how to approach this issue, but on a hunch I decided to try sendkeys hoping it would help. So far...no. So here's my issue. I created a database that sends auto-emails and at the same time saves an Excel workbook based on filtered information. The problem is, when I go to save, the company I work for puts a security box in play that asks me to verify I'm the author (defaults my name in the text box) and gives me four options as to what type of doc it is so it knows how to encrypt it (this is also defaulted). With the push of enter it's gone and the code can continue to save as normal. Emails sent by my Access DB will be in the hundreds at some point and it would be crucial to get this working without interaction.
So my first attempt to bypass this was to use the sendkeys command (I know not ideal), but all it does is adds a line under my code, it's not sending the key to the active window. Another complication is the security window is not triggered until the actual save code is implemented. I tried to pause the actual sendkey function until after that save line is run, but I don't think I'm doing that right.
Anyone have any suggestions on a better way to do this or perhaps just some help on cleaning up what I have? My theory was to activate the sendkeys command with pause allowing the save to happen and bam the timing with be enough for the enter key to fire...but hey doesn't it always seem like things go better in your head?
Here is the code I'm working with. Any suggestions are welcome, thank you in advance.
So my first attempt to bypass this was to use the sendkeys command (I know not ideal), but all it does is adds a line under my code, it's not sending the key to the active window. Another complication is the security window is not triggered until the actual save code is implemented. I tried to pause the actual sendkey function until after that save line is run, but I don't think I'm doing that right.
Anyone have any suggestions on a better way to do this or perhaps just some help on cleaning up what I have? My theory was to activate the sendkeys command with pause allowing the save to happen and bam the timing with be enough for the enter key to fire...but hey doesn't it always seem like things go better in your head?
Here is the code I'm working with. Any suggestions are welcome, thank you in advance.
Code:
'if false then save is false and only for viewing
If bOpen = False Then
Call mSendKey
objCreateWb.SaveAs FileName:=strFiledirectory
objCreateWb.Close
End If
Public Sub mSendKey()
Dim EnterKey As String
EnterKey = "~"
SendKeys EnterKey, True
Call Pause(1)
End Sub
Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause
Dim PauseTime As Variant, start As Variant
PauseTime = NumberOfSeconds
start = Timer
Do While Timer < start + PauseTime
DoEvents
Loop
Exit_Pause:
Exit Function
Err_Pause:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
Resume Exit_Pause
End Function