• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code for: Finding rows with Due Date in a column & then send email - SOLVED

Status
Not open for further replies.

inddon

Member
Hello There,

I have a small sized workbook for invoice data entry. The invoices are entered for each month in a separate worksheet. There are a few (20 to 30) invoices every month

I have attached a workbook (Invoice entry.xlsm). It contains the following worksheets:

1. June, July & August : For invoice data entry
2. Setup Email : The email specification are listed (eg. To, Cc, Subject, Body text)
3. Show And Send Email: Displaying due date invoices and then send email. There are listed 2 buttons (Display Due Dates & Send Email)
--------------------------------
(a). Display Due Dates (Button): On pressing the button it should walk through all the worksheets whose 1st 3 characters are 'INV'.
(b). Pick up all the rows in that worksheet where columns 'Email Reminder (Y/N)' & 'Email Send Date' are null or empty.
(c). For each satisfied row (from step b), check if the 'Due Date' is 7 days prior to current date. If it is true then include certain values of that record in worksheet 'Show And Send Email' (eg. Invoice Number, Party Name, Invoice Date, etc.)
4. After picking up the satisfied record, it should include a 'Y' and 'today's date' in columns 'Email Reminder (Y/N)' & 'Email Send Date' respectively.
--------------------------------
5. Repeat step 3 to 4 for next each worksheet whose 1st 3 characters are 'INV'
--------------------------------
6. After the process is completed, the user will go through the records in worksheet 'Show And Send Email' and will click the button 'Send Email'.
7. On pressing, 'Send Email', the basic details of the email should be picked up from worksheet 'Setup Email' (eg. To, Cc, Subject, etc.)
8. The body details should include, the records which were displayed in worksheet 'Show And Send Email'
9. Next time, when the user presses the button it should clear the records in the worksheet 'Show And Send Email'

I tried a lot to get this working, but it looked I entangled myself so much with the code it has become very complex and messier and I deleted it all.

Therefore, as the last resort looking for help towards the forum.

Looking forward to hearing from you.


Thanks & regards
Don
 

Attachments

  • Invoice Entry.xlsm
    28.2 KB · Views: 40
Hi Don,

Excellent write-up of problem description, your workbook, and what your goal was. Thank you! :DD :awesome:

Here's your workbook with desired codes.
 

Attachments

  • Invoice Entry LM.xlsm
    41.5 KB · Views: 108
Hi Don,

Excellent write-up of problem description, your workbook, and what your goal was. Thank you! :DD :awesome:

Here's your workbook with desired codes.


Hi Luke,

It took me days to write the code, not quite a working one. You wrote it in a very short period of time. That was pretty fast.

Thanks a lot. :)

I will try out the workbook and keep you informed.


Regards
Don
 
Hi Don,

Excellent write-up of problem description, your workbook, and what your goal was. Thank you! :DD :awesome:

Here's your workbook with desired codes.


Hi Luke,

Dat werkt helemal uitstekend :). Super bedankt, nog een keer.

What you have done is a very new concept for me (eg. with temp html file, etc), wonderful. I have something new to study now (your code), and then incorporate your code in the real workbook. It should be interesting. Also, it is nice to come across your website as well (building up my knowledge).

In the output section the column 'Sr.No' gets a #VALUE! (try to do C4+1, whereas C4 is the column header) error. Let me try it out to resolve myself (a mini homework)


Regards
Don
 
Hi Don,

If you didn't get it solved yet, the answer would be to replace this line:
Code:
.Range(.Cells(counter, "A"), .Cells(counter, "F")).Copy recSheet.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0)
with this:
Code:
recSheet.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(1, 6).Value = _
                    .Range(.Cells(counter, "A"), .Cells(counter, "F")).Value

Instead of doing a regular copy (which would copy any formulas) it transfers the values. Glad you liked it, and good luck with the full implementation!
 
Hi Luke,

I have implemented your code in the workbook, thanks again.

The copy that is there in the code is for a fixed range. As a newbie, I tried to understand this section of your code but it kept me scratching my head :confused::

