Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
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 = " '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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
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 = " '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 "M Judd" wrote: 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 = " '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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
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 = " 'Uncomment the line below to hard code a subject .Subject = "Check T-Bill balances" .Attachments.Add WB.FullName End With End Sub "M Judd" wrote: 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 = " '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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |