Solved

Trap external events in MS-Access VBA calling vbSendMail

Posted on 2009-04-05
10
1,047 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now