'Transfer info
recSheet.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(1, 6).Value = _
.Range(.Cells(counter, "A"), .Cells(counter, "F")).Value


Rather than, hard coding the columns, I was thinking to add in the worksheet 'Setup Email' where the user will list the columns to be copied to the final worksheet (thus making the option of copy more flexible).

1. The user lists which columns it would need to copy in the new worksheet for emailing.

For eg.
Cell values of the row to copy:
--------------------------------
B
D
H

Update Columns after copy
------------------------------
I
J

I would like your advice and assistance :awesome: for the above, and also to build up my knowledge. I have attached the workbook for your reference.

Thanks a lot in advance and look forward to hearing from you.

Regards
Don
 

Attachments

  • Invoice Entry - Display and Send Emails.xlsm
    41 KB · Views: 19
I can explain the code we use now at least...
recSheet.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(1, 6).Value = _
.Range(.Cells(counter, "A"), .Cells(counter, "F")).Value

VB is an object oriented language, meaning that it uses objects as a reference point, and then lets you decide what to do from there (either actions-methods, or jump to another object).

So, working left-to-right. First, we're looking at the recSheet, which is our worksheet. Within that object, we want to find a particular cell, the last cell with data. To find this, we will want to start at bottom of column, and then go up until we find something. In the code, we find this bottom via the Cells objects, which lets us give the row number and column location. For row, we count total number of rows in the sheet. In XL 2003, this was 65536, but in later files it is much larter. Anyway, we are now at the bottom of column C.
Now, to find the last cell with data. This is what End does. It lets you go in any of the 4 directions, until you find either the beginning of data, or the end of current block of data. So, if last cell with data was C10, we are now referring C10.
But, we don't want to paste over our existing data! So, we need to Offset by 1 row. Offset uses 2 arguments, row and column offsets. Negative numbers would be to go up/left, respectively. Now we would be referring to C11.
Finally, we want to actually copy 6 cell's worth of information. So, we Resize the object we are looking at. Again, two arguments, # of rows and # of columns. Now, we are referring to C11:H11. Hurrah!
We tell VB that the value of this array of cells is equal to the value of the array of cells in the Range from our source sheet. That range is determined by being everything from col A of row = counter, to col F of row = counter. If counter = 20, this would be referring to A20:F20.

All together then, our code, in this example, would be saying to make the value of C11:H11 of destWS = A20:F20 of source worksheet. Phew!

=======
All that said, letting user pick and choose which columns to copy or change will require much more coding, as we'll need to individually copy each cell rather than a block, and we'll need to add some type of search capcability to determine which columns to go to. How high of a priority is this?
 
I can explain the code we use now at least...
recSheet.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(1, 6).Value = _
.Range(.Cells(counter, "A"), .Cells(counter, "F")).Value

VB is an object oriented language, meaning that it uses objects as a reference point, and then lets you decide what to do from there (either actions-methods, or jump to another object).

So, working left-to-right. First, we're looking at the recSheet, which is our worksheet. Within that object, we want to find a particular cell, the last cell with data. To find this, we will want to start at bottom of column, and then go up until we find something. In the code, we find this bottom via the Cells objects, which lets us give the row number and column location. For row, we count total number of rows in the sheet. In XL 2003, this was 65536, but in later files it is much larter. Anyway, we are now at the bottom of column C.
Now, to find the last cell with data. This is what End does. It lets you go in any of the 4 directions, until you find either the beginning of data, or the end of current block of data. So, if last cell with data was C10, we are now referring C10.
But, we don't want to paste over our existing data! So, we need to Offset by 1 row. Offset uses 2 arguments, row and column offsets. Negative numbers would be to go up/left, respectively. Now we would be referring to C11.
Finally, we want to actually copy 6 cell's worth of information. So, we Resize the object we are looking at. Again, two arguments, # of rows and # of columns. Now, we are referring to C11:H11. Hurrah!
We tell VB that the value of this array of cells is equal to the value of the array of cells in the Range from our source sheet. That range is determined by being everything from col A of row = counter, to col F of row = counter. If counter = 20, this would be referring to A20:F20.

