Change VBA code to run from button click to automaticly

[WillemS]

Good day

Can I please ask for help to show me how to change my code, see below, so that the code automatically runs once new information arrives in the excel sheet instead of me have to click the button created to run teh code please. Much appreciated.Tx

Private Sub CommandButton1_Click()

Dim outlook As Object
Dim mail As Object
Dim i As Integer

Dim tostring As String
Dim subjectstring As String
Dim bodystring As String

subjectstring = "6 Enter your subject line here"

bodystring = ""
bodystring = bodystring & "Good Day" & vbNewLine & vbNewLine
bodystring = bodystring & "Trust you are well." & vbNewLine & vbNewLine
bodystring = bodystring & "Thank you" & vbNewLine & vbNewLine
bodystring = bodystring & "Kind Regards" & vbNewLine & vbNewLine

For i = 2 To (Application.CountA(Columns("A")) - 1)
Set outlook = CreateObject("outlook.application")
Set mail = outlook.createitem(0)
tostring = Cells(i, 1).Value
With mail
.To = tostring
.Subject = subjectstring
.body = bodystring
'.attachments.Add "C:\Users\willem.smith\Desktop\Form.docx" ''Replace this address with your file location''
.send
End With
Next i

MsgBox ("Email(s) has been sent")

'Move row to another worksheet
Rows("1:100").Cut
Worksheets("Sheet2").Rows("1:100").Insert

End Sub

 

[JackDanIce]

Untested, try this as a worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    With Target
        If .CountLarge > 1 Or .Column <> 1 Or Len(.Value) = 0 Then Exit Sub
        Send_Email Target
    End With
    
End Sub
Sub Send_Email(Target As Range)
    
    Dim mail As Object

    With CreateObject("Outlook.Application")
        Set mail = .createitem(0)
        With mail
            .To = Target.Value
            .Subject = "6 Enter your subject line here"
            .body = bodystring
            '.attachments.Add "C:\Users\willem.smith\Desktop\Form.docx" ''Replace this address with your file location''
            .send
        End With
    End With
        
    With Target
        .EntireRow.Cut
        Sheets("Sheet2").Rows(.Row).Insert
    End With
    
    MsgBox "Email to: " & Target.Value & " sent", vbOKOnly, "Email sent"
    
    Set mail = Nothing
    
End Sub

 

[WillemS]

Hi there
Thank you for the prompt reply.

It returns the below error

 

[WillemS]