Macro Help

[M Judd]

Instead of using outlook calendar alert, I have set up a macro in excel to
email me when I run the macro. Please see below.

I have a formula in a worksheet, which when negative indicates that I need
to physically do something manuel in my life (i.e. T-bills have matured, Time
to buy more). How do I change the macro below so that it looks at a cell or
range of cells (I have more than 1 T-bill), and then executes the subroutine
below, which will remind me (email me) that it is time for me to order new
T-bills. I have the date issues under control in excel so that part is not a
problem.

Also this subroutine emails me with an attached excel worksheet file. The
subject of the email is check t-bill balances, which is fine. However, I do
not need the attached file but instead to have the body of the email contain
a range within the worksheet.

Any help would be greatly appreciated. The macro below, I found on-line and
I have little experience in Visual basic.



Sub TBill_2()
'
' TBill_2 Macro
' Macro recorded 5/23/2007 by MTJ
'
If j11 < 0 Then
Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "T_Bill.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "(e-mail address removed)"
'Uncomment the line below to hard code a subject
.Subject = "Check T-Bill balances"
.Attachments.Add WB.FullName
End With
Else

End If
End Sub

 

[Joel]

I modified macro to check all cells in column J for less than 0

Sub TBill_2()
'
' TBill_2 Macro
' Macro recorded 5/23/2007 by MTJ
'
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
MyRange = Range(Cells(1, "J"), Cells(LastRow, "J"))

For Each cell In MyRange
If IsNumber(cell) Then
If j11 < 0 Then
Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "T_Bill.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "(e-mail address removed)"
'Uncomment the line below to hard code a subject
.Subject = "Check T-Bill balances"
.Attachments.Add WB.FullName
End With
Else

End If
End If
Next cell
End Sub

 

[Joel]

the program I sent wasn't running try these fixes

Sub TBill_2()
'
' TBill_2 Macro
' Macro recorded 5/23/2007 by MTJ
'
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Set MyRange = Range(Cells(1, "J"), Cells(LastRow, "J"))

For Each mycell In MyRange
If WorksheetFunction.IsNumber(mycell.Value) = True Then
If mycell.Value < 0 Then

Call EmailWithOutlook

End If
End If
Next mycell
End Sub
Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "T_Bill.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "(e-mail address removed)"
'Uncomment the line below to hard code a subject
.Subject = "Check T-Bill balances"
.Attachments.Add WB.FullName
End With
End Sub