Solved

Calling Class Module Sub

Posted on 2011-03-09
15
712 Views
Last Modified: 2012-05-11
What is the trick to calling a Sub in a Class Module?  I had vbSendMail working but needed to get "WithEvents" error handling working so needed to more the code into a class module.  Now I can't call my Send_EMail sub (sub not defined error).  I've attached my cSendMail class module.  Below is how I call my sub:
   Call Send_EMail(strAlias, strSubject, strMessage & vbNewLine & "(EM-28)", strForm, MySendObjectType, Do_not_ask)

Option Compare Database
Option Explicit
' *****************************************************************************
' Required declaration of the vbSendMail component (withevents is optional)
' You also need a reference to the vbSendMail component in the Project References
' *****************************************************************************
Private WithEvents poSendMail As vbSendMail.clsSendMail  'need to be a class module for WithEvents to work

Private Sub poSendMail_SendFailed(Explanation As String)
  ' vbSendMail 'SendFailed Event
  MsgBox ("Your attempt to send mail failed for the following reason(s): " & vbCrLf & Explanation)
End Sub

Public Sub SMTP_EMail_Sender(strRecipient As String, strSubject As String, strMessage As String, Optional strForm As String, Optional strCC As String)
'This routine sends e-mail using vbSendMail.dll
  Dim strServer As String
  Dim strFromDisplayName As String
  Dim strFrom As String
  
  Set poSendMail = New clsSendMail
  strServer = "mail.medtronic.com"
  strFrom = "robert.schmitt@medtronic.com"
  strFromDisplayName = "DTL Database"
  
  With poSendMail
    ' **************************************************************************
    ' Optional properties for sending email, but these should be set first
    ' if you are going to use them
    ' **************************************************************************
    .SMTPHostValidation = VALIDATE_NONE         ' Optional, default = VALIDATE_HOST_DNS
    .EmailAddressValidation = VALIDATE_SYNTAX   ' Optional, default = VALIDATE_SYNTAX
    .Delimiter = ";"                            ' Optional, default = ";" (semicolon)

    ' **************************************************************************
    ' Basic properties for sending email
    ' **************************************************************************
    .SMTPHost = strServer                  ' Required the fist time, optional thereafter
    .from = strFrom                       ' Required the fist time, optional thereafter
    .FromDisplayName = strFromDisplayName         ' Optional, saved after first use
    .Recipient = strRecipient                     ' Required, separate multiple entries with delimiter character
    .RecipientDisplayName = strRecipient      ' Optional, separate multiple entries with delimiter character
    .CcRecipient = strCC                        ' Optional, separate multiple entries with delimiter character
'    .CcDisplayName = txtCcName                  ' Optional, separate multiple entries with delimiter character
'    .BccRecipient = txtBcc                      ' Optional, separate multiple entries with delimiter character
    .ReplyToAddress = strFrom              ' Optional, used when different than 'From' address
    .Subject = strSubject                  ' Optional
    .Message = strMessage                     ' Optional
'    .Attachment = Trim(txtAttach.Text)          ' Optional, separate multiple entries with delimiter character

    ' **************************************************************************
    ' Additional Optional properties, use as required by your application / environment
    ' **************************************************************************
'    .AsHTML = bHtml                             ' Optional, default = FALSE, send mail as html or plain text
'    .ContentBase = ""                           ' Optional, default = Null String, reference base for embedded links
'    .EncodeType = MyEncodeType                  ' Optional, default = MIME_ENCODE
'    .Priority = etPriority                      ' Optional, default = PRIORITY_NORMAL
'    .Receipt = bReceipt                         ' Optional, default = FALSE
'    .UseAuthentication = bAuthLogin             ' Optional, default = FALSE
'    .UsePopAuthentication = bPopLogin           ' Optional, default = FALSE
'    .UserName = txtUserName                     ' Optional, default = Null String
'    .Password = txtPassword                     ' Optional, default = Null String, value is NOT saved
'    .POP3Host = txtPopServer
'    .MaxRecipients = 100                        ' Optional, default = 100, recipient count before error is raised
    
    ' **************************************************************************
    ' Advanced Properties, change only if you have a good reason to do so.
    ' **************************************************************************
    ' .ConnectTimeout = 10                      ' Optional, default = 10
    ' .ConnectRetry = 5                         ' Optional, default = 5
    ' .MessageTimeout = 60                      ' Optional, default = 60
    ' .PersistentSettings = True                ' Optional, default = TRUE
    ' .SMTPPort = 25                            ' Optional, default = 25

    ' **************************************************************************
    ' OK, all of the properties are set, send the email...
    ' **************************************************************************
    ' .Connect                                  ' Optional, use when sending bulk mail
    .Send                                       ' Required
    ' .Disconnect                               ' Optional, use when sending bulk mail
  End With
