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.
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.