CODEWITHSUNDEEP
vbaformsoutlookms-access-2010
Drew
Drew

Reputation: 1

Generate email body with values from MS Access userform

I created a form and would like to send an email using a button capturing data from the sub-form (based on a query).

I am using this YouTube video as a guide and get stuck (starting from msg =....

Private Sub cmd_EmailContact_Click()

    Dim Msg As String
    
    msg = "Dear " & First name & ",<P>" & _
        Student First name & "has been successfully been loaded on the platform" & ",<P>" & _
        "Student login details on the platform are:" & ",<P>" & _
        "Username:" & Username & ",<P>" & _
        "Password:" & Password**

    Dim O As Outlook.Application
    Dim M As Outlook.MailItem
    
    Set O = New Outlook.Application
    Set M = O.CreateItem(olMailItem)
    
    With M
        .BodyFormat = olFormatHTML
        .HTMLBody = Msg
        .To = Email
        .Subject = "Student available on OARS"
        .Display        
    End With
    
    Set M = Nothing
    Set O = Nothing

End Sub

Variables are populated on a query on the form.
First name (Name of teacher)
Student First name
Username
Password

Upvotes: 0

Views: 1832

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

To send the email as HTML, you will need to format the body with HTML tags and set the HTMLBody property of the olMailItem (email) as shown below.

The example uses late binding (no reference to Outlook needed) which means it can run with different versions of outlook installed.

Private Sub cmd_EmailContact_Click()

    Dim firstName As String, _
        studentFirstName As String, _
        userName As String, _
        password As String, _
        email As String, _
        body_ As String
    
    'provide values
    firstName = "ABC"
    studentFirstName = "XYZ"
    userName = "User"
    password = "Pass"
    email = "[email protected]"
    
    'build body
    body_ = "<p> Dear" & firstName & ", </p>" _
          & "<p>" & studentFirstName & " has been successfully been loaded on the platform. </p>" _
          & "<p> Student login details on the platform are: </p>" _
          & "<p> Username: " & userName & "</p>" _
          & "<p> Password: " & password & "</p>"

    'send email
    With CreateObject("Outlook.Application")
        With .CreateItem(0) 'olMailItem
            .BodyFormat = 2 'olFormatHTML
            .To = email
            .Subject = "Student available on OARS"
            .HTMLBody = "<html><head></head><body>" & body_ & "</body></html>"
            .Display
        End With
    End With
    
End Sub

You will need to provide values for the following variables:

  • FirstName
  • StudentFirstName
  • UserName
  • Password
  • Email

Upvotes: 1

Related Questions