End Sub

Public Sub Send_EMail(strName As String, strSubject As String, strMessage As String, Optional strForm As String, _
                Optional MySendObjectType As Access.AcSendObjectType = acSendNoObject, Optional Do_not_ask As Boolean = True, _
               Optional OutputFormat As accSendObjectOutputFormat = accOutputHTML, Optional EditMessage = False, Optional CC As String, Optional BCC As String)
'New - General SMTP e-mail routine (all e-mail should go through this common routine)
If Not debugging Then
  On Error GoTo err_Send_EMail_SMTP
End If
  Dim clsSendObject As accSendObject
  Dim intResponse As Integer
  Dim strCommandLineParam As String
'  Dim strCC As String
  
  If IsNull(strName) Or strName = "" Then
    MsgBox "E-Mail address missing so E-Mail cannot be sent", vbInformation, "E-Mail Notice (MUI-24)"
    GoTo exit_Send_EMail_SMTP
  End If
  
'  Set clsSendObject = New accSendObject
  If Do_not_ask = False Then  'Ask
    intResponse = MsgBox("Press cancel if you DO NOT want an automated Email message to be sent to " & strName & vbNewLine & _
                         "  E-Mail Subject = " & strSubject _
                         , vbOKCancel, "User Input (MUI-25)")
    If intResponse = vbCancel Then  'If user doesn't want to send e-mail then exit
      GoTo exit_Send_EMail_SMTP
    End If
  End If
  strCommandLineParam = Trim(Command)  '7/6/09
'uncomment tbd  If strName = LoggedEmployeeEMail_Address And strCommandLineParam <> "Maintenance" Then  'Don't send e-mail to person logged in but send if maintenance updated 7/6/09
''    MsgBox "Email will not be sent to you because you made the entry", vbInformation, "User Notice (MUI-20)"
'uncomment  Else
    CC = "robert.schmitt@medtronic.com"  'for test
    Call SMTP_EMail_Sender(strName, strSubject, strMessage, , CC)
'Public Sub SMTP_EMail_Sender(strRecipient As String, strSubject As String, strMessage As String, Optional strForm As String, Optional strCC As String)
'uncomment  End If
exit_Send_EMail_SMTP:
  Exit Sub
err_Send_EMail_SMTP:
  If Err.Number = 2295 Or Err.Number = -2147221229 Then
    LogEvt "Email could not be sent to " & strName & ". You need to manually notify the person", vbInformation, "Warning (MUI-26)"
  Else
    LogEvt "Error Number " & Err.Number & vbNewLine & Err.Description, vbCritical, "Send_EMail_SMTP Error (MUI-27)"
    Resume exit_Send_EMail_SMTP
  End If
End Sub

Open in new window

0
Comment
Question by:schmir1
  • 8
  • 6
15 Comments
 
LVL 84
ID: 35088378
If you're working with a Class module, you must build and work with an instance of that Class module:

Dim mMyClassModule As TheNameOfYourClassModule

Set mMyClassModule = New TheNameOfYourClassModule

YOu can then "call" the methods of your class:

mMyClassModule.SendEmail YourArgumentsHere



0
 

Author Comment

by:schmir1
ID: 35088680
I'm a little slow today.  Do you have an example?  
0
 
LVL 84
ID: 35088721
The code you provided above - is this in a Class module, or have you stored this in a Standard module?

If it's in a Class module, what is the name of the Class you built?

Please be VERY specific with this, and make sure that you are 100% sure what type of container you're working with.

0
 

Author Comment

by:schmir1
ID: 35088768
The code that I attached is in a class module that I named "cSendMail".  I had it in a regular module and it worked except for the WithEvents.  So I moved it into the class module "cSendmail" to get the WithEvents to work.  At least I'm hoping the WithEvents will work.
0
 
LVL 84
ID: 35088889
So you're hoping to use the poSendMail Events? If so, then when you are viewing your cSendMail class, you should find an object named "poSendMail" in the leftside combo box (above the editor). Select that object, and then examine the items in the rightside comob. You should see any events which are exposed by the poSendMail class. You can use those in YOUR class to manage other actions and processes.

To use them, select one in the rightside combo. VBA will insert it into your cSendMail class module, and you can the write code in that sub/function to react when that event is raised by poSendMail.

