CODEWITHSUNDEEP
ms-accessvba
user1706975
user1706975

Reputation: 325

Loop to write e-mail body based on query

I want to write all the records in a query to an e-mail.

This writes the first record in the query.

MyBodyText = MailList("AccountName") & "   -   " & MailList("ExpirationDate")

I know I need some kind of loop.

MailList is defined as follows

Set MailList = db.OpenRecordset("qryDateEmail")

Option Compare Database
Option Explicit

Public Function ExpirationDate()

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("qryDateEmail")

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

Set MyMail = MyOutlook.CreateItem(olMailItem)

Do While Not MailList.EOF
    MyBodyText = MailList("AccountName") & "   -   " & MailList("ExpirationDate")
    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

End Function

Upvotes: 0

Views: 3577

Answers (1)

HansUp
HansUp

Reputation: 97101

You could loop through the recordset, adding those values from each row to your body text.

Untested air code:

With MailList
    Do While Not .EOF
        MyBodyText = MyBodyText & !AccountName & _
            "   -   " & !ExpirationDate & vbCrLf
        .MoveNext
    Loop
End With

Now I see you've added similar code to your question. The problem is that code overwrites the value of MyBodyText each time through the loop. Append to MyBodyText each time instead of replacing the text ...

MyBodyText = MyBodyText & "new text"

instead of ...

MyBodyText = "new text"

Upvotes: 1

Related Questions