Forum Discussion

Please_ser's avatar
Please_ser
Copper Contributor
Apr 06, 2023

Sending an Outlook email with attachments from Access VBA

My Access database sends emails through Outlook using VBA code.  It works correctly if I don't specify a file to attach.  It I do, I get an error.  I am using Windows 11 Pro and Microsoft 365.

4 Replies

    • Please_ser's avatar
      Please_ser
      Copper Contributor

      Karl_Donaubauer This is the code. 

      The line in green is where I get error 438 - Object doesn't support this property or method

       

      Function fSendOutlookEmail(strTo As String, strSubject As String, strBody As String, bEdit As Boolean, Optional strCC As Variant, Optional strBCC As Variant, Optional AttachmentPath As Variant, Optional strSender As String) As Boolean
      'Send Email using late binding to avoid reference issues
      Dim objOutlook As Object
      Dim objOutlookMsg As Object

      Dim objOutlookRecip As Object
      Dim objOutlookAttach As Object
      Dim i As Integer
      Dim strSignature As String
      Dim strHyperlinks As String
      Const olMailItem = 0

      On Error GoTo ErrorMsgs

      Set objOutlook = CreateObject("Outlook.Application")
      Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

      With objOutlookMsg

      .To = strTo

      If Not IsMissing(strBCC) Then
      .bcc = strBCC
      End If

      If Not IsMissing(strCC) Then
      .cc = strCC
      End If

      .Subject = strSubject
      .HTMLBody = strBody
      .Importance = 1 'Importance Level 0=Low,1=Normal,2=High

      ' Add attachments to the message.
      If Not IsMissing(AttachmentPath) Then
      If IsArray(AttachmentPath) Then
      For i = LBound(AttachmentPath) To UBound(AttachmentPath) - 1
      If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
      Set objOutlookAttach = objOutlookMsg.Attachments.Add(AttachmentPath(i))
      End If
      Next i
      Else
      If AttachmentPath <> "" Then
      Set objOutlookAttach = objOutlookMsg.Attachments.Add(AttachmentPath)

      End If
      End If
      End If

      'Choose sender

      If Not IsMissing(strSender) Then
      .SentOnBehalfOfName = strSender
      End If

      If bEdit Then 'Choose btw transparent/silent send and preview send
      .Display
      Else
      .Send
      End If
      End With
      fSendOutlookEmail = True

      Set objOutlookMsg = Nothing
      Set objOutlook = Nothing
      Set objOutlookRecip = Nothing
      Set objOutlookAttach = Nothing

      ErrorMsgs:
      If Err.Number = "287" Then
      MsgBox "You clicked No to the Outlook security Attention. " & _
      "Rerun the procedure and click Yes to access e-mail " & _
      "addresses to send your message. For more information, " & _
      "see the document at http://www.microsoft.com/office" & _
      "/previous/outlook/downloads/security.asp."
      fSendOutlookEmail = False
      Exit Function
      ElseIf Err.Number <> 0 Then
      MsgBox Err.Number & " - " & Err.Description, vbCritical, "Attention"
      fSendOutlookEmail = False
      Resume Next
      Exit Function
      End If
      End Function

      • Hi,

         

        Your code works without problem here when I test it.

        This message can also pop up when the path and/or file you are trying to add as attachment are wrong or not accessible out of any reason. Are you sure that it is? Perhaps test it with a simple local path and file.

         

        Servus
        Karl
        ****************

        Access Bug Trackers

        Access News
        Access DevCon

         

Resources