need assistance in send mail macro

[saurabhlotankar]

Hi guys,

Please find attached sheet. If we click on validate button it highlights all cells in mandatory columns which are blank and pops up a message on the screen stationg "Please fill up mandatory fields.

My question is if there are no blanks in the mandatory columns. then macro should not pop up that message it should draft an email.

i have designed the code but its either highlighting blanks cells or drafting email. am not bale to combine both the things.



kindly assist.

[NoSparks]

You should really use Option Explicit and declare all your variables, not just some of them.
Have a look at this page.

Give this macro a try

Public Sub HighLightEmptyCells()
    Dim myRange As Range
    Dim myCell As Range
    Dim lastrow As Long
    Dim anyBlanks As Boolean
    
With ActiveSheet
    lastrow = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious).Row
    Set myRange = Application.Union(.Range("B3:D" & lastrow), .Range("F3:F" & lastrow))
    anyBlanks = False
    
    For Each myCell In myRange
        If myCell.Value = "" Then
            myCell.Interior.Color = vbRed
            anyBlanks = True
        Else
            myCell.Interior.Color = xlNone
        End If
    Next myCell
    
    If anyBlanks = True Then
        MsgBox " Please fill up Mandatory Fields Highlighted in Red"
        Exit Sub
    Else
        MsgBox "OK -- Ready to draft email"
        ' Application.Dialogs(xlDialogSendMail).Show "xyz@abc.com"
    End If
End With
End Sub

[saurabhlotankar]

Thanks NoSparks. I am new to this coding hence i worte that leanthy code. But your is awesome.
Its working perfect. I just neeed to add one more condition to it. If there are any duplicate value in column D(i.e.header 3), then again macro should highlight those cells( with duplicate values) and pop up a msg stating there are duplicate values in column D.
So ideally this macro should do 2 checks
first : Highlight blank cells if any in mandatory field and pop msg stating the same. (which we have already done)
second: highlight Duplicate value cells in column D and pop up msg stating the same.

and if no blank cells and no duplicate values then send mail.

If you can assist with that then it would be awesome.

Thanks.

[saurabhlotankar]

Hi NoSparks,

I have written the below mentioned code for highlighting duplicate values if any in column D.

Public Sub MarkDuplicates()
Dim iWarnColor As Integer
Dim rng As Range
Dim rngCell As Variant
Dim myRangeUpdateAA As Range


Set rLastCell = ActiveSheet.Cells.Find("*", ActiveSheet.Cells(1, 1), , , xlByRows, _
xlPrevious)
lastrow = rLastCell.Row
Set rng = ActiveSheet.Range("D3" & lastrow)
iWarnColor = xlThemeColorAccent2
For Each rngCell In rng.Cells
vVal = rngCell.Text
If (WorksheetFunction.CountIf(rng, vVal) = 1) Then
rngCell.Interior.Pattern = xlNone
Else
rngCell.Interior.ColorIndex = iWarnColor
End If
Next rngCell
End Sub

Now am stuck on how to incorporate this code with our earlier code. So that our final macro will be ready with both condiotions i.e. no mandatory fields should be blank and there should not be any duplcate value in column D. In both pop msg should populate and mail should not be drafted.

Request you to kindly assist.

Thanks a lot.

[saurabhlotankar]

Any luck anyone?