Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Sending an Outlook email with attachments from Access VBA

Copper Contributor

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

Hi,

 

That's unfortunate, but without seeing the code, or at least the error message, no one will be able to help you.

 

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

@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

 

Thanks for testing the code. I have tried to attach a local file but perhaps I should try that again. Do you think it has something to do with the version of Office I am using? It is Office 365 Family.