Hi All,
I am trying to complete this code for a form at work for vacation. It works if the employee presses yes to the required fields questions, but I am having trouble if the employee presses no, i get an error. Can someone please let me know what i am doing wrong? What i need is if they press yes, it picks the type of request and fills in the applicable dates associated with the request type. If they press no, it will return them to form to review it.
Here is the code:
Sub VacationForm()
Dim Response As Integer
Response = MsgBox(prompt:="Have all required fields been filled in?", Buttons:=vbYesNo)
If Response = vbYes Then
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
Set wb1 = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
Exit Sub
End If
If Response = vbNo Then
End If
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim FName As String
FName = Range("D6").Text
Dim LName As String
LName = Range("K6").Text
Dim Rtype As String
Rtype = Range("K12").Text
Dim edate As String
edate = Range("D12").Text
Dim Flexstart As String
Flexstart = Range("e23").Text
Dim Flexend As String
Flexend = Range("k23").Text
Dim Vacstart As String
Vacstart = Range("e37").Text
Dim Vacend As String
Vacend = Range("k37").Text
Dim Email As String
Email = "[email protected]"
TempFilePath = Environ$("temp") & "\"
TempFileName = FName & " " & LName & " " & Rtype & " " & edate
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
Send = Range("k12").Value
Result = Flexstart & " to " & Flexend
If Send = "Vacation Request" Then Result = Vacstart & " to " & Vacend
ElseIf Send = "Flex Day Request" Then Result = Flexstart & "to" & Flexend
ElseIf Send = "Flex_Vacation Request" Then Result = Flexstart & "to" & Vacend
Else
End If
With OutMail
.To = Email
.Subject = FName & " " & LName & " " & Send & " " & Result
.Body = "Hello," & vbNewLine & vbNewLine & "Please find the attached" & " " & Rtype & " form for" & " " & FName & " " & LName & " " & vbNewLine & "If you have questions or concerns, please do not hesitate to contact me." & vbNewLine & FName
.Attachments.Add wb1.FullName
.Send
End With
On Error GoTo 0
wb2.Close SaveChanges:=False
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox " Thank you for submitting your form"
End Sub
Bookmarks