Excel Form and email

Private Sub CommandButton1_Click()

Sheets("Project").Visible = True

Worksheets("Project").Activate

lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

If TextBox1.Value = "" Then

MsgBox "Value is empty for Project Name"

TextBox1.SetFocus

Cancel = True

Exit Sub

End If

If TextBox2.Value = "" Then

MsgBox "Value is empty for Date"

TextBox2.SetFocus

Cancel = True

Exit Sub

End If

If Not IsDate(TextBox2.Value) Then

MsgBox "Only Date Format Allowed for Date"

TextBox2.Value = ""

TextBox2.SetFocus

Cancel = True

Exit Sub

End If

If TextBox4.Value = "" Then

MsgBox "Value is empty for What event promoted a change to the contract?"

TextBox4.SetFocus

Cancel = True

Exit Sub

End If

If TextBox5.Value = "" Then

MsgBox "Value is empty for When did the event occur?"

TextBox5.SetFocus

Cancel = True

Exit Sub

End If

If TextBox6.Value = "" Then

MsgBox "Value is empty for Brief description of the request"

TextBox6.SetFocus

Cancel = True

Exit Sub

End If

If TextBox7.Value = "" Then

MsgBox "Value is empty for Number of Contracts"

TextBox7.SetFocus

Cancel = True

Exit Sub

End If

If Not IsNumeric(TextBox7.Value) Then

MsgBox "Only Numbers Allowed Number of Contracts"

Cancel = True

Exit Sub

End If

If TextBox8.Value = "" Then

MsgBox "Value is empty for Target completion date"

TextBox8.SetFocus

Cancel = True

Exit Sub

End If

If Not IsDate(TextBox8.Value) Then

MsgBox "Only Date Format Allowed for Date"

TextBox8.Value = ""

TextBox8.SetFocus

Cancel = True

Exit Sub

End If

If TextBox9.Value = "" Then

MsgBox "Value is empty for Impact of updates not being processed by target date"

TextBox9.SetFocus

Cancel = True

Exit Sub

End If

If TextBox10.Value = "" Then

MsgBox "Value is empty for Rerquestor Name"

TextBox10.SetFocus

Cancel = True

Exit Sub

End If

If TextBox11.Value = "" Then

MsgBox "Value is empty for Rerquestor Department"

TextBox11.SetFocus

Cancel = True

Exit Sub

End If

If TextBox12.Value = "" Then

MsgBox "Value is empty for Comments"

TextBox12.SetFocus

Cancel = True

Exit Sub

End If

Project = ComboBox1.Value

If Project = "" Then

Exit Sub

End If

ActiveSheet.Cells(lastrow + 1, 1).Value = TextBox1

ActiveSheet.Cells(lastrow + 1, 2).Value = TextBox2

ActiveSheet.Cells(lastrow + 1, 3).Value = ComboBox1

ActiveSheet.Cells(lastrow + 1, 4).Value = TextBox4

ActiveSheet.Cells(lastrow + 1, 5).Value = TextBox5

ActiveSheet.Cells(lastrow + 1, 6).Value = TextBox6

ActiveSheet.Cells(lastrow + 1, 7).Value = TextBox7

ActiveSheet.Cells(lastrow + 1, 8).Value = TextBox8

ActiveSheet.Cells(lastrow + 1, 9).Value = TextBox9

ActiveSheet.Cells(lastrow + 1, 10).Value = TextBox10

ActiveSheet.Cells(lastrow + 1, 11).Value = TextBox11

ActiveSheet.Cells(lastrow + 1, 12).Value = TextBox12

Dim EBody As String

MyDate = Format(Date, "mm/dd/yyyy")

MyTime = Format(Time, "hh:nn:ss")

'Message using HTML

EBody = " Today is " & MyDate & " - " & MyTime & "<br />" & "<br />" & " A request to create a project is being submitted " & "<br />" & "<br />" _

& "<U>" & "<h4 style=color:red;>" & "FA PROJECT REQUEST" & "</h4>" & "</U>" _

& "&emsp;" & "Project: " & TextBox1 & "<br />" _

& "&emsp;" & "Date: " & TextBox2 & "<br />" _

& "&emsp;" & "Priority Level: " & ComboBox1 & "<br />" _

& "&emsp;" & "What event promoted a change to the contract?: " & TextBox4 & "<br />" _

& "&emsp;" & "When did the event occur?: " & TextBox5 & "<br />" _

& "&emsp;" & "Brief description of the request: " & TextBox6 & "<br />" _

& "&emsp;" & "Number of contracts involved: " & TextBox7 & "<br />" _

& "&emsp;" & "Target completion date: " & TextBox8 & "<br />" _

& "&emsp;" & "Impact of updates not being processed by target date: " & TextBox9 & "<br />" _

& "&emsp;" & "Requestor Name: " & TextBox10 & "<br />" _

& "&emsp;" & "Requestor Department: " & TextBox11 & "<br />" _

& "&emsp;" & "Comments: " & TextBox12 & "<br />" _

& "&emsp;" & "<br />" & "<br />" & "<br />" _

& " Thank You" & "<br />"

Set MyApp = CreateObject("Outlook.Application")

Set objMsg = MyApp.CreateItem(0)

With objMsg

.To = "FA@cit.com"

.CC = "xavier.arias@cit.com"

.Subject = "FA Project Request: " & TextBox1

.Categories = "PROJECTS"

'.BodyFormat = olFormatPlain ' send plain text message

.HTMLBody = EBody

.Display

Application.Wait (Now + TimeValue("0:00:02"))

Application.SendKeys "%s"

End With

MsgBox "The process was completed succesfully"

ActiveWorkbook.Save

Unload FAProjForm

Sheets("Project").Visible = False

ActiveWorkbook.Close

Application.Visible = True

End Sub

Private Sub CommandButton2_Click()

Unload FAProjForm

End Sub

Private Sub UserForm_Initialize()

With ComboBox1

.AddItem "1 Low"

.AddItem "2 Medium-Low"

.AddItem "3 Medium"

.AddItem "4 Medium-High"

.AddItem "5 High"

.ListIndex = 0

End With

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Application.Visible = True

ActiveWorkbook.Close SaveChanges:=True

End Sub