6
votes

Can anyone tell me why I'm getting the "Run-time error '91'" message in my function below? It's happening on this line:

Set olMailItem = olApp.CreateItem(olMailItem)

Also, whenever I'm in debug and I place my cursor over this line, Access gives me this message: "Outlook.Application = < Object variable or With block variable not set >":

Dim olApp As New Outlook.Application 

I'm trying to create a button that will open an outlook email message and allow the data entry clerk to edit the message before sending it. I've checked my references and I have Microsoft Outlook 14.0 Object Library checked.

Also, if you have any suggestions on making my code more efficient, please share. I'm fairly new to Access programming.

Private Sub EmailButton_Click()
    Dim EmailThis As String

    EmailThis = CreateEmailWithOutlook("[email protected]", "Testing e-mail Access database", "This is a test")
    DoCmd.SendObject acSendForm, "SubmitNewIdeaForm", , "My Name", , "Test", , True
    On Error GoTo CreateEmail_Exit

CreateEmail_Exit:
    Exit Sub

End Sub

Public Function CreateEmailWithOutlook(MessageTo As String, Subject As String, MessageBody As String)

    ' Define app variable and get Outlook using the "New" keyword
    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem  ' An Outlook Mail item

    Set olApp = CreateObject("Outlook.Application")
    ' Create a new email object
    Set olMailItem = olApp.CreateItem(olMailItem)

    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = MessageTo
        .Subject = Subject
        .Body = MessageBody
        .Display    ' To show the email message to the user
    End With

    ' Release all object variables
    Set olMailItem = Nothing
    Set olApp = Nothing

End Function
1

1 Answers

8
votes

The problem is that, with the Outlook library reference enabled, olMailItem is a reserved constant, and I think when you are Dim olMailItem as Outlook.MailItem that is not a problem, but trying to set the variable is causing a problem.

Here is the full explanation:

You have declared olMailItem as an object variable.

  • On the right side of the assignment statement, you are referencing this Object prior to setting it's value to an instance of an object. This is basically a recursive error, since you have the object trying to assign itself itself.
  • There is another potential error, if olMailItem had previously been assigned, this statement would raise another error (probably a Mismatch error, since the constant olMailItem is an Integer but by using this name inappropriately, you may introduced the mismatch error by passing an Object where an Integer is expected.

Try changing the name of this variable olMailItem to something else, like mItem. This code is tested in Excel 2010, Windows 7, I think it should work for Access, too:

Dim olApp As New Outlook.Application
Dim mItem As Outlook.MailItem  ' An Outlook Mail item

Set olApp = CreateObject("Outlook.Application")
Set mItem = olApp.CreateItem(olMailItem)
' Add the To/Subject/Body to the message and display the message
With mItem
    .To = MessageTo
    .Subject = Subject
    .Body = MessageBody
    .Display    ' To show the email message to the user
End With

' Release all object variables
Set mItem = Nothing
Set olApp = Nothing