Auto generate email after checking a field where cirtain cells =x or any designated value

Hi ,

I am looking to Auto generate email after checking a field where cirtain cells =x or any designated value(very urgent :-))

thanks very much

shamil
example-spreadsheet.xlsx
shamilazAsked:
Who is Participating?
 
Robberbaron (robr)Connect With a Mentor Commented:
1. to use a breakpoint, right click on the desired line and select add breakpoint. This causes the code to stop at that point and you can then print values etc.   Even hovering over a variable will display its current value. You can then step through the code using F8.

2. with your screenshot, a JPG or PNG would be better than Word file. (i guess your way works but is a pain my end)

2. Jim is correct in that it seems you changed the offset in the code, yet left the comment "address is 3 columns to the left.
I deliberately get the value of the offset into the variable  
emailTo
at the top of the routine so I can test it. this is the likely cause of the "name error".

change back to
.To = emailTo

Open in new window

0
 
Robberbaron (robr)Commented:
need more info on what the process is.
1/ what do you mean by 'checking' ?
2/ which cells =   what value ?
3/ how does the sheet get updated ?

write soem psuedo-code or flowchart on what is to happen.
what code have you written ? (we dont get paid to post on this site, just help others)


see http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27907980.html

this had a similar requirement.
0
 
J2FCommented:
the link in robberbaron's post pretty well covers the basics. in your case i would have the worksheet change event monitor the "send email" column and trigger when a cell value becomes "x". i would add code in the email function to change the "x" to something to indicate the email had been sent to prevent emails being resent.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
shamilazAuthor Commented:
-Thanks for your time guys, it is appreciated,

unfortunately I am not an excel macro expert at all.  I tried this by recording a macro without any success.

What I mean by "Checking" is actually looking up field "E"(Send email Trigger), and when cell value in Field "E" ="x" or any other disignated value.

The spreadsheet is updated manually.

thanks
0
 
Robberbaron (robr)Commented:
please post your workbook (xlsm) with the macro you recorded as this is very informative as to the process you want to happen.

excel updates many cells whenever some value changes and we can hook into this using the Worksheet_Change event.

but we need you to tell us the process in detail...
ie when cell value in Column E changes to be 'x' and column F value is 'yes' , then send email to the person listed in Column G from test1@ zxcvv

what causes column E to change ? some changes dont trigger events.

what happens if column G is empty  ?
0
 
shamilazAuthor Commented:
Hi,

thanks again for your time

look for "0" in column "E" and if value in any cel is "0" then go to column "G" and send email to listed email addresses in column "G".  If no email addresses listed in column "G" no action to be taken.  This ducument will reside on a sharepoint site.  Email will have to be auto generated in the morning once a day.  

Sub emaildemo()
'
' emaildemo Macro
'

'
    ActiveCell.Offset(5, 6).Range("A1").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    ActiveCell.Offset(2, 0).Range("A1").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    ActiveCell.Offset(2, -2).Range("A1").Select
End Sub
0
 
Robberbaron (robr)Commented:
Does a user open the document and do updates  or do you want it to be automated somehow.  
The latter would require different approach as it my need to be on a system wit excel installed.
How ddoes column e get zero value ?
0
 
shamilazAuthor Commented:
Hi ,

Just completed a more detailed version for you. and filled in so that it may answer your questions better.

The document will be opened daily from the share drive to update the manual fields and checked back into sharepoint.  However only the emails have to be auto generated.

thanks very much
order-Tracking---WIP---30-1-2013.xlsm
0
 
shamilazAuthor Commented:
Hi ,

This macro shown below works on a single cell.  I am at a loss as to how to change this to a range.  I.e when any cell on column K changes to "0" how should the listed macro change to reflect this.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me
        If Intersect(.[k9], Target) Is Nothing Then Exit Sub
        If UCase(.[k9].Value) <> "0" Then Exit Sub
        Dim OutApp As Outlook.Application
        Dim OutMail As Outlook.MailItem
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(olMailItem)
        With OutMail
            .To = "shamil@email.com
            .CC = ""
            .Subject = "Notification"
            .Body = "Cell A1 Changed."
            .Send
        End With
        Set OutMail = Nothing
        Set OutApp = Nothing
        On Error GoTo 0
    End With
