sending Outlook e-mail from Access form

[may1hem]

I have a form with client details and notes. I want a button on the form which allows the user to send the client name and notes to a colleague in our company.

We need this totally idiot-proof, so I thought that we should steer clear of asking the user to select names from his/her Outlook address book. Perhaps a couple of list boxes on the Access form? One list box for selecting the colleagues that the e-mail should be sent TO, and the other for selecting colleagues that the e-mail should be CC to.

What code would I need?

Is it possible to do this without showing the main Outlook window or the Compose New Message window, so that the user is not confused?

What if the user has configured Outlook not to send messages instantly? Is there a way to force Outlook to send this message?

Any ideas on what to do if Outlook has a SEND/RECEIVE error? How should the code handle this? How should the user be notified?

Almost forgot: the user needs to be notified whether the message was sent successfully, and if not, why not?

All ideas are appreciated!

Thanks,

May

 

[qzl53x]

Option Compare Database
Option Explicit

Function sendemail()
Call SendMessage(True)
End Function




Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
Dim objOutlook As New 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&quot

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

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Livesay, Robin M&quot
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Rundell, Bob&quot
objOutlookRecip.Type = olCC

' Add the BCC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Pedota, Jennine&quot
objOutlookRecip.Type = olBCC

' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "This is the body of the message." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

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

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
End Sub

 

[may1hem]

Thanks qzl53x!

I'm confused... Where should this code go, and how should it be run?

May

 

[may1hem]

I'm getting a "user-defined type not defined" error on the "Dim objOutlook As New Outlook.Application" line of the code.

I forgot to mention that we're using Access 97. Is that the cause of the problem here? If so, what code would we need?

Thanks, =oD

May

 

[may1hem]

When I type "Dim objOutlook As ", it doesn't offer "Outlook" in the drop-down auto-completion list.

I also tried "Dim objOutlook As New ", but again, it doesn't offer "Outlook" as one of the completion options.

Any ideas why this would be?

Thanks,

May

 

[may1hem]

Aha, I believe I have worked it out.

Outlook wasn't on the Dim drop-down list because Access wasn't using Outlook's object library. So, I open the Access Visual Basic Editor, go to TOOLS ---> REFERENCES ---> tick "MICROSOFT OUTLOOK # OBJECT LIBRARY". The "#" depends on the version of Outlook that you are using. "#" is "8.0" for Access 97 and is "10.0" for Access XP.

 

[scking]

You need to add Outlook as a reference. Open any module in design mode and go to tools\references to select Microsoft Outlook as a reference. You will then see Outlook as an type when dimensioning variables.

The code above merely sends a test message. You would obviously need to make some modifications to fit your needs. Also, it doesn't return a set of addressees from Outlook to fill a listbox. You have requested a number of things; Get addressees from Outlook, Fill listboxes with addressees, allow selection of multiple addressees, compile the list of addressees from the listboxes for the TO: and CC: sections of the Email, send the Email, Check for errors in Outlook while running an application in Access, and notifying the user of the results of the event. This is no simple task but is achievable, with the possible exception of running the database, trapping errors in Outlook, and reporting them to the Access user.

My contribution: Open Outlook and fill a listbox.

Dim myOlApp As Outlook.Application
Dim myNameSpace As NameSpace
Dim olFolder As MAPIFolder
Dim oNotice As frmNotice

Public objOutlook As Object
Public objNamespace As Object
Public mFolder As Object
Public bOutlookRunning As Boolean
Public Const ErrCreatingOutlookObject$ = _
"Could not create the Microsoft Outlook automation object. Check to make sure that Microsoft Outlook is properly installed."
Const olFolderCalendar = 9
Const olFolderContacts = 10
Const olFolderDeletedItems = 3
Const olFolderInbox = 6
Const olFolderJournal = 11
Const olFolderNotes = 12
Const olFolderOutBox = 4
Const olFolderSentMail = 5
Const olFolderTasks = 13

Sub FillCtrlWithOutlookContacts(ctrl As Control)

Dim objFolder As Object
Dim objAllContacts As Object
Dim Contact As Object

'Screen.MousePointer = vbHourglass

If OutLook_Open Then
' Set the default Contacts folder
Set objFolder = objNamespace.GetDefaultFolder(olFolderContacts)
' Set objAllContacts = the collection of all contacts
Set objAllContacts = objFolder.Items
' Loop through each contact
For Each Contact In objAllContacts
' Display the Fullname field for the contact
If Len(Trim(Contact.FullName)) <> 0 Then
ctrl.AddItem Contact.FullName
End If
Next
End If
ctrl.ListIndex = -1
'Screen.MousePointer = vbDefault

End Sub

Public Function OutLook_Open() As Boolean

On Error Resume Next

bOutlookRunning = False

' Check to see if Outlook is already loaded
Set objOutlook = GetObject(, &quot;Outlook.Application&quot

If objOutlook Is Nothing Then
' Create a new instance of it
Set objOutlook = CreateObject(&quot;Outlook.Application&quot

' Check to make sure it worked
If objOutlook Is Nothing Then
MsgBox ErrCreatingOutlookObject
GoTo Err_Outlook_Open
End If

End If

Set objNamespace = objOutlook.GetNamespace(&quot;MAPI&quot
bOutlookRunning = True
OutLook_Open = True

Exit_Outlook_Open:
Exit Function

Err_Outlook_Open:
OutLook_Open = False
MsgBox &quot;Error: &quot; & Err & &quot; &quot; & Error
GoTo Exit_Outlook_Open
End Function ----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[may1hem]

Thanks scking!

I have solved this now and it's working perfectly.

2:30am and time to sleep (at last)!

May