All together then, our code, in this example, would be saying to make the value of C11:H11 of destWS = A20:F20 of source worksheet. Phew!

=======
All that said, letting user pick and choose which columns to copy or change will require much more coding, as we'll need to individually copy each cell rather than a block, and we'll need to add some type of search capcability to determine which columns to go to. How high of a priority is this?


Hi Mike,

Your style of explanation is simply very good. Thanks a lot for making me understand the copy range code.

The requirement priority is not that high. But, at one point this will have to be done.

For the moment, I will try to change your piece of range copy code to suit the need.

When you are free and can spare some time it would be a great to have your advice and include this functionality.


Thanks again & regards
Don
 
I can explain the code we use now at least...
recSheet.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(1, 6).Value = _
.Range(.Cells(counter, "A"), .Cells(counter, "F")).Value

VB is an object oriented language, meaning that it uses objects as a reference point, and then lets you decide what to do from there (either actions-methods, or jump to another object).

So, working left-to-right. First, we're looking at the recSheet, which is our worksheet. Within that object, we want to find a particular cell, the last cell with data. To find this, we will want to start at bottom of column, and then go up until we find something. In the code, we find this bottom via the Cells objects, which lets us give the row number and column location. For row, we count total number of rows in the sheet. In XL 2003, this was 65536, but in later files it is much larter. Anyway, we are now at the bottom of column C.
Now, to find the last cell with data. This is what End does. It lets you go in any of the 4 directions, until you find either the beginning of data, or the end of current block of data. So, if last cell with data was C10, we are now referring C10.
But, we don't want to paste over our existing data! So, we need to Offset by 1 row. Offset uses 2 arguments, row and column offsets. Negative numbers would be to go up/left, respectively. Now we would be referring to C11.
Finally, we want to actually copy 6 cell's worth of information. So, we Resize the object we are looking at. Again, two arguments, # of rows and # of columns. Now, we are referring to C11:H11. Hurrah!
We tell VB that the value of this array of cells is equal to the value of the array of cells in the Range from our source sheet. That range is determined by being everything from col A of row = counter, to col F of row = counter. If counter = 20, this would be referring to A20:F20.

All together then, our code, in this example, would be saying to make the value of C11:H11 of destWS = A20:F20 of source worksheet. Phew!

=======
All that said, letting user pick and choose which columns to copy or change will require much more coding, as we'll need to individually copy each cell rather than a block, and we'll need to add some type of search capcability to determine which columns to go to. How high of a priority is this?




Hello Luke,

The flexy proposal, where the user chooses which columns to copy and send in email now is final and deliver in a weeks time.

For me as a newbie in VBA, is too difficult. Could you please advice in how this can be done, need your help.

Have a nice weekend and hear from you.

Regards
Don
 
Hi inddon,

Take a look at the attached. I had to make the assumption (for now) that the Due Date is always in column H, but we might be able to add that to the Setup sheet. Also, I noticed that the column letters in the setup sheet, had tons of extra spaces after them. I went through the code, and it's working now. I did take a lot of the debug messages out. Have a good weekend.
 

Attachments

  • Invoice Entry - Choose Columns.xlsm
    44 KB · Views: 27
Hi inddon,

Take a look at the attached. I had to make the assumption (for now) that the Due Date is always in column H, but we might be able to add that to the Setup sheet. Also, I noticed that the column letters in the setup sheet, had tons of extra spaces after them. I went through the code, and it's working now. I did take a lot of the debug messages out. Have a good weekend.


Hello Luke,

Thank you very much for your code. It works perfect. I did incorporate it in my workbook.

Good observation. Yes, in the future there are chances that the user will add more columns in the worksheet, this will move away the 'Due Date' column as well as the 'Email Reminder and Email Send Date columns'.

Will it be possible to:
1. Include the above columns in the setup worksheet. So it can be controlled in the setup sheet, no matter if the positions of these columns change in the working sheet
2. Also, nice to have in the output sheet the sequence numbering and total sum of amounts.

I tried to do the sequence numbering, but it goes wrong.

