How to reference a public variable in a module from a class?

[gojets1721]

Related to this post

I have a class module and a module to automate Outlook. The module is the code to send the email and the class determines if the email was actually sent. I'm trying to declare a variable in the module in order to update a form field if the email was sent.

Here is the class:

Public WithEvents itm As Outlook.MailItem
Public strDocumentation As String

Private Sub itm_Close(Cancel As Boolean)

    Dim blnSent As Boolean
  
    On Error Resume Next
    blnSent = itm.Sent
  
    If Err.Number = 0 Then
      
    Else
        Forms!frmComplaints.FollowUp = Date & ": " & strDocumentation
    End If
  
End Sub

Here is the module:

Dim OutApp As Outlook.Application
Dim itmevt As New CMailItemEvents
Dim oDocumentation As New CMailItemEvents

Public Sub EmailTemplate(sEmailTemplate As String, sTo As String, sCC As String, sBCC As String, sSubjectCap As String, sReportName As String, sCriteria As String, sFileName As String)

        Set OutApp = GetObject(, "Outlook.Application")
      
        Set OutApp = New Outlook.Application
        'If OutApp Is Nothing Then Set OutApp = New Outlook.Application
  
        Set OutMail = OutApp.CreateItem(olMailItem)
        Set itmevt.itm = OutMail
        oDocumentation.strDocumentation = "Hello"
      
        With OutMail
            .BodyFormat = olFormatHTML
            .To = sTo
            .CC = sCC
            .BCC = sBCC
            .Subject = Nz(DLookup("Subject", "tblEmailTemplates", sEmailTemplate), "") & sSubjectCap
            .HTMLBody = Nz(DLookup("Message", "tblEmailTemplates", sEmailTemplate), "")
            If Not IsMissing(sFullNamePath) Then
               .Attachments.Add (sFullNamePath)
            End If
            .Display
        End With
      
        Set OutMail = Nothing
  
    End If
  
End Sub

I have just a simple "hello" in the module line for testing. However, when I execute the module code and send an email, I can't get the form field to update with the strDocumentation. Only the date appears

Any suggestions on what I'm doing wrong?

Thanks

 

[MajP]

That design makes no sense or you are not showing us all your code.. Did you copy this from somewhere, because it looks overly complicated and incomplete and wrong. You have a class module that does nothing. A class module with Public class variables? Where do you instantiate your class? No itm declared in your class. What is oDocumentation and what is it supposed to do? Why not just trap the outlook event in a form? Those public variables are a poor design and not needed.

If you want a standard module, then maybe something like this without the public variables.

Public Function EmailTemplate(sEmailTemplate As String, sTo As String, sCC As String, sBCC As String, sSubjectCap As String, sReportName As String, sCriteria As String, sFileName As String) As Outlook.MailItem
        Dim outApp As Outlook.Application
        Dim outMail
        On Error Resume Next
        Set outApp = GetObject(, "Outlook.Application")
        On Error GoTo 0
        If outApp Is Nothing Then
           Set outApp = New Outlook.Application
        End If
        Set outMail = outApp.CreateItem(olMailItem)
      
          With outMail
            .BodyFormat = olFormatHTML
            .To = sTo
            .CC = sCC
            .BCC = sBCC
            .Subject = sSubjectCap
            .HTMLBody = "body"
           ' .Subject = Nz(DLookup("Subject", "tblEmailTemplates", sEmailTemplate), "") & sSubjectCap
           ' .HTMLBody = Nz(DLookup("Message", "tblEmailTemplates", sEmailTemplate), "") & "<p>" & StaffName & "<br>" & "Risk Management & Compliance Department"
            If Not IsMissing(sFileName) Then
            
               ' sFullNamePath = "SOME CODE NEEDED HERE OR A PARAMETER"
               '.Attachments.Add (sFullNamePath)
            End If
            .Display
        End With
    
        Set EmailTemplate = outMail
End Function

Then call it from the form something like

Private WithEvents outItm As Outlook.MailItem

Private Sub Command14_Click()
  Me.followUp = ""
  On Error Resume Next
  Set outItm = EmailTemplate("Test", "email@verizon.net", "email", "email", "Subject", "Report", "Dog = Cat", "FileName")
End Sub

Private Sub outItm_Close(Cancel As Boolean)
  On Error Resume Next
  If outItm.Sent Then
    Me.followUp = "Sentresume next " & Now
  Else
   Me.followUp = "canceled"
  End If
End Sub

 

[The_Doc_Man]

MajP showed some things in code. I would generally tend to trust his class-based code a lot. However, showing you the code doesn't always tell you WHY you do (or don't do) something, and his comments WERE a bit terse. I'll explain it to you in a different way.

The biggest problems with class modules are

(a) they don't exist until instantiated, but if not PROPERLY instantiated, they don't contain anything particularly useful.

(b) class modules CAN be instantiated more than once, in which case if you HAD a public variable you would have to qualify it to show which class object contained the variable you sought.

(c) it is possible to dissolve a class and again cause related variables to not exist, or to exist with meaningless content.

