Hello,
Can anyone help me out ??
Private Sub Worksheet_Change(ByVal Target As Range)
Dim j As Long
Dim xRgSel As Range
Dim xOutApp As Object
Dim xMailItem As Object
Dim xMailBody As String
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xRgSel = Intersect(Target, Range("R10:R20"))
ActiveWorkbook.Save
j = 10
For Each Cell in xRgSel
If xRgSel Is Nothing Then Exit Sub
If xRgSel = "A" or xRgSel = "B" or xRgSel = "C" Then
Set xOutApp = CreateObject("Outlook.Application")
Set xMailItem = xOutApp.CreateItem(0)
With xMailItem
.To = Cells(j, "S").Value
.Cc = Cells(j, "X").Value
.Subject = Cells(j, "Y").Value
.Body = Cells(j, "Z").Value
'.Attachments.Add (ThisWorkbook.FullName)
.Display
End With
Set xRgSel = Nothing
Set xOutApp = Nothing
Set xMailItem = Nothing
End If
j = j + 1
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
What I'm stuck is that the output of S,X,Y & Z freeze into row 10 only. Whenever the value of R11 is given, the output of S,X,Y & Z remain same as row 10. Can anyone help me out solve this ??? Any modification with this script ???
N:B - S,X,Y & Z value already exists in the cell.
Can anyone help me out ??
Private Sub Worksheet_Change(ByVal Target As Range)
Dim j As Long
Dim xRgSel As Range
Dim xOutApp As Object
Dim xMailItem As Object
Dim xMailBody As String
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xRgSel = Intersect(Target, Range("R10:R20"))
ActiveWorkbook.Save
j = 10
For Each Cell in xRgSel
If xRgSel Is Nothing Then Exit Sub
If xRgSel = "A" or xRgSel = "B" or xRgSel = "C" Then
Set xOutApp = CreateObject("Outlook.Application")
Set xMailItem = xOutApp.CreateItem(0)
With xMailItem
.To = Cells(j, "S").Value
.Cc = Cells(j, "X").Value
.Subject = Cells(j, "Y").Value
.Body = Cells(j, "Z").Value
'.Attachments.Add (ThisWorkbook.FullName)
.Display
End With
Set xRgSel = Nothing
Set xOutApp = Nothing
Set xMailItem = Nothing
End If
j = j + 1
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
What I'm stuck is that the output of S,X,Y & Z freeze into row 10 only. Whenever the value of R11 is given, the output of S,X,Y & Z remain same as row 10. Can anyone help me out solve this ??? Any modification with this script ???
N:B - S,X,Y & Z value already exists in the cell.