vbams-accesswaitcdo.message

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


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.


Solution

  • 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.