• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need correction on VBA CODE - Email is sent thrice but I wanted email to go only once


New Member
Below is my VBA Code to send emails Automatically but unfortunately emails are sent thrice but I wanted email to be sent only once. Please help me.
>>> use code - tags <<<
Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("C5:V17"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi Team" & vbNewLine & vbNewLine & _
              "I am on leave today, please refer the attachment for more details." & vbNewLine & _
              "                                                            " & vbNewLine & _
              "\\inba1fs1.corp.emc.com\GBS_BLR_PS_OnBoarding_Services\Leave_Calendar" & _
              "                                                            " & vbNewLine & _
              "Thank you"
    On Error Resume Next
    With xOutMail
        .To =  email@address.com ' Don't show email-address here!
        .CC = ""
        .BCC = ""
        .Subject = "I am on leave today"
        .Body = xMailBody
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub
Last edited by a moderator:


Active Member
Please use the CODE identifiers when posting your code. You will find it in the drop down menu, immediately to the right of the Happy Face.

< / > Code

Regarding your issue ... I am unable to reproduce the error here. The macro as presented above creates ONE email here.