How to automatically send email in macro, excel

[SAFranklin]

Hey all, frustrations have long since set in so I'm looking for a little help due to my limitations. The code below is set to automatically provide an e-mail once zTardy reaches a value that exceeds zTardyLimit. This is one of the steps that I need to get accomplished however I am really struggling with the other two steps to this process so it will be fool proof and I am hoping to get a little help.

I also need the code to:

1) Send an email each time the value increases in zTardy. (Not only when the value initially exceeds the zTardyLimit) However, it cannot send another e-mail when the value of zTardy decreases, even if it does not decrease below the current zTardyLimit value.

2) Place the date the e-mail was sent in the same row, adjacent column.



Sub sendTardies()


Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


zCount = [tardyCount]


If zCount = 0 Then
Exit Sub
End If


zTardyLimit = [tardyLimit]


zLastRow = Cells(Rows.Count, "B").End(xlUp).Row


temp = "AE7:AE" & zLastRow


For Each cell In Range(temp)
zTardy = cell.Value
If zTardy > zTardyLimit Then
zRow = cell.Row
zName = Cells(zRow, "B")


strbody = ""
strbody = strbody & "HR Manager,"
strbody = strbody & vbCr & vbCr
strbody = strbody & zName & " currently has " & zTardy & " tardies in the past year "
strbody = strbody & "and needs to have his/her attendance reviewed."
strbody = strbody & vbCr & vbCr
strbody = strbody & "Thanks"


On Error Resume Next
With OutMail
.to = "xxxxxx@xxxxx.com"
.CC = ""
.BCC = ""
.Subject = zName & " Attendance"
.Body = strbody
.Send
End With
On Error GoTo 0


zSent = zSent + 1
saywhat = "processing " & zSent & " of " & zCount
Application.StatusBar = saywhwat
End If


Next


Application.StatusBar = ""


Set OutMail = Nothing
Set OutApp = Nothing


End Sub

 

[RickXL]

Hi.

I think we need a bit more information. Imagine the scenario where:
zTardy exceeds the zTardyLimit so an email is sent.
zTardy increases so another email is sent.
zTardy drops to a level still above the zTardyLimit and remains there for ever.

That will mean that the zTardy Limit will have been broken for every following year but emails will only have been sent in the first year. If that is no problem then we have enough information to propose something. However, if that situation should not be allowed then we will need to know how that situation should be handled.

OK, it may not be a major problem but it does affect what data you need to save to work out when emails should be sent.

 

[SAFranklin]

Hi Rick,

If I'm understanding you correctly, I do not think it will be a problem because the zTardy will not remain the same as time passes. The way it is set up on my spreadsheet is that it is simply a formulated count of the tardies for any employee pulled from a different worksheet. For this reason, the count will have to naturally drop (over time) if no more tardies occur and will increase when a tardy occurs. I believe the key is that it is figured on a rolling year based on the current date and the data continues to be entered through the life of the employee. It is not strictly derived from the current calendar year.

I hope that makes some sense and I hope it addressed your issue with the macro. Thanks for your help.
SA

 

[RickXL]

Hi.

I have not really made many real changes to your code but I have added an "Option Explicit" and have Dim'd all the variables. (That revealed that variable saywhat had been spelt incorrectly in one place.)

I have also spewcifically identified the worksheet so that it can be referred to in the code. This helps prevent problems if someone clicks on another worksheet tab or opens another workboiok while the macro is running.

The part I can't help with is how you calculate your running total of zTardy. I have just added a line of code setting it to 999. You will need to change that to something that calculates the rolling year total of zTardy. The "if" statement that follows uses that value to see if a new email is required or not.

Option Explicit
Sub sendTardies()

    Dim OutApp As Object
    Dim OutMail As Object
   
    Dim temp As String
    Dim zCount As Long
    Dim zTardyLimit As Long
    Dim zTardy As Long
    Dim ws As Worksheet
    Dim zLastRow As Long
    Dim zRow As Long
    Dim Cell As Range
    Dim zName As String
    Dim strbody As String
    Dim zSent As Long
    Dim saywhat As String
    Dim zPrev As Long
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set ws = Worksheets("Sheet1")
    
    zCount = [tardyCount]
    
    If zCount = 0 Then Exit Sub
    
    zTardyLimit = [tardyLimit]
    
    zLastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    For Each Cell In ws.Range("AE7:AE" & zLastRow)
        zTardy = Cell.Value
        zRow = Cell.Row
        zPrev = 999 ' Replace 999 with zTardy total for rolling year
        
        If zTardy > zTardyLimit And zTardy > zPrev Then

            zName = Cells(zRow, "B")
            
            strbody = ""
            strbody = strbody & "HR Manager,"
            strbody = strbody & vbCr & vbCr
            strbody = strbody & zName & " currently has " & zTardy & " tardies in the past year "
            strbody = strbody & "and needs to have his/her attendance reviewed."
            strbody = strbody & vbCr & vbCr
            strbody = strbody & "Thanks"
            
            'On Error Resume Next
            With OutMail
                .to = "xxxxxx@xxxxx.com"
                .CC = ""
                .BCC = ""
                .Subject = zName & " Attendance"
                .Body = strbody
                '.display
                .Send
            End With
            'On Error GoTo 0
            
            zSent = zSent + 1
            saywhat = "processing " & zSent & " of " & zCount
            Application.StatusBar = saywhat
        End If
    
    Next
    
    Application.StatusBar = ""

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

Finally, I have commented out the "On Error Resume Next" statement because you really want to know if an error is generated so you can correct it. If you do not do this there could be an error and an email that should be sent will not be and you will never find out.