Microsoft Excel - Send Mail Using Button and Conditinal Formatting

Dear All,

In the below attached workbook following requirements to be worked.
I would request pls help me.

According to F column G value should come.

If F between the follow

60% to 70%   G Value Should 1
71% to 80%    G Value Should 2
81% to 90%    G Value Should 3
91% to 100%    G Value Should 4
101% to 120%    G Value Should 5

Then F Column should not accept more than 125%.

Also Once we Click on "Save & Submit" then a this attachment should
go to 3 specific people.

"Save & Close" should only save and Close the workbook.

Prasad

Button Mail.zip

Sub ColourBasedOnColumnD2()
'
    ActiveSheet.Unprotect    'place at the beginning of the code

    Dim myC As Range
    Dim myV As Variant
    Dim myR As Range

    Set myR = Range("A:C")

    With Intersect(myR, Range(ActiveSheet.PageSetup.PrintArea))
      .Offset(1, 0).Resize(.Rows.Count - 1).Interior.ColorIndex = xlNone
    End With

    For Each myC In Range(Range("D2"), Cells(Rows.Count, 4).End(xlUp))
      ' myV = Application.VLookup(IIf(IsEmpty(myC.Value), "", myC.Value), _
      Range("ColorKey"), 2, False)   ' code to handle just a named range
      myV = Application.VLookup(IIf(IsEmpty(myC.Value), "", myC.Value), _
                  Worksheets("ColorKey").Range("A:B"), 2, False)  ' code to handle a sheet
      On Error GoTo NoColor:
      If myC.Row Mod 2 = 0 Then Intersect(myC.EntireRow, myR).Interior.ColorIndex = myV

NextCell:
    Next myC

    'ActiveSheet.Protect     ' place at end of code
    ' Re-protects sheet in case anything unprotects it, yet allows vb functioning to remain.
    With ActiveSheet
      .EnableAutoFilter = True
      .Protect UserInterfaceOnly:=True
    End With

    Exit Sub

NoColor:
    Intersect(myC.EntireRow, myR).Interior.ColorIndex = 3
    Resume NextCell:
End Sub

Please help on this...

This should do it for you.  I changed your data validation from Whole numbers to decimal, as it didn't like the percentages.  The formula in column F reads:

=IF(AND(F7>=0.6,F7<=1.2),IF(F7>1,5,INT(F7*10)-5),"")

and will autofill up and down as necessary.  You did not specify the value for G if the target is between 120 and 125, so I left it blank.

I put two modules in, one for the command buttons and one for the e-mail.  You will need to adjust the email macro to suite your needs, but it is fairly self-evident.  It does assume you are using Outlook as your mail client, I don't have any other clients to work with.

ButtonMail.zip

Tweaks for basEMail will include:

strBody = yourbody

oMessage.Attachments.Add yourpath

Set objOutlookRecip = .Recipients.Add("youremailaddresses")

.CC = "yourCCaddresses"

.Subject = "yoursubject"

it is set up to display the e-mail, once you are comfortable with it, you can comment out the .Display and uncomment the .Save and .Send.

Hi Wally,

This is working but..in A5, I have emp name, that should come in subject line whenever am sending.
And you have specified a location of attachment but I need to send mail attachement of this work book only.

Prasad

You could adjust the text associated with "yoursubject" to include the employee name:

  .Subject = "Regarding " & worksheets("Per").Range("A5").value & ":"

To attach the current spreadsheet:

oMessage.Attachments.Add ThisWorkbook.FullName