Question description:
This user has given permission to use the problem statement for this
blog.
I have problem regarding formulas for finding sum please help
Solved by K. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
02/12/2017 - 06:16
Hello there, welcome to Got It Pro
Excelchat Expert
02/12/2017 - 06:17
Hello, are you there?
User
02/12/2017 - 06:19
Sir I have a problem
Excelchat Expert
02/12/2017 - 06:19
Yes, how can I help you?
User
02/12/2017 - 06:19
It is regarding a sheet file
User
02/12/2017 - 06:19
I have been trying to contact on this portal for long
Excelchat Expert
02/12/2017 - 06:20
I am available to help you
Excelchat Expert
02/12/2017 - 06:20
What is your problem
User
02/12/2017 - 06:21
This is teh code
User
02/12/2017 - 06:21
Can you please explain me this
Excelchat Expert
02/12/2017 - 06:22
Do you have a specific part you want explained or everything?
User
02/12/2017 - 06:23
Everything
Excelchat Expert
02/12/2017 - 06:24
OK, next time please indicate clearly the problem you need assistance with in description the problem. It helps the experts prepare for what help is required.
Excelchat Expert
02/12/2017 - 06:24
I will begin the explanation
Excelchat Expert
02/12/2017 - 06:25
1. The code is used for sending emails from Excel
User
02/12/2017 - 06:25
Yes
Excelchat Expert
02/12/2017 - 06:25
2. Set OutApp = CreateObject("Outlook.Application")
Excelchat Expert
02/12/2017 - 06:26
Creates an Outlook object. You need this to access Outlook functions
Excelchat Expert
02/12/2017 - 06:27
3. OutApp.Session.Logon
Excelchat Expert
02/12/2017 - 06:27
Login into outlook
Excelchat Expert
02/12/2017 - 06:28
sSendTo = "upnejaakshat96@gmail.com"
sSendCC = ""
sSendBCC = ""
sSubject = "Due date reached"
Excelchat Expert
02/12/2017 - 06:28
That part should be clear, you are setting the message parameters
User
02/12/2017 - 06:28
yes it is
Excelchat Expert
02/12/2017 - 06:29
4. lLastRow = Cells(Rows.Count, 3).End(xlUp).Row
Excelchat Expert
02/12/2017 - 06:29
In the excel sheet you want to get the last row in column 3 (C)
Excelchat Expert
02/12/2017 - 06:31
The next thing is then that you loop through from row 2 to the last row and send emails according to the conditions of the cells in the corresponding rows
Excelchat Expert
02/12/2017 - 06:32
For lRow = 2 To lLastRow
Excelchat Expert
02/12/2017 - 06:32
.....
Excelchat Expert
02/12/2017 - 06:32
next lRow
Excelchat Expert
02/12/2017 - 06:32
I shall now explain the code in between the For loop
Excelchat Expert
02/12/2017 - 06:32
If Cells(lRow, 10) <> "S" Then
User
02/12/2017 - 06:33
Okay
Excelchat Expert
02/12/2017 - 06:33
Here you want to continue processing if teh value in cell (lRow, 10) is not equal to "S"
User
02/12/2017 - 06:34
The*
Excelchat Expert
02/12/2017 - 06:34
that is the row corresponding to lRow and column 10 (J)
Excelchat Expert
02/12/2017 - 06:34
should not have an S
User
02/12/2017 - 06:34
Okay
User
02/12/2017 - 06:34
What if it is S
Excelchat Expert
02/12/2017 - 06:34
If Cells(lRow, 5) <= Date Then
Excelchat Expert
02/12/2017 - 06:35
If it is "S" then no email is sent
User
02/12/2017 - 06:35
Okay how can I change the ro values then
Excelchat Expert
02/12/2017 - 06:35
It means the email had already been sent
User
02/12/2017 - 06:35
row*
Excelchat Expert
02/12/2017 - 06:35
what do need to change exactly?
User
02/12/2017 - 06:36
I would like to add the other row properties as well
User
02/12/2017 - 06:36
but it doesn't show the same thing
Excelchat Expert
02/12/2017 - 06:36
can you share the file on google drive or drop box?
User
02/12/2017 - 06:37
Okay
User
02/12/2017 - 06:40
https://www.dropbox.com/s/1psyvjy4w9bm06x/DRIVERS%20%20LIST%20mac.xlsm?dl=0
Excelchat Expert
02/12/2017 - 06:41
OK, I got the file
User
02/12/2017 - 06:41
Okay
User
02/12/2017 - 06:41
Please help for the same
User
02/12/2017 - 06:41
I haven't added the code to it yet. Please help so that I can send email to myself for the same
User
02/12/2017 - 06:42
And have the name of the person and it's license number along
Excelchat Expert
02/12/2017 - 06:44
Explain briefly what you want to do?
User
02/12/2017 - 06:45
I would like to send myself an automated mail for the approaching due dates with the name of the client and the license number.
Excelchat Expert
02/12/2017 - 06:46
So if in column "M" there is "Send Reminder", then you need to send the message?
User
02/12/2017 - 06:46
No that column is not a requirement
User
02/12/2017 - 06:46
The message can be from the body as well
Excelchat Expert
02/12/2017 - 06:47
=IF(G9<TODAY() +3,"Send Reminder","")
Excelchat Expert
02/12/2017 - 06:47
This is the condition you need to test to send an email or not?
User
02/12/2017 - 06:49
Yes
User
02/12/2017 - 06:49
Can you please do a code for the same or is the code that I have sent working fine?
Excelchat Expert
02/12/2017 - 06:52
I prefer to redo the code, I propose that , I will go through column G and check using a similar IF condition as above to determine if I need to send a reminder. For those whose licence is about to expire, I will create the list to show in the email of those Names whose licence is about to expire
User
02/12/2017 - 06:52
That would be really kind of you. Please help regarding this
User
02/12/2017 - 06:53
I'm unable to do so for a long tim e
User
02/12/2017 - 06:53
time*
Excelchat Expert
02/12/2017 - 06:53
please wait
User
02/12/2017 - 06:53
This is the tenth time of sending this code on your chat
User
02/12/2017 - 06:53
Sure
Excelchat Expert
02/12/2017 - 07:12
uploading
Excelchat Expert
02/12/2017 - 07:13
https://drive.google.com/file/d/1Chxr5HzMvcFtjJrvjkqmWcHUjGnweCMP/view?usp=sharing
User
02/12/2017 - 07:13
Okay
Excelchat Expert
02/12/2017 - 07:13
Change the .To in the code to your email address
Excelchat Expert
02/12/2017 - 07:13
Which Excel version are you using?
Excelchat Expert
02/12/2017 - 07:13
You need to make sure have Outlook reference in your VBA
User
02/12/2017 - 07:14
I do
Excelchat Expert
02/12/2017 - 07:14
Tools->References
Excelchat Expert
02/12/2017 - 07:14
OK
User
02/12/2017 - 07:14
Using a 2016 versio
Excelchat Expert
02/12/2017 - 07:14
I am using 2016 also, please check , there is not session extension this time around. If you have a problem please repost the question
User
02/12/2017 - 07:15
Okay i would repost the request
Excelchat Expert
02/12/2017 - 07:15
Only 2 extensions are available
Excelchat Expert
02/12/2017 - 07:15
OK
Excelchat Expert
02/12/2017 - 07:15
I will try to pick it up if you repost
User
02/12/2017 - 07:15
Okay will repost asap
Excelchat Expert
02/12/2017 - 07:15
https://drive.google.com/file/d/1Chxr5HzMvcFtjJrvjkqmWcHUjGnweCMP/view?usp=sharing
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
