Macro works, but not when I run it (error 91)

[johnspartan117]

Hello guys"

I'm using this code below for an automatic email to be send 7 days prior to the due date (ron de bruin's one) and it's working when i change the dates, when i use the sheet 7 days prior, but if I have that sheet open and try running the macro using "f9" or clicking "run" it gets me an error 91 on the following line and i'd like some help fixing it, since i've looked up on all the internet and couldn't get it to run properly. Also, if i erase this line giving me an error, the macro runs:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The mail code module is this one:

Please Login or Register  to view this content.

[6StringJazzer]

First, I do not understand how your first sub compiles with Option Explicit because Formula Cell is undeclared.

Second, I see a problem, and I will suggest a solution, but I cannot test this without your file and some sample data.

The variable FormulaCell is undeclared, but it is assigned in your first Sub. It is used in your second sub, but because it is undeclared your second sub doesn't know about it. When your second sub encounters it, it has not been set, but you are trying to use it as a Range, and so you get Error 91, which means "object variable not set."

To fix this, pass a parameter to the second sub.

Please Login or Register  to view this content.

[johnspartan117]

Thanks very much, specially for the quick responde! Your suggestions did stop error 91 from coming, but now, the macro list is empty, so i'm guessing something got redefined there! Was that supposed to happen?