Solved

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

Posted on 2013-01-28
18
258 Views
Last Modified: 2013-02-06
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
0
Comment
Question by:shamilaz
  • 8
  • 7
  • 3
18 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38830754
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
 
LVL 1

Expert Comment

by:J2F
ID: 38831123
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
 

Author Comment

by:shamilaz
ID: 38831948
-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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38834947
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
 

Author Comment

by:shamilaz
ID: 38837072
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38838288
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
 

Author Comment

by:shamilaz
ID: 38838388
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
 

Author Comment

by:shamilaz
ID: 38840403
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38842284
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:shamilaz
ID: 38842785
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38842918
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
 

Author Comment

by:shamilaz
ID: 38844178
thanks

2.  Email address...

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

still does not work unfortunately
0
 
LVL 1

Expert Comment

by:J2F
ID: 38845048
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38845692
what error is thrown ?

put a breakpoint on the .To line to check
0
 

Author Comment

by:shamilaz
ID: 38851620
here is a screenshot attached
Doc1.docx
0
 
LVL 1

Assisted Solution

by:J2F
J2F earned 150 total points
ID: 38852246
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
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 350 total points
ID: 38853134
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
 

Author Closing Comment

by:shamilaz
ID: 38860741
thanks all...much appreciated
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now