Automatic send email (without even click send in mail software) with excel vba ??

Hi,

Currently I have a worksheet (attached excel file) which able to trigger send-email-sub macro when specific criteria meet.

Refer to attached excel file -
When value on D5:D6 is smaller then E5:E6, msgbox will show and if Yes was choose, it will go to email (outlook or Thunderbird or Hotmail). Then just click send and the email will send to email address which show in I5:I6.

So is it possible to ignore above step, which mean When value on D5:D6 is smaller then E5:E6, email will automatic send (even without click send button in email (outlook or Thunderbird or Hotmail)?

BR
KC

Originally Posted by alexnkc

Hi,

Currently I have a worksheet (attached excel file) which able to trigger send-email-sub macro when specific criteria meet.

Refer to attached excel file -
When value on D5:D6 is smaller then E5:E6, msgbox will show and if Yes was choose, it will go to email (outlook or Thunderbird or Hotmail). Then just click send and the email will send to email address which show in I5:I6.

So is it possible to ignore above step, which mean When value on D5:D6 is smaller then E5:E6, email will automatic send (even without click send button in email (outlook or Thunderbird or Hotmail)?

BR
KC

• Alt+F11 to open the VBA Editor
• Double-click on ThisWorkbook in the upper left VBA Project window.
• Replace the code in the edit window with the code below. Just the three green lines have been commented out.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lngResponse As Long
    Dim URL As String, strEmail As String, strSubject As String
    If Left(Target.Address, 2) = "$D" Then
        If Target.Value < Range("$E" & Right(Target.Address, 2)).Value Then
            'lngResponse = MsgBox("The inventory quantity for this item has dropped below the reorder level. Would you like to reorder this item?", vbYesNo)
            'If lngResponse = vbYes Then
                strEmail = Range("$I" & Right(Target.Address, 2)).Value
                strSubject = "New order for " & Range("$F" & Right(Target.Address, 2)).Value & " '" & Range("$B" & Right(Target.Address, 2)).Value & "' items. The remaining " & "'" & Range("$B" & Right(Target.Address, 2)).Value & "' was " & Range("$D" & Right(Target.Address, 2)) & ". This data was capture on " & Format(Now, "dd-mmm-yy, hh:mm AM/PM")
                strSubject = Application.WorksheetFunction.Substitute(strSubject, " ", "%20")
                strURL = "mailto:" & strEmail & "?subject=" & strSubject
                ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus
            'End If
        End If
    End If
End Sub

Hi,

Thanks Cheeky Charlie & AlphaFrog for your reply.

By removing IngResponse, vbYes and End If. when specific criteria meet, email prepared automatically and by just click "send", target group of people will receive the mail.

How about when specifit criteria meet, email prepared automatically and will send automatically as well to target group of people (without even need to click "send", then the email only sending out)? Is VBA able to do that??

BR
KC