**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

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