Solved

Trap external events in MS-Access VBA calling vbSendMail

Posted on 2009-04-05
10
1,041 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

746 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

16 Experts available now in Live!

Get 1:1 Help Now