Can I send an email too?

  • Thread starter Thread starter John Menken
  • Start date Start date
J

John Menken

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
 
Check out Ron de Bruin excel pages. You can also search for CDO Code
in the excel forum if you use Exchange.
 
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
Click to expand...

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/12/how-to-send-an-email-using-excel-macro-from-gmail-or-yahoo/

http://www.learnexcelmacro.com/2011/12/how-to-send-email-by-excel-macro-from-outlook/

Let me know, if it works for you...

Thanks,
Vish
 
Back
Top