Hello,
I am attempting to send an email based on a indirect cell value change (e.g. > 7), with the email attachment and specific cell reference to the changed cells in my outing mail body. I've got the indirect cell value change down based on the criteria. However I am getting a 424 error when attempting to set a variable to call the cell address into my email. Could you assist? I feel I am close.
Dim xRg As Range
Dim xRgSel As Range
'Set range & target
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRgPre As Range
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Range("Q2:Q43")
Set xRgPre = xRg.Precedents
Set xRgSel = Intersect(Target, xRg)
ActiveWorkbook.Save
If xRg.Value > 7 Then
Call Mail_small_Text_Outlook
ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Adress) Then
End If
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 there, cells(s)" & xRgSel.Address(False, False) & _
" in the worksheet '" & Me.Name & "' are 3 days past intake" & vbNewLine & vbNewLine & _
"Please review and reach out to the lead(s)" & vbNewLine & _
"Thank you"
On Error Resume Next
With xOutMail
.To = "rdavis@bridge.partners"
.CC = ""
.BCC = ""
.Subject = "Days since lead intake"
.Body = xMailBody
.Attachments.Add (ThisWorkbook.FullName)
.Display 'or .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Thanks!
I am attempting to send an email based on a indirect cell value change (e.g. > 7), with the email attachment and specific cell reference to the changed cells in my outing mail body. I've got the indirect cell value change down based on the criteria. However I am getting a 424 error when attempting to set a variable to call the cell address into my email. Could you assist? I feel I am close.
Dim xRg As Range
Dim xRgSel As Range
'Set range & target
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRgPre As Range
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Range("Q2:Q43")
Set xRgPre = xRg.Precedents
Set xRgSel = Intersect(Target, xRg)
ActiveWorkbook.Save
If xRg.Value > 7 Then
Call Mail_small_Text_Outlook
ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Adress) Then
End If
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 there, cells(s)" & xRgSel.Address(False, False) & _
" in the worksheet '" & Me.Name & "' are 3 days past intake" & vbNewLine & vbNewLine & _
"Please review and reach out to the lead(s)" & vbNewLine & _
"Thank you"
On Error Resume Next
With xOutMail
.To = "rdavis@bridge.partners"
.CC = ""
.BCC = ""
.Subject = "Days since lead intake"
.Body = xMailBody
.Attachments.Add (ThisWorkbook.FullName)
.Display 'or .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Thanks!
- Page :
- 1
There are no replies made for this post yet.