SendObject macro sends twice!

[Ruth Isaacs]

Hello All

This problem has baffled me for too long now, and I hope someone out there
can explain to me what's going on!
I have a simple macro, with a single command line that has a SendObject
action, with parameters as follows:

Object type: query
Object name: qry new staff details
Output format: Microsoft excel (*.xls)
To: =DLookUp("[practices]!","[practices]","[practices]![prac
name]=[Forms]![frm x main]![prac name]")
Cc: (blank)
Bcc: (blank)
Subject: Staff details
Message text: Please find attached an Excel spreadsheet for you to enter
your Staff details.
Edit message: No
Template file: (blank)

The macro runs OK ... except that every time, TWO identical emails are
sent!!
They arrive simutaneously in the recipients Inbox, with exactly the same
date/time received.

How can this be?
There is definitely only one command line in the macro (I've checked this
many times!).
I have tried setting the Edit message parameter to Yes, but although I was
only offered the opportunity of editing the message once, it made no
difference - TWO emails arrived.

Is this a bug? I hope someone can save my sanity, because this is driving me
nuts.

Thanks in advance.
Les

 

[Ken Snell \(MVP\)]

My first guess... you are calling the macro in more than one event, and that
is the cause for two emails. Take a careful look at the form in design view
and look at the Event properties for all the form's and control's events.

 

[Steve Schapel]

.... for example, the macro might be assigned on both the Click *and*
DblClick events of a command button.

 

[Ruth Isaacs]

Hello Steve and Ken

Many thanks for your suggestion ... but unfortunately it's not that. I get
the same dehaviour when runing the macro directly frm the main database
window. In fact I've been looking at the macro in design view, then clicking
the exclamation mark icon to run it: same result.

What seems really odd is that, with the Edit Message parameter set to Yes, I
am only presented with the email dialogue window (to edit the message) once,
not twice. This would suggest to me that the command is only being fired
once ... yet two emails arrive!!

I had wondered whether there was in fact a problem with my outlook express
setup, but no other emails are duplicated - not even the one that is
generated by vba code from within a module i the same mdb. The problem only
occurs with this macro.

I would be very grateful if you could suggest any other cause.

Many thanks
Les

 

[Ken Snell \(MVP\)]

Verify that this function call is not returning two names separated by a
comma:

DLookUp("[practices]!","[practices]","[practices]![pracname]=[Forms]![frm
x main]![prac name]")

If, for example, the above returns
Smith, Doris

Then the email software will assume aliases are being used and will look up
Smith and Doris and substitute the email addresses it finds for them. If you
have only one email address with Smith in it, and one with Doris in it, then
you'll get two of the same email address in the To box.

 

[Leslie Isaacs]

Hello Ken

Thanks for that ... but I don't think that's it: every value in the (e-mail address removed)

 

[Ken Snell \(MVP\)]

Only other thing that comes to mind is that email field with the address in
it might have more than one line of data (second line wouldn't be visible
when looking at table or query in default "row" height). It's possible that
someone might have mistakenly pressed Enter key before entering the email
address, so it would go on a "second" line in the field, then someone later
looked and saw that the email field was "empty" and added the email address
again.

Carefully look through your data for this type of error.

Also, the DLookup function should be changed to this (no need for table name
in the first and third arguments, and the ! operator is not the correct one
for table.field references anyway):

