Andyjk1984
New Member
- Joined
- May 17, 2019
- Messages
- 11
morning guys
got myself a little stuck with this
so I have created a userform that once populated with the relevant info fills a log on sheet1, I then want It to email the data that was entered to myself.
where am I going wrong, I have bitten off more than I am capable of
got myself a little stuck with this
so I have created a userform that once populated with the relevant info fills a log on sheet1, I then want It to email the data that was entered to myself.
where am I going wrong, I have bitten off more than I am capable of
Code:
Private Sub BUTTON1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("LOG")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for date
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Please enter Date"
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(iRow, 1).Value = Me.TextBox1.Value
.Cells(iRow, 2).Value = Me.TextBox2.Value
.Cells(iRow, 3).Value = Me.TextBox14.Value
.Cells(iRow, 4).Value = Me.TextBox3.Value
.Cells(iRow, 5).Value = Me.TextBox4.Value
.Cells(iRow, 6).Value = Me.TextBox5.Value
.Cells(iRow, 7).Value = Me.TextBox6.Value
.Cells(iRow, 8).Value = Me.TextBox7.Value
.Cells(iRow, 9).Value = Me.TextBox8.Value
.Cells(iRow, 10).Value = Me.TextBox9.Value
.Cells(iRow, 11).Value = Me.TextBox10.Value
.Cells(iRow, 12).Value = Me.TextBox11.Value
.Cells(iRow, 13).Value = Me.TextBox12.Value
.Cells(iRow, 14).Value = Me.TextBox13.Value
' .Protect Password:="password"
End With
'clear the data
Me.TextBox4.Value = ""
Me.TextBox3.Value = ""
Me.TextBox6.Value = ""
Me.TextBox5.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox1.SetFocus
'Send Email automatically
Dim oOLook As Object
Dim oEMail As Object
Dim sMessage As String
Set oOLook = CreateObject("Outlook.Application")
oOLook.Session.Logon
Set oEMail = oOLook.CreateItem(0)
oEMail.display
'Compile the message body:
sMessage = "This is an automated email:" & vbCrLf
sMessage = sMessage & "Route Re-String:" & vbCrLf
sMessage = sMessage & TextBox3 & vbCrLf
sMessage = sMessage & "" & TextBox4 & vbCrLf
sMessage = sMessage & "" & TextBox5 & vbCrLf
On Error Resume Next
With oEMail
.To = "[EMAIL="user@email.com"]user@email.com[/EMAIL]"
.CC = ""
.BCC = ""
.Subject = " Changes"
.Body = sMessage
.send
End With
Set outmail = Nothing
Set outapp = Nothing
End Sub