Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Wednesday, June 29, 2022
  13 Replies
  8K Visits
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!
2 years ago
·
#2838
Hi there,

When you get a 424 error, please click debug, and then take a screenshot of the located problem.
debug.png

We ran the code you provided, it worked fine after we changed the end if below to exit sub.
change.jpg

Please change your code accordingly, and then try it to see if it works as you wanted.

Amanda
2 years ago
·
#2843
Thanks, Amanda! However I am still getting the error, and the prompt does not provided a "debug" option. I am fairly new to VBA, could it be my indenting? Seeing as it works on your end. I've tried uploading a screen shot but having a hard time to get my reply message sent (it is Loading for long periods of time, just a headsup).
2 years ago
·
#2844
Hi Ryan, did you only change the strikethrough end if to exit sub? (I noted that there is a typo (eixt) in the screenhot, sorry for that.)
If yes, could you please attach the Excel file where you applied the code?

Amanda
2 years ago
·
#2847
Thanks, Amanda! However I am still getting the error, and the prompt does not provided a "debug" option. I am fairly new to VBA, could it be my indenting? Seeing as it works on your end. I've tried uploading a screen shot but having a hard time to get my reply message sent (it is Loading for long periods of time, just a headsup).


You can try to post it with the attachments now. :)
2 years ago
·
#2858
Hi Amanda, sorry for the delay, I did change the End if to Exit Sub. But I am unable to add the excel file or screenshots for some reason - I cannot reply with the attachments. I couldn't add the excel file anyway as it is an xlsm file and that is not supported for file upload. Can we chat via email? ryandavis1225@gmail.com
2 years ago
·
#2859
Hi Amanda and ExcelFan, sorry for the delay, I did change the End if to Exit Sub. But I am still unable to add the excel file or screenshots for some reason - I cannot reply with the attachments. I couldn't add the excel file anyway as it is an xlsm file and that is not supported for file upload. Anyway we can chat via email? That way I can pass the files
2 years ago
·
#2861
Hi Amanda, sorry for the delay, I did change the End if to Exit Sub. But I am unable to add the excel file or screenshots for some reason - I cannot reply with the attachments. I couldn't add the excel file anyway as it is an xlsm file and that is not supported for file upload. Can we chat via email? ryandavis1225@gmail.com


Hello,

Why not save it as the zip or rar?:)
2 years ago
·
#2864
I cannot post any file type unfortunately, nor links. I've reached out to support but now answer as of yet. Not sure of the cause but upon sending the page just loads and does not send. Have tried multiple browsers, clearing my cache, and multiple devices :/

I appreciate your willingness to help! Unfortunately I cannot pass files at this time
2 years ago
·
#2867
Hi Ryan,

Thanks for the feedback. We will check if there's any problem about file attaching. For now, please send your file to amanda@extendoffice.com

Amanda
2 years ago
·
#2868
Hi Amanda!

This is Ryan4 from ExtendOffice. Thank you for sending your email address to help solve my issue, I intend to be an active member since discovering your fantastic community, so I hope to get my file upload issues resolved eventually!

For now, please see the attached file here. I did update the code to read “Exit Sub” instead of “End if,” however I still receive an error with no debug option. Can you advise? Perhaps it is my structure or indenting.

Thanks again for your assistance!
Ryan

Hi Ryan,

Email messaged received. Since there may be other users who have the same problem, I will reply here.

Please try the method below:
1. Open Reference.
vba-reference.png

2. Check Microsoft Outlook 16.0 Object Library. Note: Instead of 16.0, you might have 15.0, 14.0, etc.
vba-library.png

3. Replace the code below
Dim xOutApp As Object
Dim xOutMail As Object

to
Dim xOutApp As Outlook.Application
Dim xOutMail As Outlook.MailItem


Please try the above 3 steps. Since your vba works fine on our computer, so it's difficult for us to reproduct the problem. Hopefully this method would help you!

Amanda
2 years ago
·
#2887
Hi Amanda, I do have Microsoft Outlook 16.0 Object Library but unfortunately this did not work either - I have a new error pointing to the problem code. It seems Outlook.Application is not a defined type - do I need to create some early-binding to qualify the object?

Thank you!

(ps again I am unable to send screenshots / attachements - will send via email but feel free to post here for the community as well.)
2 years ago
·
#2895
Hi Ryan,

Message received. But sorry that since the code works on our computers, so we could not think of other solutions for you now. :(

About the screenhots, did you click Upload Files under the Attachements box and then select the files that are supported by the forum?

Amanda
2 years ago
·
#2902
Bummer :/ I wonder what it could be - I will try playing around with it some more.

About the files, I did try that. I upload just fine but after checking im not a robot and hitting reply. The page just loads and loads, but never sends. I can reply just fine with no attachments. Amanda, I emailed you the attachments and book.

Will let y'all know if I get this resolved!
Thanks for all the help anyway :)

Ryan
  • Page :
  • 1
There are no replies made for this post yet.