=DLookUp("(e-mail address removed)

 

[Ruth Isaacs]

Hello Ken

Many thanks for your further suggestion ... but it's not that!!
I replaced the Dlookup function in the macro with an actual email address:
(e-mail address removed)
.... and still got the same result - two emails arrived!
I've tried it with 2 other email addresses, same result.
The 3 email addresses I've been testing this with are all mine so can see
the result straight away.
I have tried sending emails to these 3 addresses directly from outlook
express (i.e. not using the macro), and all is fine - only one email
arrives: so it's definitely something to do with the access macro.
I realise I may be close to exhausting your supply of suggestions, but if
you can think of anything else - or if you like a challenge! - please do let
me know if there is anything else I can try.

Many thanks once again.
Les

Only other thing that comes to mind is that email field with the address in
it might have more than one line of data (second line wouldn't be visible
when looking at table or query in default "row" height). It's possible that
someone might have mistakenly pressed Enter key before entering the email
address, so it would go on a "second" line in the field, then someone later
looked and saw that the email field was "empty" and added the email address
again.

Carefully look through your data for this type of error.

Also, the DLookup function should be changed to this (no need for table name
in the first and third arguments, and the ! operator is not the correct one
for table.field references anyway):

=DLookUp("(e-mail address removed)

 

[Steve Schapel]

Les,

It is about here that I would do this...
- make a new mdb file
- do File|Get External Data|Import, and get all of the objects from
the existing database *except* the misbehaving macro
- Compact/Repair
- re-create the macro
- try again

If it now works correctly, scratch your head and sigh.
If it still does the same as before, scratch your head, sigh, and hope
Ken has another brainwave.

 

[Ken Snell \(MVP\)]

I concur with Steve's suggestions.

Let's also do an experiment to identify if the macro is running twice or if
the problem is something else. Add the following action to your macro *just
before* the SendObject action:
Action: MsgBox
Message: "Macro is running."

Do what you normally do to send the email. How many message boxes do you
get?

--

Ken Snell
<MS ACCESS MVP>

Hello Ken

Many thanks for your further suggestion ... but it's not that!!
I replaced the Dlookup function in the macro with an actual email address:
(e-mail address removed)
... and still got the same result - two emails arrived!
I've tried it with 2 other email addresses, same result.
The 3 email addresses I've been testing this with are all mine so can see
the result straight away.
I have tried sending emails to these 3 addresses directly from outlook
express (i.e. not using the macro), and all is fine - only one email
arrives: so it's definitely something to do with the access macro.
I realise I may be close to exhausting your supply of suggestions, but if
you can think of anything else - or if you like a challenge! - please do
let
me know if there is anything else I can try.

Many thanks once again.
Les

Only other thing that comes to mind is that email field with the address in
it might have more than one line of data (second line wouldn't be visible
when looking at table or query in default "row" height). It's possible that
someone might have mistakenly pressed Enter key before entering the email
address, so it would go on a "second" line in the field, then someone later
looked and saw that the email field was "empty" and added the email address
again.

Carefully look through your data for this type of error.

Also, the DLookup function should be changed to this (no need for table name
in the first and third arguments, and the ! operator is not the correct one
for table.field references anyway):

=DLookUp("(e-mail address removed)

 

[Ruth Isaacs]

Hello Ken and Steve

I am very grateful that both of you are still with this!

I have tried both of your suggestions:
Creating the new mdb and importing objects (except the problem macro), then
compacting/repairing then re-creating the macro, gave the same result - 2
emails.
Adding the MsgBox command before the SendObject command resulted in the
message being displayed once, not twice.
You may recall that I had also tried setting the Edit Message parameter to
Yes, and in that case I had also found that the email dialogue window only
came up once. So it does look very much like the email is only being sent
once - as far as access is concerned. But as far as outlook express is
concerned, two copies arrive (with identical 'sent' times).

I keep wondering whether the problem is with outlook express: but it only
occurs with emails sent by this access macro.

I hope you like a puzzle, because this one is 'doing my head in' as they say
round these parts (Liverpool, England)!

Thanks for your continued help once again.
Les

I concur with Steve's suggestions.

Let's also do an experiment to identify if the macro is running twice or if
the problem is something else. Add the following action to your macro *just
before* the SendObject action:
Action: MsgBox
Message: "Macro is running."

Do what you normally do to send the email. How many message boxes do you
get?

--

Ken Snell
<MS ACCESS MVP>

Hello Ken

Many thanks for your further suggestion ... but it's not that!!
I replaced the Dlookup function in the macro with an actual email address:
(e-mail address removed)
... and still got the same result - two emails arrived!
I've tried it with 2 other email addresses, same result.
The 3 email addresses I've been testing this with are all mine so can see
the result straight away.
I have tried sending emails to these 3 addresses directly from outlook
express (i.e. not using the macro), and all is fine - only one email
arrives: so it's definitely something to do with the access macro.
I realise I may be close to exhausting your supply of suggestions, but if
you can think of anything else - or if you like a challenge! - please do
let
me know if there is anything else I can try.

Many thanks once again.
Les

Only other thing that comes to mind is that email field with the

address
in

it might have more than one line of data (second line wouldn't be visible
when looking at table or query in default "row" height). It's possible that
someone might have mistakenly pressed Enter key before entering the email
address, so it would go on a "second" line in the field, then someone later
looked and saw that the email field was "empty" and added the email address
again.

Carefully look through your data for this type of error.

Also, the DLookup function should be changed to this (no need for table name
in the first and third arguments, and the ! operator is not the correct one
for table.field references anyway):

=DLookUp("(e-mail address removed)

 

[Steve Schapel]

Les,

Do you have access to another computer, where you could try running your
database and see if the same thing happens?

 

[Ruth Isaacs]

Steve

Good idea!

I have now copied the mdb onto the other PC, ran the macro ... and just one
email arrived!
So there is something about the PC that is causing the problem.
I have tried changing the macro to send other objects (reports), and I've
had it run without sending an object (just sending the message text): 2
emails always arrive.

At least we seem to have isolated the problem to the one PC - but what can
it be on that PC that is doing this?

Can you think of anything else I can do to further isolate where the proble
lies?

Many thanks yet again.
Les

 

[Ken Snell \(MVP\)]

My thought is that some setting in your email program (did you say you're
using Outlook Express?) is causing two messages to be sent, but personally I
don't know what setting that might be.

Would it be correct to assume that the double emails occur only when you use
the macro -- that you don't get two emails if you create and send an email
in OE directly?

 

[Leslie Isaacs]

Ken

Yes, that's absolutely correct.
That's why I keep going back to the idea that it is access-related: the
problem only occurs with the macro - on this PC.
What else can I say?
Les

 

[Ken Snell \(MVP\)]

I would try running a Repair on your Office installation on that PC.

 

[Leslie Isaacs]

Ken
OK - I will do that.
Would it be from Control Panel>Add/remove programmes, or is there a better
way?
Les

 

[Ken Snell \(MVP\)]

Ken
OK - I will do that.
Would it be from Control Panel>Add/remove programmes

That is the way to do it, yes.