You had difficulty with a Public variable in a class module. That is because object-oriented programming has to take the above rules into account. Therefore, it is a restriction that you don't declare a Public variable in class module. Instead, you declare private variables and then define Property Get and Property Let statements to use those variables as properties of the class-related object. Using Property Let and Property Get, you automatically have to qualify which class object is being referenced and that resolves any ambiguity.

This isolation inside a class module exists because it is SUPPOSED to be a "black box" - i.e. you CAN'T see what is inside of it because you are not supposed to be able to reach into it in a way that could interfere with its operation. The "baggage" of a class object is that you restrict how you see it and how you work with it. It is a form of code isolation and protection so that you CAN'T screw up what it is doing by diddling with its inner workings.

In particular, you can get away with scope violations for form-base class modules because that is a different kind of class than one that was created entirely through VBA. You CAN declare public variables for a form but there is still the issue related to my points (a) and (c) above, occasional non-existence of the class. If you DO instantiate a form more than once and it has public variables, you would still need to qualify the form by the name of its particular instance - which usually turns into a crazy time.

Class modules are interesting and flexible ways to do things, so I don't blame you for experimenting. However, targeting a class module for Outlook must take into account that you can't have more than one instance of Outlook running on a Windows system. It has been that way since at least 2003 (the first time I played with Outlook at that level). Adding a class module as another layer to a singular facility that is also a type of class module (in this case, an externally defined class) seems to be overkill. You might do just as well to have a dedicated but general purpose (i.e. non-class) module of callable subroutines to do your Outlook work, and in that case a Public variable could possibly work with little or no difficulty. I do that all of the time for Word and Excel so an Outlook-centric module should be just fine. I don't want to stifle your desire to learn, but sometimes it is possible to overkill a particular solution.

 

[sonic8]

However, when I execute the module code and send an email, I can't get the form field to update with the strDocumentation. Only the date appears

You use two different instances of your class, itmevt and oDocumentation. You set the strDocumentation variable for the oDocumentation
instance but the mail item, which triggers the event, for the itmevt instance.
Get rid of one of one the instances and only use the other and your code will work.

PS: You should rather use the Send event instead of Close for your purpose.

 

[bastanu]

Cross-post?

Help with defining a variable in a class and declaring it in a module

I'm trying to define a variable in a class module and then declare in a module with a lookup function. In the class module (titled CMemail, I have the following: Public CMDocumentation As String and then in the module, I have: Dim strDocumentation As New CMailItemEvents

www.accessforums.net

Cheers,

 

[CJ_London]

Cross-post?

Yes but OP advised in opening post....

Other thread says pretty much the same as here, just in more detail!

 

[sonic8]

There is a first for everything. Here it is that I very much disagree with your assessment.

That design makes no sense or you are not showing us all your code..

I suggest you review the code again.

You have a class module that does nothing.

It obviously handles the Close event of the MailItem and writes to a form.

A class module with Public class variables?

Why not?
Of course, dedicated properties would be more to my liking, but the public variables work much the same.

Where do you instantiate your class?

The two objects are auto-instantiated as per the declaration in the module.
I very much dislike the auto-instantiation with the New keyword in the declaration, but I have no doubt it will create an instance of the class.

No itm declared in your class.

?
Well, I can see it. It's the very first line of code in the post.

What is oDocumentation and what is it supposed to do?

That's the only question I agree with. ;-)
itmevt and oDocumentation are two different instances of the same class and I believe they are causing the main problem.

Why not just trap the outlook event in a form?

If done right (instance created in the EmailTemplate procedure, then stored in a module level collection, result stored in a table instead of just a single form) @gojets1721's approach would allow it to open any number of emails and track when they are closed. - I suspect that is the intention of the code.

Those public variables are a poor design and not needed.

Why?
Public variables in a class module should not be confused with public variables in a "normal" module. As said before, properties would be better, but I would not call them "poor design".

 

[gojets1721]

That design makes no sense or you are not showing us all your code.. Did you copy this from somewhere, because it looks overly complicated and incomplete and wrong. You have a class module that does nothing. A class module with Public class variables? Where do you instantiate your class? No itm declared in your class. What is oDocumentation and what is it supposed to do? Why not just trap the outlook event in a form? Those public variables are a poor design and not needed.

If you want a standard module, then maybe something like this without the public variables.

Public Function EmailTemplate(sEmailTemplate As String, sTo As String, sCC As String, sBCC As String, sSubjectCap As String, sReportName As String, sCriteria As String, sFileName As String) As Outlook.MailItem
        Dim outApp As Outlook.Application
        Dim outMail
        On Error Resume Next
        Set outApp = GetObject(, "Outlook.Application")
        On Error GoTo 0
        If outApp Is Nothing Then
           Set outApp = New Outlook.Application
        End If
        Set outMail = outApp.CreateItem(olMailItem)
     
          With outMail
            .BodyFormat = olFormatHTML
            .To = sTo
            .CC = sCC
            .BCC = sBCC
            .Subject = sSubjectCap
            .HTMLBody = "body"
           ' .Subject = Nz(DLookup("Subject", "tblEmailTemplates", sEmailTemplate), "") & sSubjectCap
           ' .HTMLBody = Nz(DLookup("Message", "tblEmailTemplates", sEmailTemplate), "") & "<p>" & StaffName & "<br>" & "Risk Management & Compliance Department"
            If Not IsMissing(sFileName) Then
           
               ' sFullNamePath = "SOME CODE NEEDED HERE OR A PARAMETER"
               '.Attachments.Add (sFullNamePath)
            End If
            .Display
        End With
   
        Set EmailTemplate = outMail
