How do I use vba code to send an external file via email

Question

I have found the code below but it halts on the first line of the code with error 'user-defined type not identified'

I only have limited knowledge and I would very much appreciate some help, I am trying to develop this to help someone get through the Corona Virus lockdown.

I am using Access from Office 365, I suspect the code I have used as a template maybe from an earlier version of Access

The code I have is _

Private Sub Command14_Click()

    Dim oApp As New Outlook.Application

    Dim oEmail As Outlook.Mailitem

    Set oEmail = oApp.Createitem(olMailitem)

    oEmail.to = "******@123456.co.uk"

    oEmail.Subject = "No Subject"

    oEmail.Body = ""

    oEmail.attachments.Add "C:\CTS\test.txt"

    oEmail.send

End Sub

Can anyone help me with a fix.

Many many thanks if you can

John

Answer 1

ciao John,

you are missing to set the correct reference to Ol application, as you are applying early binding association.

Open VBa editor set put a check here ( tools--> references ) :

in case you do want to base on predefined references, get a look on this for late binding :

    Dim oApp As Object   ' New Outlook.Application

    Dim oEmail As Object  ' Outlook.Mailitem

    On Error Resume Next

    Set oApp = GetObject(, "Outlook.Application")

    If OlApp Is Nothing Then

        Set oApp = CreateObject("Outlook.Application")

    End If

    on Error Goto 0

    Set oEmail = oApp.CreateItem(0) ' olMailitem

    oEmail.to = "*** Email address is removed for privacy ***"

    oEmail.Subject = "No Subject"

    oEmail.Body = ""

    oEmail.attachments.Add "C:\CTS\test.txt"

    oEmail.Display    

    Set oEmail = Nothing

    Set oApp = Nothing

you had better destroy alway the object created before end the sub.

One more suggestion is to be sure about the attachment file exists before trying to attach it on the email.

HTH.-

Ciao, Sandro.

Answer 2

Use a function, such as

https://www.devhut.net/2010/09/03/vba-send-html-emails-using-outlook-automation/