access vba wait for code to finish (send E-Mail via CDO)

Question

I have the following code:

Sub OutputExpences()
Dim strPath As String
Dim FileName As String
Dim TodayDate As String

TodayDate = Format(Date, "DD-MM-YYYY")
strPath = Application.CurrentProject.Path & "\Temp\"
FileName = "Report-Date_" & TodayDate & ".xlsx"

DoCmd.OutputTo acOutputForm, "frmExpences", acFormatXLSX, strPath & FileName, False
            '*** Check Network Connection ***
            If IsInternetConnected() = True Then
                ''' connected
                EmailToCashier
            Else
                ''' no connected
            End If
            '*** Check Network Connection ***
Kill strPath & FileName
End Sub



 Public Sub EmailToCashier()
 Dim mail    As Object           ' CDO.MESSAGE
 Dim config  As Object           ' CDO.Configuration
 Dim strPath As String
 Dim FileName As String
 Dim TodayDate As String

 TodayDate = Format(Date, "DD-MM-YYYY")
 strPath = Application.CurrentProject.Path & "\Temp\"
 FileName = "Report-Date_" & TodayDate & ".xlsx"

     Set mail = CreateObject("CDO.Message")
     Set config = CreateObject("CDO.Configuration")

     config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
     config.Fields(cdoSMTPServer).Value = "smtp value"
     config.Fields(cdoSMTPServerPort).Value = 465
     config.Fields(cdoSMTPConnectionTimeout).Value = 10
     config.Fields(cdoSMTPUseSSL).Value = "true"
     config.Fields(cdoSMTPAuthenticate).Value = cdoBasic
     config.Fields(cdoSendUserName).Value = "email value"
     config.Fields(cdoSendPassword).Value = "password value"
     config.Fields.Update
     Set mail.Configuration = config

     With mail
         .To = "email"
         .From = "email"
         .Subject = "subject"
         .TextBody = "Thank you."
         .AddAttachment strPath & FileName
         .Send
     End With

     MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"

     Set config = Nothing
     Set mail = Nothing
 End Sub

I need to wait(user can't press anything or do anything) until all the code is finished.

EmailToCashier is sending the output file to email so it is taking time(from 2-15sec depending the network connection and file size).

Thank you.

What is your question? And why are you exporting a form to Excel workbook? — Parfait, Jun 04 '17 at 17:14
The relevant function that you probably need to modify is `EmailToCashier`. Please edit your question and add its code. — Andre, Jun 04 '17 at 19:00
Since Access is a single thread application, isn't everything "locked" on the form anyway until the sub is ended? Unless *EmailToCashier* involves vbScript.Run somewhere... — Kostas K., Jun 04 '17 at 21:49
Interesting. From [here](http://www.vbforums.com/showthread.php?784001-Progress-bar-to-show-progress-in-sending-a-file-using-cdo): *All CDO methods are synchronous.* -- So it should already behave as you want. — Andre, Jun 05 '17 at 18:23

score 2 · Accepted Answer · answered Jul 14 '18 at 16:38

2

I create a form frmWait with modal and popup. So, I first open frmWait and then send my email. After email, the form close.

Simple and it is working fine.

answered Jul 14 '18 at 16:38

YvetteLee

1,057
3
13
27

score 1 · Answer 2 · answered Jun 05 '17 at 04:48

Use Application.wait

Sub OutputExpences()
Dim strPath As String
Dim FileName As String
Dim TodayDate As String

TodayDate = Format(Date, "DD-MM-YYYY")
strPath = Application.CurrentProject.Path & "\Temp\"
FileName = "Report-Date_" & TodayDate & ".xlsx"

DoCmd.OutputTo acOutputForm, "frmExpences", acFormatXLSX, strPath & FileName, False
            '*** Check Network Connection ***
            If IsInternetConnected() = True Then
                ''' connected
                EmailToCashier
            Else
                ''' no connected
            End If
            '*** Check Network Connection ***
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 15
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
Kill strPath & FileName
End Sub

`Application.Wait` is only available in Excel VBA. And a constant wait time is usually the very last resort for something that varies a lot. 15 seconds is dull if the operation only takes 2 seconds, but doesn't work if it takes 16 seconds. — Andre, Jun 05 '17 at 07:34

score 0 · Answer 3 · answered Jan 15 '18 at 13:41

I usually write in Access but a lot of VBA is the same. Whenever I have a long process, I try to give the user something to look at that gives them the status. In your case, why not create a separate userform that simply says "Please Wait. Sending Mail." Open it as a modal popup (not dialog) when you run EmailToCashier and close it just before your message box. This should allow your code to run but prevent user input until control is returned.

access vba wait for code to finish (send E-Mail via CDO)

3 Answers3