click button to populate Outlook email from fields in MS Access Form-CodePudding

I'm trying to improve my knowledge of MS Access to benefit my companies operations. at the moment I'm trying to use information from a Form to populate an email. I've kept it basic to ensure I have the theory correct before adding further information.

I have a table called ClientListtbl and a Form called ClientListfrm. I want a button to generate an email prepopulated with the Email Address (field named is EmailAddress) in the "TO" part of the email and the Last Name (field named LastName) in the subject. I will want to put a "Notes" field into the email body eventually but thought better to start small. after trawling stackoverflow and other resources I came across a code that seemed to fit and retrofitted it to my database, the code is as follows:

Private Sub Command20_Click()

    Dim LastName As Variant
    Dim Email As Variant
    Dim objOutlook As Object
    Dim objEmail As Object

    LastName = ClientListfrm!LastName
    Email = ClientListfrm!EmailAddress

    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmail = objOutlook.CreateItem(0)

    With objEmail
     .To = Email
     .Subject = LastName
     .send
End With

    Set objEmail = Nothing
    Set objOutlook = Nothing

End Sub

EmailAddress is short text LastName is Short text

when I then click the button I get an error stating

run-time error '424' Object required

clicking on debug LastName = ClientListfrm!LastName is highlighted. So i tried changing LastName and EmailAddress to As Object. which gives the same error.

any help much appreciated.

cheers

CodePudding user response：

Simply a syntax error in the way you are trying to call the field in the form. This worked for me, but there may be other methods:

Private Sub Command20_Click()

Dim LastName As Variant
    Dim Email As Variant
    Dim objOutlook As Object
    Dim objEmail As Object

    LastName = Forms("ClientListfrm").LastName
    Email = Forms("ClientListfrm").EmailAddress

    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmail = objOutlook.CreateItem(0)

    With objEmail
     .To = Email
     .Subject = LastName
     .send
End With

    Set objEmail = Nothing
    Set objOutlook = Nothing


End Sub

CodePudding user response：

This - with a reference to Outlook - works for me:

Private Sub Command20_Click()

    Dim LastName As Variant
    Dim Email As Variant
    Dim objOutlook As Outlook.Application
    Dim objEmail As Outlook.MailItem

    LastName = "Macron"           'ClientListfrm!LastName
    Email = "[email protected]" 'ClientListfrm!EmailAddress

    Set objOutlook = Outlook.Application
    Set objEmail = objOutlook.CreateItem(olMailItem)

    With objEmail
        .To = Email
        .Subject = LastName
        .send
    End With
    
    Set objEmail = Nothing
    Set objOutlook = Nothing

End Sub

So, what's missing is probably the syntax for the form controls:

    LastName = Forms!ClientListfrm!LastName.Value
    Email = Forms!ClientListfrm!EmailAddress.Value