Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 738
  • Last Modified:

Calling Class Module Sub

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
schmir1
Asked:
schmir1
  • 8
  • 6
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
schmir1Author Commented:
I'm a little slow today.  Do you have an example?  
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
schmir1Author Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Note: When using vbSendMail you are already using a class.

You might want to look at this: Previous Thread
0
 
schmir1Author Commented:
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
 
schmir1Author Commented:
Also I do have the event poSendMail_SendFailed exposed as I need it to be.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
schmir1Author Commented:
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
 
schmir1Author Commented:
Excellent answer as always.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
schmir1Author Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
schmir1Author Commented:
Thanks.  I'll get it.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now