Email Alerts Using Excel

March 29, 2010 at 07:28:02
Specs: Windows XP, 3.2g
I have a series of cells that are accumulating percentages of defects from samples of our product. Each defect type has its own threshold. What I want to do is setup an email alert through Excel for each time one of these thresholds are exceeded. Can anyone help with this?

Thanks..Don


See More: Email Alerts Using Excel

Report •

#1
March 29, 2010 at 08:16:20
Hi,

If column E contains the % rate then in column F put a formula that changes to "X" when the threshold is reached or exceeded.

Add this macro as follows:
Right-click the worksheet name tab and select 'View code'
In the VB window that opens enter this code:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHnd

'stop further changes triggering this code
Application.EnableEvents = False

'only run this code if column E (E=5) has changed and next column (F) contains "X"
If Target.Column = 5 And Target.Offset(0, 1).Text = "X" Then
    'create the e-mail object
    Set objEmail = CreateObject("CDO.Message")
    'set e-mail SMTP configuration
    With objEmail
        With .Configuration.Fields
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                = "SMTP.YYYYY.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") _
                = "30"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") _
                = "2"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
                = "25"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") _
                = "XXXXX@YYYYY.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") _
                = "1"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") _
                = "XXXXXX@YYYYY.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") _
                = "ZZZZZZZZZZ"
            .Update
        End With
        'create e-mail message
        .To = "MMMMMMM"
        .From = "NNNNNNNNN"
        .Subject = "ExcelTest"
        .TextBody = Target.Offset(0, -1).Text & vbCrLf & Target.Text
        'optional add attachment
        '.AddAttachment ()
        'send e-mail
        .send
    End With
    'remove the e-mail object
    Set objEmail = Nothing
End If
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End Sub

The various e-mail fields will need to be completed.
Note that there is a plain text password in this code.
I suggest that you create an e-mail account just for this use, so that the password cannot be used to gain access to someone's e-mail account.

Technically the first part of the e-mail code only needs to be run once, then the message part can be used repeatedly, but for your use, it's probably OK to run both each time.

The code is triggered by a change in the value in a cell in column E, and then the code tests if there is an "X" in column F showing that the changed value has reached the threshold.

The message body can be created as shown in this line

.TextBody = Target.Offset(0, -1).Text & vbCrLf & Target.Text
by using the Target cell as the base address (column E) and using Offset(row, column) to collect other data, such as product name, % etc.


Hope this gives you a starting point for a solution.

Regards


Report •

#2
March 29, 2010 at 08:39:10
re: Note that there is a plain text password in this code.

You should also password protect the VBA Project so that it is Locked for Viewing.

If you need help doing that, let us know.


Report •

#3
March 29, 2010 at 08:45:29
Thanks... I will give it a try and let you know.

Report •

Related Solutions

#4
March 29, 2010 at 12:32:39
I might need to give a little more detail because I am not real familar with using VB

The actual cells read this:
Columns B - K, Row 32 are the Standard % allowed
Columns B - K, Row 33 are the Actual Average %
Columns B - K, Row 34 are the differences between 32 and 33

As long as the Row 34 % are negative then eveything is OK.
If the Row 34 % are positive then I want it to send me an email.

I am not sure if it matters or not but the B33 cell includes an "=IF(ISERROR(BH10," ",(BH10))" formula in it.


Report •

#5
March 29, 2010 at 15:59:58
Hi,

I have modified a couple of lines of code.

This code which starts:

Private Sub Worksheet_Change(ByVal Target As Range)
always runs when any cell on that worksheet changes.

The code now tests to see if the change occurred on the Actual Average % row - Row 33.
It also tests to see if the percentage difference (one row below the Actual Average %) is zero or greater.

This is the first new line:

If Target.Row = 33 And Target.Offset(1, 0).Value >= 0 Then

(replaces If Target.Column = 5 And Target.Offset(0, 1).Text = "X" Then)

Target is the cell that changed, and Excel makes the target cell information available every time there is a change - this is an 'Event' driven process, referred to as an 'On Change event'

Knowing which cell changed the macro can determine what the change was and where it was. Knowing where it was allows related information to be checked - I use Offset (row, column) as a way to obtain the related information - as an offset from the target cell.

