[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trap external events in MS-Access VBA calling vbSendMail

Posted on 2009-04-05
10
Medium Priority
?
1,136 Views
Last Modified: 2013-11-27
I have an MS-Access VBA routine that calls vbSendMail, using a CLASS module.
vbSendMail documentation (at www.freevbcode.com/ShowCode.Asp?ID=109)  says that the DLL will raise the SendSuccessful event if successful, else the SendFailed event after sending the mail.  I presume that I do not instantiate these in my main module.
Firstly, these events in my Class module are not called;  secondly I am not clear how I pass the obtained result back to my main module and how to make the main module wait until the relevant event has fired.
In calling the Property Get for the return status, I get a compile error "Argument not optional" - why?  how should I code this?
In the Class module events, I have a temporary Msgbox statement to confirm that the event is indeed not being called, lest the problem was just in passing the status back to the main module.
David
This follows on from www.experts-exchange Q_24265648.html
Sorry experts, but I closed that one a bit too soon.  Anyhow, here is your chance to earn some more points. (Am I being a bit mean - is this issue worth much more?)
Class module:
----------------
Private gstrReturnStatus As String
,
 Public Sub SendSuccesful()
  MsgBox "Email sent successfully"
  gstrReturnStatus = "OK"
  End Sub
 '
Public Sub SendFailed(P_Explanation As String)
  MsgBox "Email send failed: " & vbCrLf & P_Explanation
  gstrReturnStatus = P_Explanation
End Sub
'
Public Property Get ReturnStatus(ByVal Value As String)
  Value = gstrReturnStatus
End Property
'
' Main module
'---------------
  Dim MailStatus As String
  Dim I, OpenForms
  On Error GoTo Err_mySendOneEMail
  
  ' Initialize the MailAgent:
  Dim objMail As New Class2MailAgent
  MailStatus = ""
  objMail.Sender_id = P_user_id
  objMail.Recipient_id = P_Recipient_id
  objMail.Subject = P_subject
  objMail.Message = P_message
  objMail.Attachment = P_Attachment
  Call objMail.clSendMail
 '
 ' Wait until we get a return status:  
' See http://office.microsoft.com/en-us/access/HA012288271033.aspx
 For I = 1 To 150000
   If I Mod 1000 = 0 Then
     OpenForms = DoEvents       ' surrender resources to other processes
     Call objMail.SendMail_SendSuccesful
     MailStatus = objMail.ReturnStatus()    ' Compile error
   MsgBox "Status was " & MailStatus
   End If
 If MailStatus <> "" Then
   Next I
 End If
 MsgBox "Email status = " & MailStatus
'' End

Open in new window

0
Comment
Question by:David_Loxley
  • 5
  • 5
10 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24072305
Simple explanation of variables in a class. You define them as private. From this you create a get and let (optional) method. This should be the only way to get to this variable.

Now the let method should be the only one that has a argument. The get method will simply return the value of the variable in your class


Public Property Get ReturnStatus(ByVal Value As String)
  ReturnStatus = gstrReturnStatus
End Property


so to get to this

sRV = <<your class variable>>.ReturnStatus


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24072309
actually get can be optional also. Depending on the use of the variable and how you what code outside the module to reference it, you can either not bother with a get/let, create just one of them or both.
0
 

Author Comment

by:David_Loxley
ID: 24076252
Fine, so I have fixed the Class module:
   Public Property Get ReturnStatus(ByVal Value As String)
     ReturnStatus = gstrReturnStatus
   End Property
'
but I still get 'Argument not optional' compile error in the main module statement:
   mMailStatus = objMail.ReturnStatus
'
and, most important of all, how do I debug the fact that the Class Public Sub SendSuccesful and/or SendFailed are not being called.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 24076520
get doesnt take an argument

Public Property Get ReturnStatus()
     ReturnStatus = gstrReturnStatus
End Property

lets do this one step at a time
0
 

Author Comment

by:David_Loxley
ID: 24082142
Yes, woke up to the fact that I had created the Property Get ReturnStatus by cloning the structure of the Property Let, complete with input parameter.  Changed that to remove the input parameter and all compiles cleanly.   Sorry, I'm a dork.  
So now back to the core problems:
1) Why does vbSendMail not call the Sub SendSuccesful or Sub SendFailed?  Thier doco is inconsistent;  in one place it says "Public Event SendSuccessful()" but then gives a coding example "Private sub poSendMail_SendSuccessful()"
So do I code an Event or a Sub, or how do a link an Event to a Sub?
2) What is the neatest most efficient way of creating a delay loop in the main module to wait for either event to fire?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24085276
SendSuccessful and SendFailed are just the events raised, it is not the function name you have to create.

To handle them, you need to use the event handlers if your vbSendMail.clsSendMail instance.

In your class, if you pick your instance from the top dropdown (left hand side), the right hand side contains the events handled

In your class, you have defined it as clMailer (clMailer As vbSendMail.clsSendMail)

So you should see event handlers of SendSuccessful and SendFailed. This event handler will be called after you call clMailer.Send

Now since this is inside the class, it is not available to the caller (your objMail instance) so best thing here is to define your own variables then have a get method

eg defined in top of class (underneath clMailer definition is ideal)

Private errorMsg As String


then the post send event handlers

Private Sub clMailer_SendFailed(Explanation As String)
    errorMsg = Explanation
End Sub

Private Sub clMailer_SendSuccesful()
    errorMsg = ""
End Sub

Public Property Get LastErrorMessage()
    LastErrorMessage = errorMsg
End Property




example calling

    objMail.clSendMail "Testmessage", "fred@smith.com"

    If objMail.LastErrorMessage <> "" Then
        MsgBox "Send failed with " & objMail.LastErrorMessage
    End If



0
 

Accepted Solution

by:
David_Loxley earned 0 total points
ID: 24141306
Yes, Easter and your comments have brought resurrection, life and joy to my project.
I had not appreciated that the required name for the event handler (clMailer_SendSuccesful ) is built from the instance name (clMailer) and the Event-name concatenated with a '_'.  Now it works well for me - you will get the points.
A last minor query:  is my 'Wait' loop in the main module, as in the attached snippet, the best and simplest way to do it?

Thanks, David
Sub WaitForStatusReturn()
Dim I, OpenForms
 ' Wait until we get a return status:  
 ' See http://office.microsoft.com/en-us/access/HA012288271033.aspx
 For I = 1 To 150000
   If mMailStatus <> "" Then
     Exit For
   Else
     If I Mod 1000 = 0 Then
       OpenForms = DoEvents ' surrender resources to other processes
     End If
   End If
 Next I
End Sub

Open in new window

0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 2000 total points
ID: 24145370
Is this for waiting for a response from sending the email?
Only concern would be if it takes a while to send, the app would just be waiting, will give the appearance of hanging.
But apart from that, looks okay, considering it is from ms.
0
 

Author Comment

by:David_Loxley
ID: 24156606
Everything looking great.  Thanks for helping me through the learning curve.
David
0
 

Author Comment

by:David_Loxley
ID: 24156617
.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question