Email using a macro

[s_smith_iet]

I need a macro that will send an email to people if:

sheet Main in workbook NPI data entry form has the number 45 in Cell
A3

I need the email to say something like this:

Attention:
The chamber now has ran 45 cycles since last maintenance.

ALso I need another macro that sends an email once the maintenance is
completed..they would click a button that brings up a bunch of dialog
boxes that put the machine name, the maintenance performed, the date,
and the person who complete the task in sheet maintenance 1 A2, B2,
C2, D2, and E2.

Any help would be great

 

[Corey ....]

See the Excel Email Masters site for How to's when emailing.
http://www.rondebruin.nl/sendmail.htm
Corey....

 

[s_smith_iet]

there is only one on there that is kind of what I need but not
really....
This cell could stay at 45 for days but I only want the email to go
out once this seems to activate the macro every time I go to a
different cell in the work sheet

 

[Corey ....]

which code was similar ?

 

[s_smith_iet]

This was the code:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("A1"), rng) Is Nothing Then
If Range("A1").Value > 200 Then YourMacroName
End If
End If
EndMacro:
End Sub



It always ended on error when I used a simple macro the would open a
msg box.... but it seems like ever time I click on a cell it would
come on because the cell it as looking at was still 45.....

what happens is once it gets to 50 the operator is going to perform a
maintenance and then use a dialog box to enter in what he/she
performed which will bring the number down to zero again...then
eventually it'll go back to 45 (it takes about a month for this
process to end)....so if I could get an email sent at 45 and then when
it is at 0 that would work. so this email would like the supervisor
know that the maintenance was coming up and then once it has been
completed. It would also be good to send out another email if it goes
over 50 because it is supposed to be completed at the 50th cycle max.

 

[Corey ....]

Try:

Private Sub Workbook_Open()
Sheet1.Select
If Range("A3").Value = 45 And Range("B3").Value = "" Then
'Working in 2000-2007
Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Set Source = Nothing
On Error Resume Next
Set Source = Range("B4").SpecialCells(xlCellTypeVisible)
On Error GoTo 0


If Source Is Nothing Then
MsgBox "The source is not a range or the sheet is protected, please
correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)
Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With
TempFilePath = Environ$("temp") & "\"
TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy
h-mm-ss")

If Val(Application.Version) < 12 Then
'You use Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
End If
With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
' next line "B5" is the email address(chnage if need be). "B6" is the
Subject. Change if need be also.
.SendMail Sheet1.Range("B5").Value, Sheet1.Range("B6").Value

On Error GoTo 0
.Close SaveChanges:=False
End With
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Range("B3").Value = Date & " " & Time ' Change if need be
End Sub



Activates when WorkBook is Opened.
Save BEFORE running 1st time.


Corey...

 

[Corey ....]

previous code posted ..............
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Range("B3").Value = Date & " " & Time ' Change if need be

' ADD THE FOLLOWING to ALSO EMAIL when A3 Reaches 50 to the previous code
posted.

If Range("A3").Value >49 Then
'Working in 2000-2007
Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Set Source = Nothing
On Error Resume Next
Set Source = Range("B4").SpecialCells(xlCellTypeVisible)
On Error GoTo 0


If Source Is Nothing Then
MsgBox "The source is not a range or the sheet is protected, please
correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)
Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With
TempFilePath = Environ$("temp") & "\"
TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy
h-mm-ss")

If Val(Application.Version) < 12 Then
'You use Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
End If
With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
' next line "B5" is the email address(chnage if need be). "B6" is the
Subject. Change if need be also.
.SendMail Sheet1.Range("B5").Value, Sheet1.Range("B6").Value

On Error GoTo 0
.Close SaveChanges:=False
End With
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Range("B3").Value = Date & " " & Time ' Change if need be

End Sub