EXCEL VBA - Define Sub or Function

Question

How can I define a sub or function in VBA?

This is my code:

Private Sub CommandButton1_Click()
    Call Send_Mail
End Sub

In Worksheet "Sheet1" I have a CommandButton called Send_Mail and in "Sheet2" I have also a CommandButton. When I click the CommandButton in Sheet2 I want that the Button in Sheet1 will run.

With my code the : error "Sub or Function is not defined" appears.

EDIT:

Code for Send_Mail:

Public Sub Send_Mail_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim nameList As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error GoTo cleanup

    For i = 4 To 22                                                                         
        If Range("B4").Value <> "" Then                                                        
            nameList = nameList & ";" & Range("C" & i).Value
        End If
    Next
        With OutMail
            .To = nameList
            .Subject = "Subject Line"
            .Body = "Body Text"
            .Send
        End With
cleanup:
    Set OutApp = Nothing
    MsgBox "E-Mail sent."
    MsgBox Err.Description
End Sub

You need to make sure Sub Send_Mail is located in a regular code module, and not a worksheet module, that way it is accessible — Shai Rado
– Shai Rado, Commented Apr 25, 2018 at 6:04
Can you please show more of your codes? And what do you mean with this one: When I click the CommandButton in Sheet2 I want that the Button in Sheet1 will run, you mean the function assigned to button 1? — user6632933
– user6632933, Commented Apr 25, 2018 at 6:09
I went to "Create Macro" paste the code from Send_Mail to it. Saved it as Send_Mail .. but still the same error — diem_L
– diem_L, Commented Apr 25, 2018 at 6:16

user4039065 · Accepted Answer · 2018-04-25 06:11:44Z

1

I don't know what the underlying sub procedure attached to the Sheet1 button has been named but it is likely it has a similar name.

'Sheet2's button sub procedure
Private Sub CommandButton1_Click()
    Call Sheet1.CommandButton1_Click
End Sub

answered Apr 25, 2018 at 6:11

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

user6632933 Over a year ago

Or Sheet1.Send_Mail?

Collectives™ on Stack Overflow

EXCEL VBA - Define Sub or Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related