Access-Outlook Inconsistency

  • Thread starter Thread starter Pendragon
  • Start date Start date
P

Pendragon

WinXP/Access 2003

I have an interesting situation where a small client (4 desktops) is able to
utilize an Outlook email function from my access database on only two of
their computers. This function is used by several clients in several
different kinds of network arrangements.

This particular client is on Exchange Server. I have tested the function at
each computer; the two computers which fail to send the email fail at the
line
Set objOutlook = CreateObject("Outlook.Application")

I have compared and verified the settings of each computer in terms of
Windows and Office updates, Access references (Access, Word and Outlook 11.0
included), Email account settings and probably 3 or 4 other items, as well as
conferred with the network tech to insure that all exchange server email
accounts are operating under the same permissions, etc.

The code is posted below. If there is something in the code that needs
changing, please let me know. If you think it's an Outlook issue on the
particular PCs, a network issue, etc., that would be helpful because I'll
stop wasting time in examining the code. As I said, this works on two PCs
and doesn't work on the other two. To the best of my knowledge, all of the
PCs are configured in the same manner.

Private Sub btnCreateEmail_Click()
On Local Error GoTo Err_btnCreateEmail_Click

Dim CompanyDB As Database
Dim rs As Recordset
Dim strBody As String
Dim lngCount As Long
Dim lngRSCount As Long
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim strSubject As String
Dim strImportance As Integer
Dim intSent As Integer
Dim intNotSent As Integer
Dim olMailItem As Long
olMailItem = 0

intSent = 0
intNotSent = 0

Set CompanyDB = CurrentDb
Set rs = CompanyDB.OpenRecordset("Select * from tblEmailAddresses")

If rs.RecordCount = 0 Or IsNull(rs.RecordCount) Then
MsgBox "Your selection found no email addresses.", vbOKCancel
DoCmd.CancelEvent
Else
rs.MoveLast
lngRSCount = rs.RecordCount
rs.MoveFirst
strBody = Me.txtBody
strSubject = Me.txtSubject
intImportance = Me.optImportance

Do Until rs.EOF
lngCount = lngCount + 1
StrTo = rs("EmailAddress")
If IsNull(StrTo) Then
intNotSent = intNotSent + 1
lblStatus.Caption = "Missing Email " & lngCount & " of " &
CStr(lngRSCount) & "..."
Else
intSent = intSent + 1

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(StrTo)
objOutlookRecip.Type = olTo

' Set the Subject, Body, and Importance of the message.
.Subject = strSubject
.Body = strBody & vbCrLf & vbCrLf
If intImportance = 1 Then
.Importance = olImportanceHigh 'High importance
ElseIf intImportance = 3 Then
.Importance = olImportanceLow 'Low importance
Else
.Importance = olImportanceNormal
End If

' Add attachments to the message.
Dim nCur As Integer
For nCur = 0 To lstAttachments.ListCount - 1
AttachmentPath = CStr(lstAttachments.ItemData(nCur))
If Not IsMissing(AttachmentPath) Then
.Attachments.Add AttachmentPath
End If
Next nCur
.Save
.Send

End With

End If
rs.MoveNext
Loop

rs.Close
CompanyDB.Close
Set rs = Nothing
Set CompanyDB = Nothing

lblStatus.Caption = "Email routine completed."
MsgBox "Process complete for subject: " & strSubject, vbOKOnly
lblStatus.Caption = ""
End If

Exit Sub

Exit_btnCreateEmail_Click:
Exit Sub
Err_btnCreateEmail_Click:
MsgBox "Error " & Err.Number & ": " & Err.Description

End Sub
 
This particular client is on Exchange Server. I have tested the function
at
each computer; the two computers which fail to send the email fail at the
line
Set objOutlook = CreateObject("Outlook.Application")
Click to expand...

Does outlook work if a copy is already running? Often, sometimes outlook
does not startup with a default profile and it prompts the user (so when you
try to create a copy....it simply fails).

I would consider changing your above code to "try" and get a running copy of
outlook *first*.

(that way, you can simply tell the customer to have outlook running and you
have a better chance of your code running).

eg:

on error resume next
Set objOutlook = GetObject(,"Outlook.Application")
if err.number <> 0 then
err.clear
Set objOutlook = CreateObject("Outlook.Application")
if err.number<> 0 then
msgbox "can not start outlook"
exit sub
end if
end if
on error goto Err_btnCreateEmail_Click

I have compared and verified the settings of each computer in terms of
Windows and Office updates, Access references (Access, Word and Outlook
11.0
included)
Click to expand...

Since your code is using late binding, I ***strongly*** suggest you remove
the references to outlook and word. A different path + install location on
those offending machines can cause this to break. A different sp relase
can also cause breakage (ie some are running sp1, some sp2 etc....).

You might be able to go into tools->references, and remove and then re-add
the references on the offending pc in the hope
this will fix the machine. Even if this does fix the machine you have
nothing but problems every time you upgrade your code, so, remove the
references, and re-compile you code. I would also **always** distribute a
mde to the work stations.
 
Outlook doesn't have to be running in order to send mail.
CreateObject("Outlook.Application") does the job for you.

When you say that it failes on CreateObject...what error message are you
getting?

Do the windows users using the Access app have Outlook accounts setup? Have
you tried logging in different users to the PC? That would help to determine
if its possibly an Exchange issue.

At any rate, I would probably post in the Outlook newsgroup as Sue Moesher
is the supreme goddess of all things Outlook & Exchange and might be able to
shed some light as to why CreateObject is crapping out.
 
(Let me add one thing before Chuck Norris roundhouse kicks me...)

My organization issues PC's & laptops to specific persons which aren't
shared, hence we don't set up multiple Outlook profiles on any one piece of
equipment.
 
I thought about the Outlook running vs not, so I tried it both ways - the
code fails whether or not Outlook is already open.

I spent an entire day updating the machines for MS Updates - all are on XP
SP3 and Office SP3.

I will try the references idea.

An MDE is not an option simply by the nature of my business and my clients,
otherwise that's all I would be working with.

Thanks for the ideas.
 
See my reply to Albert as well. I did a test with Outlook open and closed
even though the logic of programming dictates it doesn't matter. It still
fails.

The error message is 214702477: Automation Error: The specified module could
not be found. Outlook does exist on the two machines and they are set up
with the individual's account (one per machine). I have not tried logging in
to the PC under a different username - excellent idea. I will repost with my
result.

I will try a post in Outlook - thanks for that idea.
 
I googled the error message and found a something similar, the fix was to
uninstall/reinstall. You may want to google yourself (::snickering::) and see
if that's the direction you want to go or if there are other possible fixes.
 
HA! Funny. Yeah, as I was reading through the various forum threads for
ideas I began to think it was going to require an uninstall/reinstall. Last
resorts. Anyway, I'll check out the google. On the error message, that is.
;-)

All PCs have the same references, and they are the same as what I have on my
laptop (which obviously functions properly for all of my clients' systems).
 
I'd definately have the users switch PC's as that might identify if the
problem is Exchange related. Basically, have someone who is able to
successfully send email log on to one of the offending PCS and vise versa.
 
Back
Top