Look forward for your help.

Thanks & regards
Don
 
Hello Luke,

I noticed the date columns show the format as '3Tue 12-Aug-14'. How to display this as 'DD-Mmm-YYYY'?

Thanks & regards
Don
 
Hi Don,

Several answers.
1. Was able to add another column callout to setup page, defining which column due Date is in
2. Not sure what you mean by sequeucning...is this keeping the numbers from "Sr No" in source sheets, or you want a new sequence generated (1,2,3...)?
2b. Total of which numbers exactly? The Amount column? What happens if Amount column is not copied?

3. For dates...first, check to make sure that the cell's contents are correct. Can you change the cell's number format to make it display correctly? I'm not seeing anything like that in sample you gave, so am unsure how to advise further.

Hopefully you can provide an example of desired output for #2, and some examples for #3.

Thanks.
 

Attachments

  • Invoice Entry - Choose Columns2.xlsm
    44.1 KB · Views: 13
Hi Don,

Several answers.
1. Was able to add another column callout to setup page, defining which column due Date is in
2. Not sure what you mean by sequeucning...is this keeping the numbers from "Sr No" in source sheets, or you want a new sequence generated (1,2,3...)?
2b. Total of which numbers exactly? The Amount column? What happens if Amount column is not copied?

3. For dates...first, check to make sure that the cell's contents are correct. Can you change the cell's number format to make it display correctly? I'm not seeing anything like that in sample you gave, so am unsure how to advise further.

Hopefully you can provide an example of desired output for #2, and some examples for #3.

Thanks.


Hello Luke,

Thank you for your help. The due date column works perfectly fine and now have implemented in the actual workbook.

2.I was looking of having a new sequence generated (1,2,3..) for the records displayed in output worksheet 'Show and send email'.

2b. I guess this will not be applicable as the amounts can be in euros as well as in dollars. Also, you are right, if the amount column is not selected in the output, problem. Will let this go off.

3. For dates: eg. In the worksheet 'Inv-June', the format of all date columns are in 'DD-Mmm-YYYY'. When the row is displayed in the output it is shown in the format 'dd mm yy'. Would be nice, if it preserves the original formats also in the output worksheet.

The people over here were happy to see your work of sending email. :)

Looking forward to hearing from you.

Thanks & regards
Don
 
Sequence numbering has been added, will always be in col C of output sheet. Glad to hear decision about 2b, especially if mixed currencies are a possibility. :eek:

For the dates, took me awhile to figure out what was going on. Looks like when the data gets transferred to HTML in the email, it's just passing the value, and not preserving the format. Annoying. To get around this, I have the code format all the cells in Output sheet as Text, so that the literal text string gets passed to the email. This seems to have done the trick, but let me know if you run into issues. :)

Glad to be of service. :cool:
 

Attachments

  • Invoice Entry - Choose Columns3.xlsm
    42.9 KB · Views: 14
Sequence numbering has been added, will always be in col C of output sheet. Glad to hear decision about 2b, especially if mixed currencies are a possibility. :eek:

For the dates, took me awhile to figure out what was going on. Looks like when the data gets transferred to HTML in the email, it's just passing the value, and not preserving the format. Annoying. To get around this, I have the code format all the cells in Output sheet as Text, so that the literal text string gets passed to the email. This seems to have done the trick, but let me know if you run into issues. :)

Glad to be of service. :cool:


Hello Luke,

Thank you for your code. It works perfect. I tried to implement it in the actual workbook with a bit difficulty as below (attached jpeg file):

I would like the Rec.# to start from column D4. I referred to your earlier post explanation on the code, but ... Tried to play around with variable value 'myoffset' to get the Rec.# to print on D4, not success.

Could you please help and advice on new code.

Thanks a lot in advance, and hopefully close the email post after this, as it will fulfill all its requirement.


Looking forward to hearing from you.


Regards
Don

code below in the actual workbook:

