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
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