Solocharles
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 <<<
>>> use code - tags <<<
Code:
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
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Last edited by a moderator: