Programming Excel with VBA and .NET

Add Methods

Methods are Sub or Function procedures within a class. You use methods to define actions within a class, such as calculating a result. For example, the following addition to the Message class sends the message via email:

    ' Send method: sends the message via email.
    Public Sub Send(ToAddress As String)
        Dim msgToSend As String, result As Double
        msgToSend = "mailto:" & ToAddress
        msgToSend = msgToSend & "?SUBJECT=" & Title
        msgToSend = msgToSend & "&BODY=" & Value
        ThisWorkbook.FollowHyperlink msgToSend, , True
    End Sub

To use this method from code, create an object and call Send with the email address of the recipient:

    ' TestMessage module
    Sub TestMessageSend( )
        Dim msg1 As New Message
        msg1.Title = "Message to Send"
        msg1.Value = "This message brought to you by Excel."
        msg1.Send ("[e-mail]")
    End Sub

If you run TestMessageSend, Excel creates a new mail message using your email client, as illustrated in Figure 5-3.

Figure 5-3. Sending mail from Excel