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, 29 June 2022
  13 Replies
  5.2K Visits
0
Votes
Undo
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 = ""
.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!
1 year ago
·
#2902
0
Votes
Undo
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
1 year ago
·
#2895
0
Votes
Undo
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
1 year ago
·
#2887
0
Votes
Undo
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.)
1 year ago
·
#2868
0
Votes
Undo
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
1 year ago
·
#2867
0
Votes
Undo
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
1 year ago
·
#2864
0
Votes
Undo
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
1 year ago
·
#2861
0
Votes
Undo
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?


Hello,

Why not save it as the zip or rar?:)
1 year ago
·
#2859
0
Votes
Undo
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
1 year ago
·
#2858
0
Votes
Undo
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?
1 year ago
·
#2847
0
Votes
Undo
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. :)
1 year ago
·
#2844
0
Votes
Undo
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
1 year ago
·
#2843
0
Votes
Undo
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).
1 year ago
·
#2838
0
Votes
Undo
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
  • Page :
  • 1
There are no replies made for this post yet.