Emailing from Access application - inconsistent behaviour with SendObject

[Rob Parker]

Apologies for the crosspost, but I'm not sure where this will get the best
response (and also not sure of which Outlook group to post to, so I've
chosen the top-level one). Application is Access 2003/Outlook 2003, running
on Win XP Pro (latest SP - corporate network environment).

I have an Access application which uses DoCmd.SendObject to email reports as
..snp files. Addressees are selected (indirectly) from a listbox for
particular reports, and VBA code modifies report design accordingly, then
uses the DoCmd.SendObject method to email to the appropriate addressee. The
code for this is as follows :

Private Sub btnSendSelected_Click()
Dim varWP As Variant
Dim strCap As String

For Each varWP In lboxWP.ItemsSelected
'Don't try to open report if no data
If DCount("*", "qryrptCAM", "WPID=" & CInt(Nz(lboxWP.ItemData(varWP),
0))) > 0 Then
On Error Resume Next
Application.Echo False
DoCmd.OpenReport "rptWP", acViewDesign
Report_rptWP.Filter = "WPID=" & lboxWP.ItemData(varWP)
Report_rptWP.FilterOn = True
Report_rptWP.Caption = lboxWP.Column(1, varWP) & " Work Package
Report"
DoCmd.Close acReport, "rptWP", acSaveYes
Application.Echo True
DoCmd.SendObject acSendReport, "rptWP", acFormatSNP, lboxWP.Column(3,
varWP) & "@dsto.defence.gov.au", , , "HAWK LIF FSFT: Work Package Report", _
lboxWP.Column(4, varWP) & strSal & "Attached is a report for the " &
lboxWP.Column(1, varWP) _
& " Work Package." & strSig, chkEditDraft
On Error GoTo 0
Else
MsgBox "The '" & lboxWP.Column(1, varWP) & "'" & vbNewLine _
& "Work Package has no current orders," _
& vbNewLine & "or no current budget allocation.", vbInformation,
"No WP Report"
End If
Next varWP

'Reset report design to default
Application.Echo False
DoCmd.OpenReport "rptWP", acViewDesign
Report_rptWP.Filter = ""
Report_rptWP.FilterOn = False
Report_rptWP.Caption = "Work Package Report"
DoCmd.Close acReport, "rptWP", acSaveYes
Application.Echo True
End Sub


On the system I'm using for development, this works fine. Each time I
select one or more Work Packages (WPs) in the listbox and click the
SendSelected button, I get the (expected) dialog that a program is trying to
send an email message, and I can elect to allow this. Works fine, time
after time after time ...

My problem is that, on a different computer (but on the same network, with
the same Exchange server, and (apart from Access itself) almost certainly
the same default settings, the major (almost sole) user of this application
gets this dialog once, and subsequent attempts to email reports via this
code fail - silently, with no errors. I've removed the error-ignoring code
and replaced it with standard error-trapping, and no error occurs; but after
the first use, nothing happens - no dialog message, no email sent, no error
message. It seems (but I'm not completely sure about this) that the code
will again work properly, on the main user's system, after about an hour
from the first failure.

I suspect that this is an Outlook-related issue rather than an Access issue
(hence the cross-post), but I've no idea where to look. I've reported it to
the corporate IT HelpDesk, but it takes a while to get a real response from
them (and I'm not on-site permanently, as I'm a contract
developer/maintainer); the initial response was puzzlement ;-).

I'm aware of the Outlook Redemption library as a possible solution, but
that's not an approach that's going to be simple in this environment. Apart
from that, does anyone have any ideas/thought/solutions?

TIA,

Rob

 

[Brian Tillman]

Apologies for the crosspost, but I'm not sure where this will get the
best response (and also not sure of which Outlook group to post to,
so I've chosen the top-level one).

microsoft.public.outlook.program_vba or
microsoft.public.outlook.program_addins are probably better.

 

[Scott McDaniel]

Sendobject failing when called in a loop is a know issue:

http://support.microsoft.com/default.aspx?scid=kb;en-us;260819

The resolution in that article is to obtain the latest Office 2000 service pack, however (IMO) if your application
relies heavily on email then you'll be better off handling it outside of Outlook. I've used the Internet Mail Library
from here for years:

www.codestone.co.uk

It works very well, handles attachments easily, and you can call it as many times as you like without issue. There are
so freeware email suggestions here;

http://www.granite.ab.ca/access/email.htm


Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Roger Carlson]

Well, it sounds like this problem:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q260819

which goes back to Access 2000. I don't know of a fix other than Outlook
automation.

I wonder if there are setting differences between Outlook on the development
machine and on the user's machine.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Aaron Kempf]

if you want to send emails, you should use Access Data Projects

sending email from the desktop is insecure and unreliable

use Access Data Projects and xp_sendmail instead of this Docmd.crap

 

[Rob Parker]

Thanks for the advice, Brian.

I'll check out the suggestions in a couple of other replies, and if I still
need help I'l try posting to those groups.

Rob

 

[Rob Parker]

Thanks Scott,

Although the kb article refers to Office 2000 and I've got an Office 2003
environment, the code there may provide a suitable solution. I'll give it a
try, and post back results. As for using an alternative email, that's not
an alternative - this application is used solely within an organisation with
a tightly controlled environment.

Rob

 

[Rob Parker]

Thanks Roger,

I'll try the solution from the kb article.

Setting differences was something that I hoped someone would be able to help
with, as it's my feeling that that could be where the problem lies. But no
suggestions on that track yet.

Rob