Automated Access Reports E-mailed with Outlook in Remote Desktop E

[Brad]

Automated Access 2007 Reports E-mailed with Outlook in Remote Desktop
Environment (aka Terminal Services )

I have set up an Access 2007 application that automatically reads data from
a SQL Server database, generates several reports, and then pushes these
reports out to several files in PDF format. After the report files are
generated, I use VBA code in Access to fire up Outlook to e-mail the reports
as attachments to several department managers.

This system is initiated by the Windows Scheduler at 4:00 AM every day and
is run with no human intervention. In order to avoid the need for human
intervention in the Outlook part, I employ a technique to bypass the normal
Outlook warning messages via the approach shown at this URL.

http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-without-Security-Warning

This approach works nicely when I am logged on, or when all of the
components are on my PC. However, I recently ran into a problem when I
tried to implement this system in a "Remote Desktop Environment".

When working in this environment, everything works fine when I am logged in.
However, if I log off, the Outlook portion will not work. I am new to the
Remote Desktop Environment, but I believe that the root issue that I am
running into is that when I am logged off, the proper connection between
Access 2007 and Outlook cannot be established, thus no emails are sent. The
generation of the reports still works, however. Just the E-mail portion
fails.

I am curious if others who work in a Remote Desktop Environment have run
into this issue.

I cannot keep the application on a stand-alone PC. It needs to be moved to
the Remote Desktop Environment.

I cannot simply stay logged in the Remote Desktop Environment 24X7.

I need to find a way to send e-mails from Access 2007 that require no human
intervention while running in the Remote Desktop Environment.

I have read a little about Blat, but I have not experimented with it yet. I
would like to avoid introducing another component and would prefer to use
Outlook if possible.

If anyone has experience with this issue, I would really appreciate their
insights.

Thanks,
Brad

 

[Mark Andrews]

As a guess on the server you are running the scheduled task, the server is
logged on with a particular userid
otherwise Access would not work (since Access runs in the foreground). Does
that userid have Outlook (an Outlook profile
etc...). Stand at the server and try opening Access and opening Outlook.

I would just throw an SMTP dll into the mix and replace your tons of code to
use Outlook with less code and simplify your life but that's just me. You
can still send mail through your smtp server.

When you login you are now running Access in your particular workspace and
you have Microsoft Access and Microsoft Outlook
so everything works fine. Emails are sent by the accounts you have setup in
Outlook (the FROM address). On the server a different userid
is being used (when the windows task starts up your access database).

That's a guess, I could be off since I don't use Terminal Services that
much.
Mark

 

[Brad]

Mark,

Thanks for the help. The Remote Desktop environment is new to me so I am
still in the learning phase. I use the Windows Scheduler to fire up Access.
I specify the user ID and password in the scheduler entry. The Access part
of the system works fine even when I am logged off the system. The Outlook
portion fails when I am logged off so I need to figure out how to pass the
user-id and password to Outlook also.

Or, use a non-Outlook approach for the e-mail portion... I am not quite what
you mean by "throw an SMTP dll into the mix"


Thanks again for your assistance.

Brad

 

[Mark Andrews]

Brad,

Try a test and use your login in the windows scheduler portion to see if by
chance it will cause everything to work.

There are lots of products designed for sending email. Usually they are one
DLL file that you need to register and set a reference to.
http://www.google.com/search?hl=en&...p+component&aq=f&aqi=g2g-m8&aql=&oq=&gs_rfai=


Here's a link to the Ostrosoft one that I use:
http://www.ostrosoft.com/OSSMTP6.asp

Download the VBA example or
download my email product
http://www.rptsoftware.com/products/email/
or download my donation software
http://www.donationmanagementsoftware.com/
to see it in action.

There are a number of companies that make these SMTP products. Lots of them
are used in high volume email situations on web sites etc....

You just need to specify a few settings and then it's a few lines of code to
send an email. So instead of relying on the entire Outlook product to be
installed you just rely on ONE extra file that you can control. Ostrosoft's
even has some things you can hook into to show progress and errors that
might happen while the email is being sent. Takes a lot of the headaches
out of the mix.

My two cents,
Mark

 

[Brad]

Mark,

Thanks for the info. I really appreciate your help/advice.

I plan to dig into Outlook alternatives.

Brad