VBA Code to require fields, temporarily save file email it then delete the temp file

[red98]

I have a spreadsheet where I want to require certain fields to be completed then I want to have that file auto emailed. I have learned that I do need to have the file saved before sending otherwise the data will not appear in the email, so with this I want to have the file temporarily saved emailed then the temp file deleted.

Here is the code I have so far but it errors on the blue text, I did change the TempFileName from = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") to = [C16] & "_" & [B6] & "_" & [D6]

Any help is much appreciated!

Private Sub CommandButton1_Click()
If Range("B6").Value = "" Or _
Range("d6").Value = "" Or _
Range("f6").Value = "" Or _
Range("E9").Value = "" Or _
Range("H9").Value = "" Or _
Range("C11").Value = "" Or _
Range("C12").Value = "" Or _
Range("C14").Value = "" Or _
Range("F14").Value = "" Or _
Range("c15").Value = "" Or _
Range("f15").Value = "" Or _
Range("c16").Value = "" Or _
Range("f16").Value = "" Or _
Range("c17").Value = "" Or _
Range("f17").Value = "" Then
MsgBox ("Please confirm all required fields have been completed!")
Exit Sub
Else


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


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


' Make a copy of the file.
' If you want to change the file name then change only TempFileName variable.
TempFilePath = Environ$("temp") & "\"
TempFileName = [C16] & "_" & [B6] & "_" & [D6]
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
' Change the mail address and subject in the macro before you run this procedure.
With OutMail
.To = "test@test.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hello World!"
.Attachments.Add wb2.FullName
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
' display the mail.
.Send
MsgBox ("Your request has been sent. Thank you!")

End With
On Error GoTo 0


wb2.Close SaveChanges:=False


' Delete the file.
Kill TempFilePath & TempFileName & FileExtStr


Set OutMail = Nothing
Set OutApp = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End If


End Sub

 

[red98]

Answered my own question:
The fields being used [B6] & [D6] are time and date fields so I changed the file name to be as follows:
= [C16] & "_" & Format([B6], "mm-dd-yy") & "_" & Format([D6], "[$-409]h-mm AM/PM;@")