|
Hello all,
so I do have a command button in my excel which first checks a few fields (they're not supposed to be empty and if there are an automated message box is created) and secondly sends the file attached to an email
I now would like to extend the function of this command button- so after the fields are checked BUT BEFORE it get's sent per email I would like the file to be saved automatically with a new file name.
Below you can see the current code. Any ideas?
VBA Code:
Private Sub CommandButton1_Click()
Dim xOutlookObj As Object
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
If IsEmpty(Range("A7")) Then
MsgBox "Enter date"
GoTo ends
Else
If IsEmpty(Range("D7")) Then
MsgBox "Enter Vizrt sales peson"
GoTo ends
Else
If IsEmpty(Range("C9")) Then
MsgBox "Enter the start date of your rental"
GoTo ends
Else
If IsEmpty(Range("C11")) Then
MsgBox "Enter the end date of your rental"
GoTo ends
End If
End If
End If
End If
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Please add any special requirements here. For FOC rentals - please attach Vanessa's approval to your email." & vbNewLine & vbNewLine & _
"" & vbNewLine & _
""
On Error Resume Next
With xOutMail
.To = "logisticaustria@vizrt.com"
.CC = ""
.BCC = ""
.Subject = "Demopool Request_"
.Body = xMailBody
.Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
ends:
End Sub
________________________
The form will get filled out by different useres and i don't trust them to save the file manually before submitting so I would like to autmate that. I think it would be great to save the file as PDF (specific print area).
Thanks for all your help and input!
Nadine
|