Auto email on opening an Excel File

[buvanamali]

Hi
I am trying to create macro which goes through this two sheets and when the due date / expiry date is approaching, my idea is to create auto email before 10 days to the addressee mentioned in the column “O”. With the following macro, I am able to complete the task for only one sheet which is active sheet when I am opening the file. Can anyone help to accomplish my task so that it will go through all the sheets by name and generate emails to the addressee based on the due date. The structure of sheet shall be the same for all sheets.

Stay Safe. Work Safe. Keep Social Distancing. Sanitize your hands as much as time possible.

Thanks in advance.

Buvanamali

[Mumps1]

Try:

Please Login or Register  to view this content.

[buvanamali]

Hi
Your Code works fine. I have modified some changes in the code, so that the template shall be more user friendly. I have inserted a drop down with 90 days, 60 days and so on . . . for reminder mail to be sent on. Also I had modified the email address to be on single row instead of every row.
No other changes I had made in the code.

While opening the Excel, it generates mails correctly for Deposits Sheet and it generates mail for irrelevant row ie. first row only instead of second and third row.

Also I request you to insert a line space between salutation and body of the email.

Can you please help me in resolving the issue.

Stay Safe. Work Safe. Keep Social Distancing. Sanitize your hands as much as time possible.

Thanks in advance.

Buvanamali

[Mumps1]

it generates mail for irrelevant row ie. first row only instead of second and third row.

I'm not sure what you mean by this. Could you please explain in detail, using a examples from your data referring to specific cells, rows and columns.

[bakerman2]

In a With...End With you need to use dots before Cells(row,column)

Please Login or Register  to view this content.

For extra space.

Please Login or Register  to view this content.

[buvanamali]

Hi
It generates mail correctly for Deposits sheet (Sheet1) ie. two mails (for row 4 and row 8) which is due within 15 days, whereas for Insurance sheet (Sheet2), instead of row 5 and row 6 which is actually due within 15 days, it generates mail only for row 4(which is irrelevant). The sample file is already attached with post #3.
Please help me in resolving the issue.

Stay Safe. Work Safe. Keep Social Distancing. Sanitize your hands as much as time possible.

Thanks in advance.

Buvanamali

[Mumps1]

Try:

Please Login or Register  to view this content.

[bakerman2]

No problem here. Generates 4 mails like intended.

[buvanamali]

Hi

Thanks a lot for both Mr Mumps and Mr bakerman2 - your code works perfectly. Thanks once again for your efforts and help.

Stay Safe. Work Safe. Keep Social Distancing. Sanitize your hands as much as time possible.

Buvanamali

[Mumps1]

Glad we could help.