I've included an image that may help
VBA-ObjectProps-Dropdown.png
0
 
LVL 21
ID: 35089798
Note: When using vbSendMail you are already using a class.

You might want to look at this: Previous Thread
0
 

Author Comment

by:schmir1
ID: 35095947
So do I put the following in my code that is call Send_EMail?

Dim mMyClassModule As TheNameOfYourClassModule

Set mMyClassModule = New TheNameOfYourClassModule

YOu can then "call" the methods of your class:

mMyClassModule.SendEmail YourArgumentsHere

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:schmir1
ID: 35095964
Also I do have the event poSendMail_SendFailed exposed as I need it to be.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35096234
I'm not sure what you mean by this. The sample code that I supplied would be used to build and "instance" of a class named "TheNameOfYourClassModule". That "instance" would be named "mMyClassModule", and you could then access the public properties and methods of "TheNameOfYourClassModule" through that instance.

Class modules are basically just like a Standard Module, except you can have more than one of them "open" at one time, and each of those "instances" act independent of any other instance.

From your posts, it would seem that you have a class named "cSendMail" which contains the code you posted at the beginning of this question. If that is correct, then in order to build a new instance of that, you'd do this:

Dim mcMyMail as cSendMail

Set mcMyMail = New cSendMail

mcMyMail.Send_Email(Your arguments here)

Also, your "SendFailed" event that you've sunk in my cSendMail class: According to good OOP design, this should NOT raise a MsgBox, but should instead do something else - like fire an event from cSendMail, or "fail" the Send_Email method for example (which would be reported back to the UI, assuming you've done so in your code).







0
 

Author Comment

by:schmir1
ID: 35097088
You are absolutely right.  I plan on having the SendFail send me e-mail for all bad address.

Sorry I was being so dumb.  I just didn't believe it could be that easy so I tried it and it worked prefectly.  My old e-mail class module works differently.  It uses the Docmd to call it.  It works (for Outlook 203 so I didn't mess with it.  Call is as follows:

DoCmd.SendObject acSendNoObject, , , Employee_Alias(strOwner), , , strSubject, strMessage, False
0
 

Author Closing Comment

by:schmir1
ID: 35097099
Excellent answer as always.
0
 
LVL 84
ID: 35097951
Thanks for the kind words.

You might also want to consider moving all those input arguments (like strName, strSubject, etc) to Class Properties. To do that, you would need to declare a class-level variable to hold each, and then add Properties. For example:

[General Declarations]
Private msName As String

Property Let EmailName(sName As String)
  msName = sName
End Property

Property Get EmailName As String
  EmailName = msName
End Property

This would add a new item to your class instances where you could "set" the email name. You'd then change your Email send routine to look at that internal property, instead of passing in the value directly via the command call:

Public Sub Send_EMail(strSubject As String, strMessage As String, Optional strForm As String, _
                Optional MySendObjectType As Access.AcSendObjectType = acSendNoObject, Optional Do_not_ask As Boolean = True, _
               Optional OutputFormat As accSendObjectOutputFormat = accOutputHTML, Optional EditMessage = False, Optional CC As String, Optional BCC As String)

<other code>

Call SMTP_EMail_Sender(msName, strSubject, strMessage, , CC)   <<<<< NOTE THE CHANGE HERE

<other code>

End Sub

This isn't a requirement, but is simply a suggestion as you move forward with in working with Class modules.

(strName As String, strSubject As String, strMessage As String, Optional strForm As String, _
                Optional MySendObjectType As Access.AcSendObjectType = acSendNoObject, Optional Do_not_ask As Boolean = True, _
               Optional OutputFormat As accSendObjectOutputFormat = accOutputHTML, Optional EditMessage = False, Optional CC As String, Optional BCC As String
0
 

Author Comment

by:schmir1
ID: 35098122
That sounds like a great idea.  Now that I'm getting into class modules I would like to do them correctly.  Thanks again for your help.

Robert
0
 
LVL 84
ID: 35098597
If you're serious about using class modules in VB/VBA, one of the best books on the subject is this one:

Visual Basic 6 Business Objects:
http://www.amazon.com/Visual-Basic-6-0-Business-Objects/dp/186100107X

Yes, it's an older book but it gives you a very good introduction to class modules. My only beef with the book is the use of UDT's to move data around. This creates a lot of overhead, and is unnecessary IMO. Otherwise, it's a good book and will serve you well in your beginning journey with class module programming.
0
 

Author Comment

by:schmir1
ID: 35098686
Thanks.  I'll get it.
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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 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

18 Experts available now in Live!

Get 1:1 Help Now