Code:
  'Check if headers need to be setup
  If recSheet.Range("C4").Value = "" Then
  For i = 1 To colList.Count
  recSheet.Range("C4").Value = "Rec. #"
  recSheet.Cells(4, i + myOffset).Value = .Cells(5, colList(i)).Value
  'old code recSheet.Cells(4, i + 2).Value = .Cells(5, colList(i)).Value
  Next i
  End If
   
  'Keep track of how many records, add to Rec # column
  recCount = recCount + 1
  recSheet.Cells(.Rows.Count, myOffset).End(xlUp).Offset(1, 0).Value = recCount

  'Transfer info
  For i = 1 To colList.Count
  recSheet.Cells(.Rows.Count, i + myOffset).End(xlUp).Offset(1, 0).Value = .Cells(counter, colList(i)).Text
  'old code recSheet.Cells(.Rows.Count, i + 2).End(xlUp).Offset(1, 0).Value = .Cells(counter, colList(i)).Value
  recordExists = 1
  Next i
 

Attachments

  • Rec number.jpg
    Rec number.jpg
    90 KB · Views: 4
Oops, looks like I didn't make the whole thing dynamic. First few lines use a hard call-out of column C. Thankfully, easy to fix. Block of code should be changed to:
Code:
'These two lines use variables now, rather than hard call-out
 If recSheet.Cells(4, myOffset).Value = "" Then
  For i = 1 To colList.Count
  recSheet.Cells(4, myOffset).Value = "Rec. #"
  recSheet.Cells(4, i + myOffset).Value = .Cells(5, colList(i)).Value
  'old code recSheet.Cells(4, i + 2).Value = .Cells(5, colList(i)).Value
 Next i
  End If
   
  'Keep track of how many records, add to Rec # column
 recCount = recCount + 1
  recSheet.Cells(.Rows.Count, myOffset).End(xlUp).Offset(1, 0).Value = recCount

  'Transfer info
 For i = 1 To colList.Count
  recSheet.Cells(.Rows.Count, i + myOffset).End(xlUp).Offset(1, 0).Value = .Cells(counter, colList(i)).Text
  'old code recSheet.Cells(.Rows.Count, i + 2).End(xlUp).Offset(1, 0).Value = .Cells(counter, colList(i)).Value
 recordExists = 1
  Next i
 
Oops, looks like I didn't make the whole thing dynamic. First few lines use a hard call-out of column C. Thankfully, easy to fix. Block of code should be changed to:
Code:
'These two lines use variables now, rather than hard call-out
If recSheet.Cells(4, myOffset).Value = "" Then
  For i = 1 To colList.Count
  recSheet.Cells(4, myOffset).Value = "Rec. #"
  recSheet.Cells(4, i + myOffset).Value = .Cells(5, colList(i)).Value
  'old code recSheet.Cells(4, i + 2).Value = .Cells(5, colList(i)).Value
Next i
  End If
 
  'Keep track of how many records, add to Rec # column
recCount = recCount + 1
  recSheet.Cells(.Rows.Count, myOffset).End(xlUp).Offset(1, 0).Value = recCount

  'Transfer info
For i = 1 To colList.Count
  recSheet.Cells(.Rows.Count, i + myOffset).End(xlUp).Offset(1, 0).Value = .Cells(counter, colList(i)).Text
  'old code recSheet.Cells(.Rows.Count, i + 2).End(xlUp).Offset(1, 0).Value = .Cells(counter, colList(i)).Value
recordExists = 1
  Next i



Hello Luke,

Wow!!, that made a lot of difference, thank you. It is now easier :)

Somehow it still leaves a blank column between Rec. # and Invoice Number. Similar to the output in my previous post's attachment.

eg.
Column D4 -> Rec.#
Column F4 -> Invoice Number

Could you please advise?

regards
Don
 
Hmm. I have a feeling a code line may have gotten messed up with this many edits. Try using this file...seems to be working correctly.
I also looked ahead and realized that similar changes need to be made to the "Send Email" macro, so that it looks in the correct place.

The myOffset variable is now listed at the top of the module, so that all macros can read it (also makes it easier to find, ha ha).
 

Attachments

  • Invoice Entry - Choose Columns4.xlsm
    43 KB · Views: 14
