Dont email form if certain cells are blank

[rfrederick62]

Good day all, I currently use a macro to send an "Order Form' (an excel sheet) to my Stores. The end-user fills out the form for items then hits a button on the sheet and the sheet is sent to Outlook and waits for the user to hit SEND (they have to c.c. certain people) and then the form goes to Stores email for printing and filling.
The problem is that many people 'forget' to fill out certain information- location , requester and quantity- so I have to return the form and/ or call them for the info - wasting valuable time.
What Id like is if they DO NOT fill in those cells they get a message back ("FORM INCOMPLETE- REDO") and the email process stops and they have to fill these cells in.....and this continues until all pertinent cells have the required information THEN it sends the form to Outlook....

thoughts?
thanks, Rob

 

[Fishboy]

Good day all, I currently use a macro to send an "Order Form' (an excel sheet) to my Stores. The end-user fills out the form for items then hits a button on the sheet and the sheet is sent to Outlook and waits for the user to hit SEND (they have to c.c. certain people) and then the form goes to Stores email for printing and filling.
The problem is that many people 'forget' to fill out certain information- location , requester and quantity- so I have to return the form and/ or call them for the info - wasting valuable time.
What Id like is if they DO NOT fill in those cells they get a message back ("FORM INCOMPLETE- REDO") and the email process stops and they have to fill these cells in.....and this continues until all pertinent cells have the required information THEN it sends the form to Outlook....

thoughts?
thanks, Rob

Hi Rob, welcome to the boards.

Your best bet to get help with this is to show us your existing macro (wrapped in code tags) so we can see what is already happening and see if we can introduce the conditions you would like added.

What would also help would be seeing an example of what your data looks like so we know how the columns are laid out etc. This can either be sharing a screenshot of your layout or by using something like the MrExcel HTML Maker or Excel Jeanie to share parts of your workbook right here on the forums.

 

[rfrederick62]

HERES THE MACRO, i cant get a screen shot of the form to stick in here..

Sub Mail_workbook_Outlook_1() Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object


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


Set Sourcewb = ActiveWorkbook



ActiveSheet.Copy
Set Destwb = ActiveWorkbook


' Determine the Excel version, and file extension and format.
With Destwb
If Val(Application.Version) < 12 Then
' For Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
' For Excel 2007-2010, exit the subroutine if you answer
' NO in the security dialog that is displayed when you copy
' a sheet from an .xlsm file with macros disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "You answered NO in the security dialog."
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With


' You can use the following statements to change all cells in the
' worksheet to values.
' With Destwb.Sheets(1).UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False


' Save the new workbook, mail, and then delete it.
TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")


Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)


With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
' Change the mail address and subject in the macro before
' running the procedure.
With OutMail
.to = "Stores@krrc.ns.ca"
.CC = ""
.BCC = ""
.Subject = "Personal Supply Order " & Format$(Now, "Medium Date")
.Body = "Please Ensure ALL the appropriate fields are filled out- Unit, Requester, QUANTITY and c.c. your Program Leader"
.Attachments.Add Destwb.FullName
' You can add other files by uncommenting the following statement.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
' display the mail or .Send to automatically send the message.
.Display
End With
On Error GoTo 0
.Close savechanges:=False
End With


' Delete the file after sending.
Kill TempFilePath & TempFileName & FileExtStr


ThisWorkbook.Close savechanges:=False


Set OutMail = Nothing
Set OutApp = Nothing


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

 

[Fishboy]

(wrapped in code tags)

My eyes!

Anyway, thanks for posting the code at least. I understand you cannot provide a screenshot of your data, that's fair enough, however are you able to let us know what columns being blank should flag as an error and stop the macro running?

 

[rfrederick62]

Hi there
MUST have information in:
cells G1, G3
and
somewhere in G7 to G53 ( the quantity column)
thanks so much, Rob

 

[Fishboy]

Hi there
MUST have information in:
cells G1, G3
and
somewhere in G7 to G53 ( the quantity column)
thanks so much, Rob

Just to clarify, does this mean if there is a blank anywhere in G7:G53 don't send, or so long as there is at least one value in G7:G53 it can be sent?

 

[Fishboy]

Ok, so I have 2 codes. One for each of the possibilities mentioned in my previous post.

If you want it so that "If G1 is blank, if G3 is blank, or if ALL of G7:G53 is blank":

Sub TEST()
If ThisWorkbook.Sheets(1).Range("G1").Value = "" Then
    MsgBox "FORM INCOMPLETE - PLEASE CHECK CELL G1"
    Exit Sub
End If
If ThisWorkbook.Sheets(1).Range("G3").Value = "" Then
    MsgBox "FORM INCOMPLETE - PLEASE CHECK CELL G3"
    Exit Sub
End If
If Application.WorksheetFunction.CountA(Range("G7:G53")) < 1 Then
    MsgBox "FORM INCOMPLETE - PLEASE CHECK CELLS G7:G53"
    Exit Sub
End If
' The rest of your code goes here
End Sub

If you want it so that "If G1 is blank, if G3 is blank, or if ANY CELL in G7:G53 is blank":

Sub TEST()
If ThisWorkbook.Sheets(1).Range("G1").Value = "" Then
    MsgBox "FORM INCOMPLETE - PLEASE CHECK CELL G1"
    Exit Sub
End If
If ThisWorkbook.Sheets(1).Range("G3").Value = "" Then
    MsgBox "FORM INCOMPLETE - PLEASE CHECK CELL G3"
    Exit Sub
End If
If Application.WorksheetFunction.CountA(Range("G7:G53")) < 47 Then
    MsgBox "FORM INCOMPLETE - PLEASE CHECK CELLS G7:G53"
    Exit Sub
End If
' The rest of your code goes here
End Sub

 

[rfrederick62]

yes ONE value MUST be in that range...

thanks

 

[Fishboy]

Right, then try out the top of those 2 codes.

It basically checks G1 for a value and if one is not found it gives an error and stops the macro from continuing, but if a value is found the macro continues.
It then checks G3 for a value and if one is not found it gives an error and stops the macro from continuing, but if a value is found the macro continues.
Finally it checks G7:G53 for values and if no values are found at all it gives an error and stops the macro from continuing, but so long as at least one cell has a value the macro continues.

You can simply add the rest of your code below mine.

 

[rfrederick62]

awesome!
so this is inserted back into my macro AFTER it opens Outlook ?