Avatar of schmir1
schmir1
Flag for United States of America asked on

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

Microsoft Access

Avatar of undefined
Last Comment
schmir1

8/22/2022 - Mon
Scott McDaniel (EE MVE )

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



schmir1

ASKER
I'm a little slow today.  Do you have an example?  
Scott McDaniel (EE MVE )

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.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
schmir1

ASKER
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.
Scott McDaniel (EE MVE )

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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

Note: When using vbSendMail you are already using a class.

You might want to look at this: Previous Thread
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
schmir1

ASKER
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

schmir1

ASKER
Also I do have the event poSendMail_SendFailed exposed as I need it to be.
ASKER CERTIFIED SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
schmir1

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
schmir1

ASKER
Excellent answer as always.
Scott McDaniel (EE MVE )

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
schmir1

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott McDaniel (EE MVE )

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.
schmir1

ASKER
Thanks.  I'll get it.