MS Access VBA code to send an Outlook email-CodePudding

I am trying to get Access to send a simple email programatically. I have added the Outlook 16.0 reference. Below is the code. When it gets to the With oMail part, it returns an error: "Application-defined of object-defined error."

It errors on the .ReplyRecipients.Add line. If I comment out that line, then it errors on the .Send line.

Note I am running the TestSend() sub to activate the SendEmailOutlook sub.

Sub TestSend()
    Call SendEmailOutlook("[email protected]", "Test message", "Test message.")
End Sub

Public Sub SendEmailOutlook(strTo As String, strSubject As String, strBody As String)
    
    On Error GoTo SendEmailOutlookErr
    
    Dim strEmail As String
    Dim strMsg As String
    Dim oLook As Object
    Dim oMail As Object
    
    Set oLook = CreateObject("Outlook.Application")
    Set oMail = oLook.createitem(0)
    With oMail
        .ReplyRecipients.Add "[email protected]"
        .to = strTo
        .htmlbody = strBody
        .Subject = strSubject
        .Send
    End With
    Set oMail = Nothing
    Set oLook = Nothing
    Exit Sub
    
SendEmailOutlookErrExit:
        Exit Sub
    
SendEmailOutlookErr:
        MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
        Resume SendEmailOutlookErrExit
End Sub

CodePudding user response：

In the code the late binding technology is used, so the COM reference is optional. But if you have already added the Outlook COM refence you may declare all Outlook objects instead of just having the object in the declaration. It can help. Read more about the late and early binding in the Using early binding and late binding in Automation article.

Also the following line of code contains multiple property and method calls:

With oMail
  .ReplyRecipients.Add "[email protected]"

The code is valid. But it makes sense to declare each property or method on a separate line of code, so you could easily find the faulting one - where exactly the error occurs.

The MailItem.ReplyRecipients property returns a Recipients collection that represents all the reply recipient objects for the Outlook item. Use the Add method to create a new Recipient object and add it to the Recipients object. The Type property of a new Recipient object is set to the default for the associated AppointmentItem, JournalItem, MailItem, or TaskItem object and must be reset to indicate another recipient type.

Set myItem = Application.CreateItem(olMailItem)  
Set myRecipient = myItem.Recipients.Add ("Jon Grande")  
myRecipient.Type = olCC

Another aspect is how Outlook has been configured to trust applications on a client computer, an application that uses the Outlook object model to access certain data or execute certain actions can invoke security warnings or throw errors when Outlook is automated without any UI. Depending on the type of information or action that the program was attempting to access or execute, there are three different security prompts that applications can invoke through the Object Model Guard: the address book warning, send message warning, and execute action warning. Read more about that in the Outlook Object Model Security Warnings article.

CodePudding user response：

The error message "Application-defined or object-defined error" suggests that there is an issue with the object or variable being referred to. In this case, it is likely that the issue is with the "ReplyRecipients.Add" line.

The "ReplyRecipients" property is used to add recipients to the "To" field of a reply message, but in this case, you're trying to add recipients to a new mail item. Instead of using "ReplyRecipients.Add," try using the "Recipients.Add" property, which adds recipients to the "To" field of a new mail item.

Also, "oLook.createitem(0)" is used to create a new mail item, you can use "oLook.CreateItem(0)" instead.

Try this:

Public Sub SendEmailOutlook(strTo As String, strSubject As String, strBody As String)

On Error GoTo SendEmailOutlookErr

Dim oLook As Object
Dim oMail As Object

Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
With oMail
    .Recipients.Add strTo
    .HTMLBody = strBody
    .Subject = strSubject
    .Send
End With
Set oMail = Nothing
Set oLook = Nothing
Exit Sub

SendEmailOutlookErrExit:
Exit Sub

SendEmailOutlookErr:
MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
Resume SendEmailOutlookErrExit
End Sub