Email Query Results in Access 2007

Question

Good Morning,

I was hoping someone could help me out. I'm not overly experienced when it comes to VBA coding and have something that needs to be done and to be honest I dont know where to start.... I have come across some coding I could use which is below

Dim strSQL
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim MyDecision As String
Dim strReportName As String
Dim strEnroll As String
Dim strWho As String
Dim strEmail As String

Set fso = New FileSystemObject
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset("qryEmail")

Subjectline$ = "Expiration Date" & " " & Date

Set MyMail = MyOutlook.CreateItem(olMailItem)

Do While Not MailList.EOF
MyBodyText = MailList("")
MailList.MoveNext
Loop

MyMail.To = "" & ""
MyMail.CC = CurrentUser() & ""

MyMail.Subject = Subjectline$

MyMail.Body = MyBodyText
MyMail.Display

strEmail = Now()
strWho = CurrentUser()

Set MyMail = Nothing
Set MyOutlook = Nothing

The query is called qryemail and these are the tables within that query

ColID, ColDate, Companyname, address, address1, county, town, postcode

Where in the code above would I put these table names in ......

Any help would be greatly appreciated

thanks Emma

Its a collections/delivery system for our Transport Department.... — Emma, Oct 28 '13 at 10:49
If you're doing early bindings, make sure everyone is using the same object library. If you develop in 2010 and a user has 2003, it will error out (in my experience). Here is an example of late bindings: http://stackoverflow.com/questions/19256504/sending-automated-email-from-access-vba-through-outlook Just a suggestion because I've ran into the issue. — Grant, Oct 28 '13 at 19:04

score 0 · Answer 1 · answered Oct 28 '13 at 18:50

To clarify, you're trying to email the query results to a number of people. Correct? If so you're pretty close.

Take a look at this code. It demos the way to get values out of the query. To use, replace the text <INSERT YOUR FIELD> with the name of a field from your query. So, if you wanted Companyname, put that in place of the text.

Do While Not MailList.EOF
  If MyBodyText = "" Then
    MyBodyText = MailList![<INSERT YOUR FIELD>]
  Else
    MyBodyText = MyBodyText & "," & MailList![<INSERT YOUR FIELD>]
  End If
  MailList.MoveNext
Loop

score 0 · Answer 2 · answered Jun 01 '14 at 16:46

You need to iterate through the query's field names (ColID, ColDate, Companyname, address, address1, county, town, postcode) and concatenate them all into the email's message body text.

To achieve all the following in the email body:

ColID: (some value)
ColDate: (some value)
Company: (some value)
Address: (some value)
Address1: (some value)
County: (some value)
Town: (some value)
Postcode: (some value)

The code would be as follows:

MailList.MoveLast
MailList.MoveFirst

MyBodyText = ""

Do While Not MailList.EOF
MyBodyText = MyBodyText & "ColID: " & MailList!ColID & vbNewLine
MyBodyText = MyBodyText & "ColDate: " & MailList!ColDate & vbNewLine
MyBodyText = MyBodyText & "Company: " & MailList!CompanyName & vbNewLine
MyBodyText = MyBodyText & "Address: " & MailList!address & vbNewLine
MyBodyText = MyBodyText & "Address1: " & MailList!address1 & vbNewLine
MyBodyText = MyBodyText & "County: " & MailList!county & vbNewLine
MyBodyText = MyBodyText & "Town: " & MailList!town & vbNewLine
MyBodyText = MyBodyText & "Postcode: " & MailList!postcode & vbNewLine

MailList.MoveNext
Loop

Please be aware that this iterates through ALL records of recordset (qryEmail). If that is not your intention, consider adding a where clause in dbOpenRecordset line to filter or condition output to a specific record or few records.

Email Query Results in Access 2007

2 Answers2