End Function

Then call it from the form something like

Private WithEvents outItm As Outlook.MailItem

Private Sub Command14_Click()
  Me.followUp = ""
  On Error Resume Next
  Set outItm = EmailTemplate("Test", "email@verizon.net", "email", "email", "Subject", "Report", "Dog = Cat", "FileName")
End Sub

Private Sub outItm_Close(Cancel As Boolean)
  On Error Resume Next
  If outItm.Sent Then
    Me.followUp = "Sentresume next " & Now
  Else
   Me.followUp = "canceled"
  End If
End Sub

This worked!! Thanks so much. I found my original code online so I apologize that it was so flawed. This was very helpful

 

[gojets1721]

Yes but OP advised in opening post....

Other thread says pretty much the same as here, just in more detail!

Thank you. Apologies if I didn't label this correctly enough as a cross post.

 

[sonic8]

Therefore, it is a restriction that you don't declare a Public variable in class module.

What restriction? I think I didn't get that memo.
Sure, the approach with properties you describe is better, but I don't believe there is any such restriction you mentioned.

However, targeting a class module for Outlook must take into account that you can't have more than one instance of Outlook running on a Windows system.

But that single instance of Outlook can open any number of Mail-, Appointment-, and other items.

 

[MajP]

I suggest you review the code again.

I reviewed it and I will stand by that statement that code makes no sense as designed. Sure you can jam a square peg in a round hole. A standard module with three global variables where the class modules auto instantiate. Two of them are duplicates and the Outlook app is really local to the procedure. Unless you are a big fan of unlimited lifetime and scope and no encapsulation, then this design does not make sense.

The class is hardwired to output to a single form control. This cannot be modified. So I would guess you are calling this from that form (I could be wrong it could be a pop up and the code is being called by multiple forms. I doubt it). If you are calling it from the form then makes way more sense to do as I show.

I do not have issue with public class variables. I was referring to the Public "global" variables in the standard module. However if I expose class variables then write the variable names as something readable. Instead of "itm" use "OutlookMailItem" or something the user of the class can figure out.

f done right (instance created in the EmailTemplate procedure, then stored in a module level collection, result stored in a table instead of just a single form) @gojets1721's approach would allow it to open any number of emails and track when they are closed. - I suspect that is the intention of the code.

Maybe doable but really sloppy. If tracking multiple instances I am building a custom collection. At least I am putting all of the standard module code in the class.

 

[The_Doc_Man]

What restriction? I think I didn't get that memo.
Sure, the approach with properties you describe is better, but I don't believe there is any such restriction you mentioned.

But that single instance of Outlook can open any number of Mail-, Appointment-, and other items.

You are correct in that you CAN declare a public variable in class module. The problem is, if you do that, you must first instantiate an object of that class, and then to actually USE the public variable, you must qualify it because each instance of the object can have a different value for the same variable name. I should have been more careful by saying it is difficult to use public variables in a class module since they are not necessarily uniquely identified by the variable's name.

It is immaterial that a single instance of Outlook can handle any number of its own objects. The question was whether you needed to go as far as to layer another module over the external entry points of Outlook when a non-class module can handle the problem easily.

 

[sonic8]

I reviewed it and I will stand by that statement that code makes no sense as designed. Sure you can jam a square peg in a round hole. A standard module with three global variables where the class modules auto instantiate. Two of them are duplicates and the Outlook app is really local to the procedure. Unless you are a big fan of unlimited lifetime and scope and no encapsulation, then this design does not make sense.

This description of the problems with the code is very different from and much more accurate than your previous one.

Just one more nitpick: There are no Global Variables in the original code. Declaring variables using Dim in a module will create a private variable.

I fully agree that the code incorporated many bad practices. Beyond that, it appeared to be pretty close to achieving what I thought the OP wanted to achieve. – After reading the later comments from the OP, my assumption about the intention was obviously incorrect.

Considering the now revealed intentions of the OP, your suggestion is indeed a much better and cleaner solution to the problem.

 

[sonic8]

The problem is, if you do that, you must first instantiate an object of that class, [...]

Yes, of course! That is not "the problem" but a basic fact of class modules and their use.

It is immaterial that a single instance of Outlook can handle any number of its own objects. The question was whether you needed to go as far as to layer another module over the external entry points of Outlook when a non-class module can handle the problem easily.

I had the assumption - now proven incorrect - that it was the very intent of the code discussed here to handle multiple MailItems created by Outlook. This would not be possible without a class module.