The second line I have changed is the one that creates the message:

        .TextBody = "Item " & Target.Offset(-2, 0).Text & " is out of specification" & _
                " ... " & "Max failures allowed is " & Target.Offset(-1, 0).Text & _
                " ... " & "Actual failure rate is " & Target.Text

Replace the previous line that started '.TextBody='

Again Offset is used to get related information.

I had the item description on row 31, an offset of -2 rows from the target cell.
The 'Allowed %' was one row above and the actual value was the target cell itself - so no offset needed.

Excel allows me to obtain text directly from cells that contain values - so I can get what is showing in the cell (e.g., formatted numbers or percentages rather than the actual underlying value).

I had this in column D:

	D
31	Widget - Alloy - Size #3
32	4.00%
33	4.02%
34	0.02%

This was the body of the e-mail I received:

Item Widget - Alloy - Size #3 is out of specification ... Max failures allowed is 4.00%
... Actual failure rate is 4.02%

I would like to get carriage returns in the message, but no luck so far. I know that it is an issue - I just don't know how to resolve it!
(I tried <br /> and \n as well as vbCrLf)

Regards


Report •

#6
March 30, 2010 at 04:49:59
So far I havent been able to get it to work. Does this line need to remain in the code?

'only run this code if column E (E=5) has changed and next column (F) contains "X"


Report •

#7
March 30, 2010 at 05:08:55
Hi,

Any line that starts with a single quote ' is a comment line and has no impact on the macro.

As this macro is event driven, it would be a good starting point to see if it is being triggered when data in row 33 changes.

Go to the new line

If Target.Row = 33 And Target.Offset(1, 0).Value >= 0 Then

Click in the border immediately to the left of the code. This will produce a button in the border and highlight the line of code. This creates a break point. The code will stop at this point.

Go back to the main Excel window (you can use Alt+f11).
Change a value on row 33.

If this macro has been correctly installed, the VB code window will open and the highlighted line will be seen, but now highlighted in yellow.

Click f8 to single step the code line by line.
If the change in row 33 created an 'out of range' value, then single stepping will show the macro continuing to the next line and into the e-mail section.

If the macro jumps to End If, record the values in the cell you just changed and the cell containing the difference, and let me know what there are and what format is applied to each cell.

Also if the code never runs at all after you add the break point and change a value on row 33, let me know and we can look in more detail at what is not correct.

Regards



Report •

#8
March 30, 2010 at 06:03:28
Nothing changed. The VB box never came up or anything highlighted in yellow.

Report •

#9
March 30, 2010 at 07:31:07
Hi,

1.
Did you get the break point to show - the button and highlighted text.

2.
What cell did you change.

Regards


Report •

#10
March 30, 2010 at 07:42:26
Yes. In the code I tapped on the pane just to the left of the "If Target.Row = 33 And Target.Offset(1, 0).Value >= 0 Then" There was a dot that appeared and the string of text was highlighted.

I changed a cell that made B33 change. When that didnt work I made each of the Line 33 cells change to above the threshold. Nothing happened.


Report •

#11
March 30, 2010 at 08:00:16
Hi,

Goto cell B33. Select the cell.
Click inside the formula bar, then click the adjacent check box.

Does this trigger the code.

Regards


Report •

#12
March 30, 2010 at 08:09:17
Nothing is happening.

Report •

#13
March 30, 2010 at 08:26:16
Hi,

In that case the code is not installed as it should be.

1.
Click on the line of highlighted code in the VB window.

What is highlighted in the Project Explorer pane on the left, and what is above it after VBAProject

2.
Copy the whole of the code in the code window and paste it into a reply between <pre> and </pre> tags that you can find above the reply box.

Regards


Report •

