VBA For Data Validation and Email Button

XLnoobVBA

New Member
Joined
Mar 3, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi;

I'm very new to VBA coding on Excel and have learnt everything I know from this forum today,

I am trying to create VBA for a spreadsheet, that if users place a value in column A, before the spreadsheet is emailed, they have to fill in all additional columns to AH. I have created a button that I would like users to be able to click, which then validates the data and if validated, creates an email with attachment. If there is missing data I would like the VBA to highlight the missing data.

So far, I have been able to get the validation and email to work in two separate buttons, but I would like to combine the code to work under a single click.

For Validation (no highlights):

Private Sub CommandButton1_Click()
Dim foundRng As Range
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set foundRng = Range("B4:AJ" & LastRow).Find("", LookIn:=xlValues, LookAt:=xlWhole)
If Not foundRng Is Nothing Then
MsgBox ("You must enter data in all cells")
foundRng.Select
Cancel = True
Exit Sub
End If
End Sub

For Email:

Private Sub CommandButton1_Click()
Dim xOutlookObj As Object
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Type the body or your email message here" & vbNewLine & vbNewLine & _
"Use this if you want a separate line of text" & vbNewLine & _
"Use this if you want another separate line of text"
On Error Resume Next
With xOutMail
.To = "xxxx@email.com"
.CC = ""
.BCC = ""
.Subject = "SAP Hybris Content Upload Request"
.Body = xMailBody
.Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

Any help would be appreciated.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi,
if your validation code is doing what you want then change it to a function that returns a boolean value (true / false)

VBA Code:
Function MustComplete() As Boolean
    Dim foundRng    As Range
    Dim LastRow     As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set foundRng = Range("B4:AJ" & LastRow).Find("", LookIn:=xlValues, LookAt:=xlWhole)
    If Not foundRng Is Nothing Then
        MsgBox "You must enter data in all cells", 48, "Entry Required"
        foundRng.Select
        MustComplete = True
    End If
End Function


Call it from your CommandButton1 code

Rich (BB code):
Private Sub CommandButton1_Click()
Dim xOutlookObj As Object
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String

If MustComplete Then Exit Sub

rest of code

Dave
 
Upvote 0
Hi,
if your validation code is doing what you want then change it to a function that returns a boolean value (true / false)

VBA Code:
Function MustComplete() As Boolean
    Dim foundRng    As Range
    Dim LastRow     As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set foundRng = Range("B4:AJ" & LastRow).Find("", LookIn:=xlValues, LookAt:=xlWhole)
    If Not foundRng Is Nothing Then
        MsgBox "You must enter data in all cells", 48, "Entry Required"
        foundRng.Select
        MustComplete = True
    End If
End Function


Call it from your CommandButton1 code

Rich (BB code):
Private Sub CommandButton1_Click()
Dim xOutlookObj As Object
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String

If MustComplete Then Exit Sub

rest of code

Dave
Thanks Dave, really appreciate you help here.

I'm a bit confused about how I combine these to produce a working code? When I do as you say I get an error. Clearly this is something I'm doing wrong But I cant figure out where. This is how I've written the code:

Private Sub CommandButton1_Click()
Dim xOutlookObj As Object
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String

If MustComplete Then Exit Sub

Function MustComplete() As Boolean
Dim foundRng As Range
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set foundRng = Range("B4:AJ" & LastRow).Find("", LookIn:=xlValues, LookAt:=xlWhole)
If Not foundRng Is Nothing Then
MsgBox "You must enter data in all cells", 48, "Entry Required"
foundRng.Select
MustComplete = True
End If
End Function
 
Upvote 0
Hi,
You don't combine the two codes - the Function is a separate code from your CommandButton1 code.

Place it on its OWN in a standard module.

VBA Code:
Function MustComplete() As Boolean
Dim foundRng As Range
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set foundRng = Range("B4:AJ" & LastRow).Find("", LookIn:=xlValues, LookAt:=xlWhole)
If Not foundRng Is Nothing Then
MsgBox "You must enter data in all cells", 48, "Entry Required"
foundRng.Select
MustComplete = True
End If
End Function

As ranges are unqualified, it is assumed that the sheet being emailed is the activesheet.

Dave
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top