Separating Command Buttons and VBA code in Excel

Question

I have an Excel worksheet with over 100 rows of data. When the user completes filling in the information in their row, there is a command button at the end of the row that automatically sends an email to a specific email account with information from the row.

I've inserted several command buttons with the VB code to send the email and it works great!. The problem I'm having is that I can't separate the command buttons, i.e., each button's code should be specific to the row it is on; when I change the code with the cell location that contains the information for that command button, it changes all of the command buttons to that information.

I know the answer must be really simple, but I've drawn a complete blank.I appreciate any help!

Here is the code I have:

Option Explicit

Private Sub CommandButton2_Click()

On Error GoTo ErrHandler

    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")

    ' CREATE EMAIL OBJECT.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)

    With objEmail
        .to = "[email address].com"
        .Subject = Range("A3")
        .Body = "[Message]"
        .Send        ' SEND THE MESSAGE.
    End With

    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing

ErrHandler:
    '
End Sub

Each row should have this same command with only the CommandButton number changing and the .Subject = Range entry changing.

I'm doing something wrong though because that doesn't work.

Answer 1

I said in a comment, it's probably easier to simply color cells to look like buttons and have the users click on a cell to send the emails - then you can simply use the offset for the particular row, but if you insist on using command buttons, it's quite simple.

Take your current code and put it in a new subroutine that accepts a range parameter.

Then, add your buttons, and link each one to its own code with a different range.

Option Explicit

Private Sub CommandButton3_Click()
SendEmail Range("A3")
End Sub

Private Sub CommandButton4_Click()
SendEmail Range("A4")
End Sub    

Private Sub CommandButton5_Click()
SendEmail Range("A5")
End Sub

`...

Sub SendEmail(TheRange as Range)
    On Error GoTo ErrHandler
    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    ' CREATE EMAIL OBJECT.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)
    With objEmail
        .to = "[email address].com"
        .Subject = TheRange 'Change this line
        .Body = "[Message]"
        .Send        ' SEND THE MESSAGE.
    End With
    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing
ErrHandler:
End Sub

If you prefer instead to use the SelectionChanged event, you can do it like this.

Then, you can just update [C4:C8] if you want to add any more "buttons"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, [C4:C8]) Is Nothing Then
    SendEmail Range("A" & Target.Row)
    'Optionally select the subject we sent so we can re-click
    'You can choose any other cell not in our event range
    Range("A" & Target.Row).Select
End If
End Sub