#14
March 30, 2010 at 08:56:18
+ QIMacrosSPCforExcel(QIMacros2007.xlam)
+ QIMacrosSPCforExcel(QIMacros.xla)
- VBAProject(VAP Finished Product Assessment Log - 1.xls
Microsoft Excel Objects
 
***THEN A LIST OF THE Excel SHEETS within the file.

The first 2 items QIMacros is an Ad-In that I was using quite sometime ago but no longer using.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHnd

'stop further changes triggering this code
Application.EnableEvents = False

'only run this code if column E (E=5) has changed and next column (F) contains "X"
If Target.Row = 33 And Target.Offset(1, 0).Value >= 0 Then
    'create the e-mail object
    Set objEmail = CreateObject("CDO.Message")
    'set e-mail SMTP configuration
    With objEmail
        With .Configuration.Fields
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                = "SMTP.YYYYY.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") _
                = "30"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") _
                = "2"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
                = "25"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") _
                = "XXXXX@YYYYY.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") _
                = "1"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") _
                = "XXXXXX@YYYYY.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") _
                = "ZZZZZZZZZZ"
            .Update
        End With
        'create e-mail message
        .To = "MMMMMMM"
        .From = "NNNNNNNNN"
        .Subject = "ExcelTest"
        .TextBody = "Item " & Target.Offset(-2, 0).Text & " is out of specification" & _
                " ... " & "Max failures allowed is " & Target.Offset(-1, 0).Text & _
                " ... " & "Actual failure rate is " & Target.Text
        'optional add attachment
        '.AddAttachment ()
        'send e-mail
        .send
    End With
    'remove the e-mail object
    Set objEmail = Nothing
End If
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End Sub


Report •

#15
March 30, 2010 at 09:33:49
Hi,

I pasted the code back into a worksheet that contained the sample data on rows 33 etc. and it was triggered as expected.

Can you say specifically which Microsoft object is highlighted.

Am I correct in thinking that the workbook is: VAP Finished Product Assessment Log - 1.xls
If so, what is the name of the actual worksheet.

Regards


Report •

#16
March 30, 2010 at 09:36:30
Sheet1 (Finished Product - Arg)

Report •

#17
March 30, 2010 at 09:41:43
Hi,

OK, select Sheet1 (Finished Product - Arg) in the Project Explorer window (it will be under Microsoft Excel Objects which will be under VBAProject (VAP Finished Product Assessment Log - 1.xls)

Now double-click Sheet1 (Finished Product - Arg)

Does the large VB window show the code.

Regards


Report •

#18
March 30, 2010 at 09:52:18
Yes it does.

Report •

#19
March 30, 2010 at 10:05:49
Hi,

In that case I am not clear why it isn't triggering.

If you like, I will take a look at the spreadsheet.

Just let me know, and I will send you an email address through the private message system.

Just as one more thought -
go to the VB window. From the menu select View - Immediate window.
In the new window/pane that opens, scroll down to the bottom of the list and enter this:
Application.EnableEvents=True
then click Enter.
Close the immediate window and try selecting Cell B33, click inside the formula bar and then click the check mark next to it.

Regards


Report •

#20
March 30, 2010 at 10:16:04
When I entered the text in to the Immediate section I get an error message that says:

"The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros."

I went into the Excel settings and "Enabled Macros" earlier with no effect. I am wondering if the Ad-In program QIMacros is prohibiting the code from functioning????

If you dont have a better suggestion for enabling this then I will send you the file. Or maybe it's better that you do it as it appears I have no clue :)


Report •

#21
March 30, 2010 at 10:34:02
Hi,

I think that you are using Excel 2007.
Go to the Office button, select Excel options at the bottom
Select the 'Trust Center' tab
Click 'Trust Center Settings...'
Check the 'Trust access to the VBA project object module
and at the top select Disable all macros with notification.
(You will always get a warning about macros and they will not run unless specifically allowed).
Click OK, OK
Save the workbook
Close Excel.

Re-open Excel and try again.

I will send you an email address if you still can't get it to work.

Regards
PS to disable an Add-In that is not being used:
Office Button - Excel Options - Add-Ins Tab
In the 'Manage:' drop-down list at the bottom select Excel Add-Ins
Click 'Go'
Find your Add-In in the list.
Clear the check box beside it.
Click OK



Report •

#22
March 30, 2010 at 10:53:28
Hmmm. My Excel options are correct and I still get the same error message about enabling Macros. Also, oddly enough I cannot find the QIMacros in the Add-In section of Excel Options. Should I just uninstall the program (assuming that I can find it)?

Report •

#23
March 30, 2010 at 11:07:42
Hi,

Try looking in Com Add-Ins
In the drop-down list instead of Excel Add-Ins choose Com Add-Ins. Some Add-ins, particularly commercially available ones are in this group.

But if you don't use, it is probably best to uninstall it.

Regards


Report •

#24
March 30, 2010 at 12:21:31
I check there as well when I looked at Add-Ins. No luck.. At any rate I emailed you the actual file per your PM.

Report •

Ask Question