ZDF broadcasted an interesting documentary on Rommel, which can be seen here in its entirety. While Rommel publicly displayed the optimism that was required to upkeep the morale of his troops, he expressed himself very differently in private. His adjutant Winrich Behr (at about 24:45 in the documentary) relays Rommel’s sentiment as follows:
"Er war ja kein komplizierter Philosoph oder Denker sondern er sagte ganz einfach: ‘Das schaffen wir doch nicht mehr, und stellen Sie sich doch mal vor, und die Leute sagen das und das, nun sehen Sie mal hier, da is eine Infanteriedivision eingesetzt und die haben kaum Geschütze, und wir haben auf zwei kilometer, haben wir eine Batterie stehen.’ "
which roughly translates as:
"He wasn’t a complicated philosopher or thinker, but he quite simply said: ‘We’re just not going to make it, and just imagine, people are saying all kind of things, but look, an infantry division has just been deployed and they hardly have artillery support, and for every two kilometers, we have a single battery.’ "
That was before D-Day, when he was still preparing for the upcoming invasion.
(edit to update the translation)