Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I send an email too?
I'm using Excel 2010 and I use the code below to search through a db,
find values of a certain type then copy that entire row to another sheet and it works fine for that purpose. One of the fields that gets copied is the manager's email address. Is there code that I can add that will send this manager a short message? Thanks very much. 'This macro searches for a value 'in column G that is greater than 1.5 'and copies the entire row to sheet 2 Sub SearchForValue() Dim LSearchRow As Integer Dim LCopyToRow As Integer On Error GoTo Err_Execute 'Start search in row 2 LSearchRow = 2 'Start copying data to row 2 in Sheet2 (row counter variable) LCopyToRow = 2 While Len(Range("A" & CStr(LSearchRow)).Value) 0 'If value in column G 1.5, copy entire row to Sheet2 If Range("G" & CStr(LSearchRow)).Value 1.5 Then 'Select row in Sheet1 to copy Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Copy 'Paste row into Sheet2 in next row Sheets("Sheet2").Select Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select ActiveSheet.Paste 'Move counter to next row LCopyToRow = LCopyToRow + 1 'Go back to Sheet1 to continue searching Sheets("Sheet1").Select End If LSearchRow = LSearchRow + 1 Wend 'Position on cell A3 Application.CutCopyMode = False Range("A3").Select MsgBox "All matching data has been copied." Exit Sub Err_Execute: MsgBox "An error occurred." End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I send an email too?
Check out Ron de Bruin excel pages. You can also search for CDO Code
in the excel forum if you use Exchange. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I send an email too?
All you need for emailing can be found at Ron deBruin's site.
http://www.rondebruin.nl/sendmail.htm Note the SendMail add-in...............very easy to use. Gord On Sat, 31 Dec 2011 04:23:28 -0800 (PST), John Menken wrote: I'm using Excel 2010 and I use the code below to search through a db, find values of a certain type then copy that entire row to another sheet and it works fine for that purpose. One of the fields that gets copied is the manager's email address. Is there code that I can add that will send this manager a short message? Thanks very much. 'This macro searches for a value 'in column G that is greater than 1.5 'and copies the entire row to sheet 2 Sub SearchForValue() Dim LSearchRow As Integer Dim LCopyToRow As Integer On Error GoTo Err_Execute 'Start search in row 2 LSearchRow = 2 'Start copying data to row 2 in Sheet2 (row counter variable) LCopyToRow = 2 While Len(Range("A" & CStr(LSearchRow)).Value) 0 'If value in column G 1.5, copy entire row to Sheet2 If Range("G" & CStr(LSearchRow)).Value 1.5 Then 'Select row in Sheet1 to copy Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Copy 'Paste row into Sheet2 in next row Sheets("Sheet2").Select Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select ActiveSheet.Paste 'Move counter to next row LCopyToRow = LCopyToRow + 1 'Go back to Sheet1 to continue searching Sheets("Sheet1").Select End If LSearchRow = LSearchRow + 1 Wend 'Position on cell A3 Application.CutCopyMode = False Range("A3").Select MsgBox "All matching data has been copied." Exit Sub Err_Execute: MsgBox "An error occurred." End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I send an email too?
On Dec 31 2011, 5:23*pm, John Menken wrote:
I'm using Excel 2010 and I use the code below to search through a db, find values of a certain type then copy that entire row to another sheet and it works fine for that purpose. One of the fields that gets copied is the manager's email address. Is there code that I can add that will send this manager a short message? Thanks very much. 'This macro searches for a value 'in column G that is greater than 1.5 'and copies the entire row to sheet 2 Sub SearchForValue() * * Dim LSearchRow As Integer * * Dim LCopyToRow As Integer * * On Error GoTo Err_Execute * * 'Start search in row 2 * * LSearchRow = 2 * * 'Start copying data to row 2 in Sheet2 (row counter variable) * * LCopyToRow = 2 * * While Len(Range("A" & CStr(LSearchRow)).Value) 0 * * * * 'If value in column G 1.5, copy entire row to Sheet2 * * * * If Range("G" & CStr(LSearchRow)).Value 1.5 Then * * * * * * 'Select row in Sheet1 to copy * * * * * * Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select * * * * * * Selection.Copy * * * * * * 'Paste row into Sheet2 in next row * * * * * * Sheets("Sheet2").Select * * * * * * Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select * * * * * * ActiveSheet.Paste * * * * * * 'Move counter to next row * * * * * * LCopyToRow = LCopyToRow + 1 * * * * * * 'Go back to Sheet1 to continue searching * * * * * * Sheets("Sheet1").Select * * * * End If * * * * LSearchRow = LSearchRow + 1 * * Wend * * 'Position on cell A3 * * Application.CutCopyMode = False * * Range("A3").Select * * MsgBox "All matching data has been copied." * * Exit Sub Err_Execute: * * MsgBox "An error occurred." End Sub Hi John, You can send email automatically from Gmail, Yahoo or Outlook configured to your system. You can send it through an attachment also. To get the complete code.. kindly visit this link: http://www.learnexcelmacro.com/2011/...mail-or-yahoo/ http://www.learnexcelmacro.com/2011/...-from-outlook/ Let me know, if it works for you... Thanks, Vish |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send email from excel to notes - save email in sent folder | Excel Programming | |||
Excel VBA macro to send email attachment from default email client | Excel Programming | |||
Send data from Excel in email from specific email address | Excel Programming | |||
send wkbk as an email attachment with an email address copied from | Excel Discussion (Misc queries) | |||
body of email disappears when I send an email from Excel | Excel Discussion (Misc queries) |