Solved

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

Posted on 2013-01-28
18
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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