Discussion Locked

Automating Outlook from ACCESS

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Below is a sample of my code. I run this code as a module in ACCESSS2000. The problem I am having is that the code works fine first shot in the morning. The second time I run it, the system locks. I have tried this on both WIN2000 and WIN98.
I would like to develop this into a loop so we can send dozens of automatic e-mails. However with the lockup problem, this is not practical.

Function SendMessage(Optional AttachmentPath)

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

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

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

With objOutlookMsg
'Add the To recepient to the message
Set objOutlookRecip = .Recipients.Add([Forms]![forma]![frmSuba]![VendorEmail])
objOutlookRecip.Type = olTo

'Set Subject, Body and Importance of the message
.Subject = "Request for Info " & [Forms]![forma]![ReqNo]
.Body = "Please complete and return the attachment before " & [Forms]![forma]![DueDate] & "." & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "Thank you." & vbCrLf & vbCrLf
.Importance = olImportanceHigh

'Add attachments to the message
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

'Kill attachment
Kill ("C:\My Documents\" + "info.doc")

.Send

' .Display

End With

Set objOutlookMsg = Nothing
Set objOutlook = Nothing

End Function

5 replies

by it_cowboy

I run similar routines without getting the lockup problem and the only difference between your code and my code is that i Set the Recipient object to nothing. I suggest entering the following two lines at the end...
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing

If this does not work, on an NT PC, check the Windows NT Task Manager, processes tab and look for additional Outlook processes, maybe they are not terminating properly.

Accessing the Outlook application from Access creates a fairly large memory hit, its pretty important to make sure you terminate the objects when finished.

by charles byrne

I concur with Greg. It seems that all of your object variables need to be set to nothing when you're done with them. Also one of my programs encountered similar problems with Novell Client 32 and Office 97. My VBA routine would make ODBC calls toa shared EXCEL data file. When Excel would close, the ODBC Driver would basically knock the user off the network and she/he would lose all network functionality which resulted in a PC lockup or crash. I ended up having to move the program from EXCEL to Outlook and have Outlook Call Excel.

This discussion is closed to new replies.