CODEWITHSUNDEEP
ms-accessvbams-access-2007html-email
Hawsidog
Hawsidog

Reputation: 165

In Access 2007 how to print results of sql query into the body of an email message

I have an access database that sends an email out with some reminder information. I’d like to use the results of a sql query to populate the body of the email. Right now I am using GetRows() and am able to debug.print the data that I want, however, I am not familiar with how to get this into the email. Should I be trying to save it as an array or something like that?

My current code looks like this:

'Select which serial numbers to display
sqlSerialNumbers = "SELECT serialNumber, [Item Number] FROM [Equipment on loan] WHERE Evaluation = " & MailList![ID]

Set serialDB = CurrentDb
Set serialRS = serialDB.OpenRecordset(sqlSerialNumbers)

serialVar = serialRS.GetRows(serialRS.RecordCount)
Debug.Print "Serial Number", "Part Number"
    For serialRowNum = 0 To UBound(serialVar, 2) 'Loop through each Row
        For serialColNum = 0 To UBound(serialVar, 1) 'Loop through each Column
            Debug.Print serialVar(serialColNum, serialRowNum),
        Next
    Debug.Print vbCrLf
   Next

serialRS.Close


'Open Outlook
Set MyOutlook = New Outlook.Application

'This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

'This populates the fields 
MyMail.To = emailAddress
MyMail.Subject = Subjectline
MyMail.HTMLBody =  "I want the results of GetRows here."

Upvotes: 0

Views: 65

Answers (1)

asdev
asdev

Reputation: 943

This is a possible way with concatenated strings. Also I used standard "RecordSetPointers" (I do not know the name). But you can adapt it to your for-loops

'Select which serial numbers to display
sqlSerialNumbers = "SELECT serialNumber, [Item Number] FROM [Equipment on loan] WHERE Evaluation = " & MailList![ID]

Set serialDB = CurrentDb
Set serialRS = serialDB.OpenRecordset(sqlSerialNumbers)

Dim strResult as String
'initialize strResult empty    
strResult = ""

serialVar = serialRS.GetRows(serialRS.RecordCount)

If not serialRS is Nothing then 'Check null
    If not (serialRS.EOF and seriaRS.BOF) then'Check Empty
        serialRS.MoveFirst 'not neccessary but still good habit
        Do While Not serialRS.EOF
            'I use your loop here
            'You could refer to the columns by serialRS(0), serialRS(1),... 
            'or serialRS(COLUMNNAME)...
            For serialColNum = 0 To UBound(serialVar, 1) 'Loop through each Column
                strResult = strResult & serialVar(serialColNum, serialRowNum) & vbCrLf 
                'separated by linefeed
            Next
            serialRS.MoveNext 'next RS
        Loop
    End if  
End If

'Clean Up   
serialRS.Close: set serialRS = nothing
serialDB.Close: set serialDB = nothing

'Open Outlook
Set MyOutlook = New Outlook.Application

'This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

'This populates the fields 
MyMail.To = emailAddress
MyMail.Subject = Subjectline
MyMail.HTMLBody =  strResult'"I want the results of GetRows here."

Upvotes: 1

Related Questions