Help to VBA email code

[KasperSSI]

Hi,

I have made a VBA code that sends out an email with an attachment from Outlook.
I want the code to be applied to a button somewhere at the end of each row. The creating of the email and attachments are working perfectly.
However, my cell references are "fixed" rather than dynamic pr. row. How do I change this - I would rather not copy the code and make x amount of "fixed ranges" .

I hope my question is clear and help is much appreciated.

br,

 

[NoSparks]

I would insert and name Form buttons, for example in column M, using a macro like this

Private Sub Insert_Form_Command_Buttons()
    Dim myCell As Range, myRng As Range
    Dim CMD As Button
    
With ActiveSheet
    Set myRng = .Range("M2:M20")    '<~~~~~ enter the range to have buttons
End With

Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        With myCell
            Set CMD = .Parent.Buttons.Add _
                        (Top:=.Top, Left:=.Left, _
                         Width:=.Width, Height:=.Height)
            CMD.Name = "CMD_" & .Address(0, 0)
            CMD.Caption = "Send E-Mail"
            CMD.OnAction = "RunForAllButtons"    '<~~~~~ sub to call
        End With
    Next myCell
Application.ScreenUpdating = True

End Sub

then each button will call the same RunForAllButtons proceedure when clicked.

Sub RunForAllButtons()
    Dim x As Variant, rw As Long, ID As String
    With ActiveSheet.Buttons(Application.Caller)
        'get the row
        x = Split(.TopLeftCell.Address, "$")
        rw = x(UBound(x))
    End With
    
    'your code would be something along the lines of
    ID = "Invoice_" & Range("G" & rw) & "_" & Range("F" & rw)
    MsgBox ID   'just to show resulting ID
    'etc
    
End Sub

Hope that helps

 

[KasperSSI]

I would insert and name Form buttons, for example in column M, using a macro like this

Private Sub Insert_Form_Command_Buttons()
    Dim myCell As Range, myRng As Range
    Dim CMD As Button
   
With ActiveSheet
    Set myRng = .Range("M2:M20")    '<~~~~~ enter the range to have buttons
End With

Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        With myCell
            Set CMD = .Parent.Buttons.Add _
                        (Top:=.Top, Left:=.Left, _
                         Width:=.Width, Height:=.Height)
            CMD.Name = "CMD_" & .Address(0, 0)
            CMD.Caption = "Send E-Mail"
            CMD.OnAction = "RunForAllButtons"    '<~~~~~ sub to call
        End With
    Next myCell
Application.ScreenUpdating = True

End Sub

then each button will call the same RunForAllButtons proceedure when clicked.

Sub RunForAllButtons()
    Dim x As Variant, rw As Long, ID As String
    With ActiveSheet.Buttons(Application.Caller)
        'get the row
        x = Split(.TopLeftCell.Address, "$")
        rw = x(UBound(x))
    End With
   
    'your code would be something along the lines of
    ID = "Invoice_" & Range("G" & rw) & "_" & Range("F" & rw)
    MsgBox ID   'just to show resulting ID
    'etc
   
End Sub

Hope that helps

Thanks for the inspiration. Got it to work with ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row and some more, with my experience, simple code.