Solved

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

Posted on 2013-01-28
18
261 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select only certain columns not entire sheet 12 28
Best Excel  formula for  this scenario 2 36
Creating An Intelligent Dropdown 8 27
Msgbox tickler 10 23
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

776 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