CODEWITHSUNDEEP
ms-accessvbams-access-2007
Hawsidog
Hawsidog

Reputation: 165

How do I populate the body of an email based on a variable, Access 2007

I am writing an Access 2007 application that sends an email and selects the body of the email based on a variable. I have each of the optional emails stored in a table. My current code looks like this:

Dim MyMail As Outlook.MailItem

Dim WhatEmail As Integer

Set WhatEmail = 3

MyMail.Body = "SELECT [Emails].[Body] FROM [Emails] WHERE EmailNumber = WhatEmail"

My Emails table has columns EmailNumber (integer) and Body.

I am receiving a Compile error:

Expected: line number or label or statement or end of statement.

Upvotes: 1

Views: 1181

Answers (1)

Mark Butler
Mark Butler

Reputation: 895

Your code as it is will not be doing anything meaningful; your "SELECT... " statement is merely a string, and (if the code did actually work), it would set the message body to exactly that: "SELECT [Emails].[Body] ...".

Presumably not what you want at all!

Instead we must use DAO (or similar) to get the message body from our database.

Public Sub sendMail()

    Dim WhatEmail As Integer
    Dim strSQL As String, strMessageBody As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim oApp As Outlook.Application
    Dim oMail As MailItem

    WhatEmail = 2

    strSQL = "SELECT [Body] FROM [Emails] WHERE EmailNumber = " & WhatEmail

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    If (rs.EOF And rs.BOF) Then
        Debug.Print "No matching email found"
        Exit Sub
    End If

    rs.MoveFirst
    strMessageBody = rs![Body]        
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(olMailItem)

    oMail.Body = strMessageBody
    oMail.Subject = "subject of email"
    oMail.To = "[email protected]"
    oMail.Send

    Set oMail = Nothing
    Set oApp = Nothing

End Sub

Notes on changes from your original:

The variable WhatEmail is now outside the quotes of the string, so VBA uses the value of the variable rather than the text "WhatEmail". This means we set strSQL to

"SELECT [Body] FROM [Email] WHERE EmailNumber = 2"

(or whatever number you have set your WhatEmail variable as), rather than

"SELECT [Body] FROM [Email] WHERE EmailNumber = WhatEmail"

We then load a recordset using DAO with this SQL string as the parameter. It will hopefully return just one record (the correct line from our Emails table) - but if something has gone wrong and there is no matching EmailNumber in the table, we will get a message in the Immediate window ("No matching email found") and the procedure will exit.

Otherwise, we look at the record in the table (rs.MoveFirst) and then get the Body field of it with rs![Body].

Note: I have assumed that there will be at most one entry in the Emails table with a matching EmailNumber - in my database it is the primary key.

Upvotes: 1

Related Questions