Bulk email from Access/VBA

[Guest]

We have an Access runtime application which is widely distributed around the
globe.

Our application provides the ability for our clients to bulk email their
customers. Customers register with and authorise our clients to email them,
so please do not flame me as a spammer.

Our clients are small businesses who use diverse email programs, therefore
it is not appropriate to automate Outlook for email.

To date, our application has used code originally written by Dev Ashish
which passes a mailto URL to ShellExecute. The mailto syntax includes a
subject and an address list.

The mailto URL length is limited to 2083 characters ... typically around
100-200 email addresses. If a mailto URL is created with > 2083 characters,
ShellExecute fails.

We have tried passing an internet shortcut with a mailto URL to
ShellExecute, but ultimately this fails also for the same reason: the mailto
URL cannot exceed 2083
characters.

We have considered creating multiple emails for large mailouts, but this is
an untidy workaround.

Does anyone have a solution for creating bulk email from within Access which
is email client independent?

 

[TC]

Probably the best solution would be to find a custom SMTP component
that is usable from VBA.

SMTP or "Simple Mail Tranfer Protocol" is what is the protocol that you
have to use, to send email from a client program, such as yours, to an
ISP mail server. There are quite a few of these components around,
you'd find them easily with a google search. But it is not so easy to
know how well each one would work, without actually trying it. For
example, some of them use 'With Events' for error management. But that
requires a reference set to the component. My rule is, "no references
to external components", so I can not use those ones.

There are free command-line mailers such as Blat. You'd find that
easily with a search. Tons of people use it, but personally I can't see
how! The error information is absolutely terrible. Error #1 means the
connection failed or the email address is malformed or your bother's
hat is on the wrong peg. Error #2 means the server rejected the
connection or the specified attachment file does not exist or your
second uncle's name is Jones, and so on!! Heven knows what ero
messages you could sensibly give to the user; perhaps: "Erp, it didn't
work!".

Finally, you'll find various VB code modules on the web. These talk
SMTP directly to the win32 Winsock API. But some of them that I have
looked at personally, are absolutely terrible. They show no real
understanding of SMTP, or of the underlying transport mechanism,
TCP/IP. Unless you're expert in both of those, it would be almost
impossible to get some of that code to work /reliably/ - /always/ -
instead of just working by chance, most of the time.

So I'd try for a commercial component, if I were you.

HTH,
TC [MVP Access]

 

[Steve Schapel]

John,

http://www.fmsinc.com/products/Emailer/index.asp is excellent.

 

[Guest]

Thanks for the comprehensive response TC. I have not had good experience with
add-on components. Many are fne with standard/local installations,but when
the app has to run on machines you have no control over, in other parts of
the world, things start to get a bit complicated.

In the meantime, I'll keep my fingers crossed for that few lines of code
that overcomes the 2083 character limit.

John

 

[Guest]

Thanks for the reply Steve. I had a look at the FMS email product some time
ago. I know FMS has a good reputation. Nonetheless, my experience
distributing runtime versions of add-on products has not been good.

I'll try to hold out for a VBA solution for the time being.

John

 

[Rick Brandt]

We have an Access runtime application which is widely distributed
around the globe.

Our application provides the ability for our clients to bulk email
their customers. Customers register with and authorise our clients to
email them, so please do not flame me as a spammer.

Our clients are small businesses who use diverse email programs,
therefore it is not appropriate to automate Outlook for email.

To date, our application has used code originally written by Dev
Ashish which passes a mailto URL to ShellExecute. The mailto syntax
includes a subject and an address list.

The mailto URL length is limited to 2083 characters ... typically
around 100-200 email addresses. If a mailto URL is created with >
2083 characters, ShellExecute fails.

We have tried passing an internet shortcut with a mailto URL to
ShellExecute, but ultimately this fails also for the same reason: the
mailto URL cannot exceed 2083
characters.

We have considered creating multiple emails for large mailouts, but
this is an untidy workaround.

Does anyone have a solution for creating bulk email from within
Access which is email client independent?

If users will always have Windows 2000 or XP then I would look at using CDOSys.
It's built into those systems, works well, and is fairly easy to automate from
Access.

 

[Guest]

Thanks Rick. CDO looks interesting.

Since reading your email, I have explored the CDO object model and managed
to create and send an email. However, I have been unable to make the email
visible prior to sending. I need to give users the opportunity to review/edit
the email before it is sent, but cannot find an Open method of the message
object or a visible property. Any further clues?


John

 

[TC]

I need to give users the opportunity to review/edit the email before it is sent

Hmm, would have been nice to know that to start with!

So none of the custom email components will help, unless they have
their own UI.

TC [MVP Access]

 

[Rick Brandt]

Thanks Rick. CDO looks interesting.

Since reading your email, I have explored the CDO object model and
managed to create and send an email. However, I have been unable to
make the email visible prior to sending. I need to give users the
opportunity to review/edit the email before it is sent, but cannot
find an Open method of the message object or a visible property. Any
further clues?

Well then you might be stuck with using the Email client that the user has. If
that is not consistent then you have a problem. CDO (AFAIK) has no method for
showing the message prior to sending.

You could of course create your own form to display the content of the outgoing
Email and let the user edit it prior to sending. The problem with this is
"feature creep". Once you show the user the message, then they will want to see
the attachments (if there are any) and that is difficult to mimic in an Access
form. Then they will want to be able to use their address book to enter
recipients. Before you know it you are attempting to create an entire EMail
client.

If you can control those demands and limit thing to just displaying the message
body then it would not be too difficult.

 

[Guest]

I'm sorry I didn't make tha clear TC. When you Shell a maito URL, that
invokes the default email application. I'm happy with that as it provides all
the functionality our clients expect. I'm just asking for a way around the
2083 character limitation on the URL.

Thanks for your help.

John

I need to give users the opportunity to review/edit the email before it is sent

Hmm, would have been nice to know that to start with!

So none of the custom email components will help, unless they have
their own UI.

TC [MVP Access]

 

[Guest]

That's why we use mailto, as it invokes the default email application ...
saving us a lot of work!

Our latest attempt is
DoCmd.SendObject acSendNoObject, , acFormatHTML, , , strBCC

This seems to be able to handle more than 2083 characters in the BCC list.
However the email client appears to have the drawback of being "modal" (in
Access speak), relative to the instance of our app which invoked it, that is
.... we cannot return control to the instance of our app which instantiated
the email until the email is closed.

Our app supports multiple instances, so this isn't a disaster. But it would
be nice if it didn't happen.

Are you aware of any other issues using SendObject that might get in the way
with bulk email?

 

[Steve Schapel]

John,

There may be something here of interest to you...
http://www.granite.ab.ca/access/email.htm

 

[Steve Schapel]

Fair enough, John. I have one application running in 23 customers'
sites usign the FMS control, without a hitch.

 

[Guest]

Thanks Steve. Lots of stuff there to keep me busy <G>

John