Hi,
The workbook was blank because the file was never saved. I thought you were going to do that manually. You had this code in the file (which I did not touch) that prevented the file from being saved unless VBA was disabled (Application.EnableEvents = False).
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'If the six specified cells do not contain data, then display a message box with 'and cancel the attempt to save.
'"If worksheetfunction.CountA(Worksheets("Sheet1").Range(C40,D40,E40,F40,G40,H40") ) < 6 Then MsbBox "Worrkbook will not be saved unless" & vbCrlf & _"
'"All required fields have been filled in"
Cancel = True
End Sub
'Exit If
I updated the file, to change the code so the file can be saved when all the fields are filled in. Changes in red:
In the ThisWorkbook Code Module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Verify that required Inputs are NOT BLANK
'Display an Error Message and Exit if they are BLANK
sErrorMessage = VerifyInputs()
If Len(sErrorMessage) > 0 Then
Cancel = True
MsgBox sErrorMessage
Exit Sub
End If
End Sub
In the Sheet PO Request Form Code Module:
Option Explicit
Private Sub CommandButton1_Click()
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim sErrorMessage As String
'Verify that required Inputs are NOT BLANK
'Display an Error Message and Exit if they are BLANK
sErrorMessage = VerifyInputs()
If Len(sErrorMessage) > 0 Then
MsgBox sErrorMessage
Exit Sub
End If
'Save this file
ActiveWorkbook.Save
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
.To = "giggles2005-2006@hotmail.co.uk"
.Subject = "PO Request"
.Body = " Hi Team, please can you raise the attached?"
.attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub
The Ordinary Code module that verified inputs had no change.
Lewis
Bookmarks