Hmm. I have a feeling a code line may have gotten messed up with this many edits. Try using this file...seems to be working correctly.
I also looked ahead and realized that similar changes need to be made to the "Send Email" macro, so that it looks in the correct place.

The myOffset variable is now listed at the top of the module, so that all macros can read it (also makes it easier to find, ha ha).


Hello Luke,

Thank you again for the update. I have updated the workbook with your code.

In the output sheet, it somehow still leaves a blank column between the Rec. # and Invoice Number. It seems I missed something in the process. :confused:

I have attached the file (shorter version for viewing). Could you please review the implemented code.


Many thanks & regards
Don
 

Attachments

  • Email Due Dates.xlsm
    56.9 KB · Views: 18
Line 42 is the problem.
Code:
    'Find list of columns
    i = 5 'Counter to collect values for 'cell values of the row to copy
This controls which row we start looking in to get columns. Your setup page has columns starting in row 6. :eek:
Change above to:
Code:
    'Find list of columns
    i = 6 'Counter to collect values for 'cell values of the row to copy
 
Line 42 is the problem.
Code:
    'Find list of columns
    i = 5 'Counter to collect values for 'cell values of the row to copy
This controls which row we start looking in to get columns. Your setup page has columns starting in row 6. :eek:
Change above to:
Code:
    'Find list of columns
    i = 6 'Counter to collect values for 'cell values of the row to copy


Hello Luke,

Thank you very much. Such a minor correction. It works perfect and now I am at ease :)


Regards
Don
 
Those are my favorite ones. :DD


Hello Luke,

Just a small request with the email output. I tried to do it but in vain.

I am looking to have the output result of the due dates with the following (attached jpeg file for your reference):

1. Enclosed it with thin black border line for the displayed results (from header to last record)
2. For the Header label, have a background color and make it bold


Could you please advise and help me out with it.


Many thanks & regards
Don


Below is the code:



Code:
  If lastRow <= 5 Then GoTo NextSheet 'No records found
  ' start from the row where the record is
  For counter = 6 To lastRow

  'List of our criteria.
  'NOTE: Is column AL always going to be in same place?
  ' .Cells(counter, "K").Value <> ""  is the VPL Invoice Number
  If .Cells(counter, "K").Value <> "" And _
  .Cells(counter, remCol).Value <> "Y" And _
  .Cells(counter, sendCol).Value = "" Then 'And _
.Cells(counter, dueCol).Value <= critDate Then
 
  'These two lines use variables now, rather than hard call-out
  'Check if the header start cell is empty, if yes then go to the IF statement
  If recSheet.Cells(3, myOffset).Value = "" Then
  For i = 1 To colList.Count
  recSheet.Cells(5, myOffset).Value = "Rec. #"
  recSheet.Cells(5, i + myOffset).Value = .Cells(5, colList(i)).Value
  'old code recSheet.Cells(4, i + 2).Value = .Cells(5, colList(i)).Value
  Next i
  End If
 
  'Keep track of how many records, add to Rec # column
  recCount = recCount + 1
  recSheet.Cells(.Rows.Count, myOffset).End(xlUp).Offset(1, 0).Value = recCount
 
  'Transfer info
  For i = 1 To colList.Count
  recSheet.Cells(.Rows.Count, i + myOffset).End(xlUp).Offset(1, 0).Value = .Cells(counter, colList(i)).Text
  'old code recSheet.Cells(.Rows.Count, i + 2).End(xlUp).Offset(1, 0).Value = .Cells(counter, colList(i)).Value
 
  Next i
 
  'Mark cells
  .Cells(counter, remCol).Value = "Y"
  .Cells(counter, sendCol).Value = Date
  End If
  Next counter
  End With
 

Attachments

  • Due Date Email Output.jpg
    Due Date Email Output.jpg
    122 KB · Views: 11
Added a bit of code to the end, commented with info showing which section is formatting which parts. :)
 

Attachments

  • Email Due Dates 20140918.xlsm
    57.5 KB · Views: 47
Status
Not open for further replies.
Back
Top