0

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

Community
  • 1
  • 1
Drew
  • 1
  • 1
    Does this answer your question? [Sending Email using Access VBA](https://stackoverflow.com/questions/64628633/sending-email-using-access-vba) – braX Sep 30 '21 at 02:40
  • What is `Student First name`? Are you thinking that is some sort of variable name? fwiw, I do not recommend using youtube to learn how to code. – braX Sep 30 '21 at 03:16
  • What you are really asking is "how do i create a string on multiple lines using variables?" because the rest of the code is fine (and has nothing to do with actually sending emails from anywhere), although i don't think `.BodyFormat` is needed. – braX Sep 30 '21 at 03:20
  • Try `msg = "Test"` instead of that block of code that sets `msg` and it should work. – braX Sep 30 '21 at 03:23
  • Thanks for your advice BraX. I'll give the msg = "Test" a try. I'd like to include the variables below in the body of text in the email. Variables are populated on a query on the form. - First name (Name of teacher) - Student First name - Username - Password – Drew Sep 30 '21 at 05:01
  • @braX - Msg = "Test" works! Can you advise how to link multiple strings over multiple lines with query results? Thanks – Drew Sep 30 '21 at 05:07
  • https://stackoverflow.com/questions/16624550/how-to-break-long-string-to-multiple-lines/16624590 – braX Sep 30 '21 at 05:13
  • and the `

    ` in your string means "paragraph" in HTML, which translates to "new line in the email". (kinda a sloppy way to do it, but it will work) - an alternative would be to use `
    ` instead. so try this next `Msg = "Test
    Test2
    Test3"` and notice what that does to see what i mean.

     – braX Sep 30 '21 at 05:15

1 Answers1

1

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 = "foo@bar.com"
    
    '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
Kostas K.
  • 8,293
  • 2
  • 22
  • 28