Tech Support Guy banner
Status
Not open for further replies.

Auto email using excel

2K views 19 replies 2 participants last post by  scotty718 
#1 ·
hello,
i am trying to sent out an automatic email anytime the workbook has been changed and for example 3 days have passed from a certain date on the worksheet. this would have to be applied to close to a hundred rows per sheet and would be sent to outlook 2007. not express or anything. wondering if it would require multiple VBA codes or not.
i have looked on the Ron site and many postings but i just found out what a macro was and would love to learn how to write VBA but dont have the time. looking to try and solve this. also need to know if the computer im working on has to have outlook or if i can do it from one that doesnt.
Have an example and any details for further assistance just ask. any help would be greatly appreciated thank you.
 
#2 ·
Yes - this would require at best a small amount of VBA code.
Yes - it would require that the computer you are working on to have Outlook - or at least I wouldn't know how to do it any other way.

Question - when you say "the workbook has been changed" - do you mean any change to any cell in any sheet at any time (figuring the > 3 days elapsed requirement), or just the workbook has been saved and closed or something?
 
#4 ·
So, for example, if i have make a change to any given cell on worksheet A, you want to e-mail the worksheet to somebody. If I then go change another given cell, e-mail the sheet again?

If that is it, post your workbook and tell me the sheets it needs to apply to and any restrictions on which cells it should or should not apply to.
 
#7 ·
it would be this workbook. yes if any cell changed. and a notification if any date in column $J exceeds 3 days from the date entered or column $L exceeds 20 days from date entered if column $I says TX or 45 days if column $I says other. this is a dummy sheet but with all rules i have applied.
 
#14 ·
Actually, I am having some trouble with clarity

First, you say "if any cell changes" - but I don't think you mean any cells. Please be more specific. In this case, if I mistype 1999 when I mean 1998 in column E and change it, it will trigger the code....

Second, you say "from the date entered". Which cell is the date entered? It's not clear.

To restate your rules. You want an e-mail sent if ANY of the following conditions apply:
- any date in column $J is more than 3 days after date entered
- any date in column $L is more than 20 days after date entered and column $I says TX
- any date in column $L is more than 45 days after date entered and column $I says Other
 
#15 ·
yes on all the rules. thats what i am looking for. as far as any cells i do. if someone goes in and changes any value for any reason to send the email. this on the other hand is not so important. more of a bonus. i am more worried of the date config. essentially for the dates yes those rules to apply. but what i want is it to send email and to say "notification needs to be sent" 3 days after it was inspected. thats the date in $J. also the date notification was sent is manually input into $L and if the vehicle is in tx after 20 days a message needs to be sent that the vehicle is "Cleared for destruction/auction" same for other but after 45 days from date input in $L
 
#18 ·
Ok. Place the code below into the worksheet module for each sheet you want to apply it to.

There are some assumptions here (1 being you only want to send 1 e-mail per worksheet change event) and you need to tweak your recipients and subject, but this should get you off to a great start.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wks As Worksheet
Set wks = ActiveSheet

Dim cel As Range, rngWorking As Range

With wks
    Set rngWorking = Intersect(.UsedRange, .Columns("J:J"))
    For Each cel In rngWorking
        If cel < Date - 2 Then
            SendMail "me@my.com;you@you.com", "this is my subject"
            Exit Sub
        End If
    Next
    
    Set rngWorking = Intersect(.UsedRange, .Columns("L:L"))
    For Each cel In rngWorking
        If cel.Offset(, -3) = "TX" Then
            If cel < Date - 19 Then
                SendMail "me@my.com;you@you.com", "this is my subject"
                Exit Sub
            End If
        ElseIf cel.Offset(, -3) = "Other" Then
            If cel < Date - 44 Then
                SendMail "me@my.com;you@you.com", "this is my subject"
                Exit Sub
            End If
        End If
    Next
    
End With


End Sub

Public Sub SendMail(strWho As String, strSubject As String)

ThisWorkbook.SendMail strWho, strSubject

End Sub
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top