End Sub
0
 
Robberbaron (robr)Commented:
close.... you can check Target range to get what column it is and test if it is the one to check.

i changed they way you show IF THEN to remove the exit subs, make it easier to add more checks later.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim emailTo As String
    With Me
        If Target.Column = 11 Then
            'Column K is 11th. A=1, J=10 etc
            If UCase(Target.Value) = "0" Then
                emailTo = Target.Offset(0, -3).Value  'address is 3 columns to left
                Dim OutApp As Outlook.Application
                Dim OutMail As Outlook.MailItem
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(olMailItem)
                With OutMail
                    .To = "shamil@email.com"
                    .CC = ""
                    .Subject = "Notification"
                    .Body = "Cell A1 Changed."
                    .Send
                End With
                Set OutMail = Nothing
                Set OutApp = Nothing
            End If
        End If
        On Error GoTo 0
    End With
End Sub

Open in new window

0
 
shamilazAuthor Commented:
Hi ,

This works great...however it only sends the emails to the address listed in the To: area in the macro.  it does not for some reason send the emails to the addresses listed in column offset(0,-3).  I also added a line to add an attachement.

also if the criteria has changed to "0" while the document is in sharepoint and a user opens this document the next day (as sharepoint does not support macros) will the email be generated? or does the macro have to be physically activated via a button control.  I tried linking the macro to a button control and it did not work.  your input is greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim emailTo As String
    With Me
        If Target.Column = 11 Then
            'Column K is 11th. A=1, J=10 etc
            If UCase(Target.Value) = "0" Then
                emailTo = Target.Offset(0, -3).Value  'address is 3 columns to left
                Dim OutApp As Outlook.Application
                Dim OutMail As Outlook.MailItem
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(olMailItem)
                With OutMail
                    .To = "shamil@email.com"
                    .CC = ""
                    .Subject = "Notification"
                    .Body = "Cell A1 Changed."
                    .Attachments.Add ActiveWorkbook.FullName
                    .Send
                End With
                Set OutMail = Nothing
                Set OutApp = Nothing
            End If
        End If
        On Error GoTo 0
    End With
End Sub
0
 
Robberbaron (robr)Commented:
1. macro is triggered by any Worksheet_Change.   http://dmcritchie.mvps.org/excel/event.htm suggests it wont be triggered at loadup.  You may need to look at Workbook_Open event and get it to review all of column K.


something like
for row=1 to 999
    if worksheet("test").cells(row,11) = "0" then SendMail(...)
next row

2.  Email address...
  .To = emailTo     '/// not "shamil@email.com"

Open in new window

0
 
shamilazAuthor Commented:
thanks

2.  Email address...

  .To = emailTo     '/// not "shamil@email.com"

still does not work unfortunately
0
 
J2FCommented:
be sure the "emailTo" is being initialized to a valid email address in the following line:

        emailTo = Target.Offset(0, -3).Value  'address is 3 columns to left

Jim
0
 
Robberbaron (robr)Commented:
what error is thrown ?

put a breakpoint on the .To line to check
0
 
shamilazAuthor Commented:
here is a screenshot attached
Doc1.docx
0
 
J2FConnect With a Mentor Commented:
your example above uses:
                emailTo = Target.Offset(0, -3).Value  'address is 3 columns to left

the code in your screenshot uses:
                emailTo = Target.Offset(0, -2).Value  'address is 3 columns to left
these are not the same:

i tested the example code, placing a valid email address in column H and it worked just fine.
(office 2012)
be sure your offset value is pointing to a valid email

in your code example you test:
       If UCase(Target.Value) = "0" Then
granted this code executes without error, but I would like to point out there is no such animal as UCase of the digit 0 (or any other digit for that matter).

Jim
0
 
shamilazAuthor Commented:
thanks all...much appreciated
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.