Grouping data to send to email

[MichelleB]

Hi again, i have a filter that shows the bookings for the weekend, which i can click on each act and it generates an email for then to confirm. however some acts have more than one booking on that weekend and i want to be able to list the bookings in one email, how would i do this please, the email script i use at the moment is below, the acts can be grouped by a field called grouped

Private Sub artist_DblClick(Cancel As Integer)
Dim msgTxt As Variant
Dim objOutlook As Outlook.Application
Dim objMailItem As Outlook.MailItem
Dim blnCreated As Boolean
Dim act As String
Dim venue As String
Dim start As String
Dim finish As String
Dim actfee As String
Dim netpay As String
Dim actpayment As String
Dim actcomment As String
Dim gigdate As String
Dim street As String
Dim commision As String


strEmail = "michelle@work.com.au"
strSubject = "Weekend Booking Checkoff "
strBody = "<h2><b>Please confirm your"
act = Me.artist
gigdate = Format(Me.gigdate, "dddd dd mmmm yyyy")
venue = Me.venuename
start = Format(Me.start, "hh:nn am/pm")
finish = Format(Me.finish, "hh:nn am/pm")
street = Me.street
suburb = Me.suburb
actfee = Format(Me.actfee, "00.00")
commission = Format(Me.commission, "00.00")
netpay = Format(Me.netpay, "00.00")
paid = Me.actpayment



blnCreated = False

Set objOutlook = New Outlook.Application

If IsNull(Me.artist_email) = True Or Me.artist_email = "" Then
DoCmd.Hourglass False
msgTxt = MsgBox("Unable to create an email for " & Me.artist & Chr(13) & "No email address listed in the database.", vbOKOnly + vbInformation, "")
Else
DoCmd.Hourglass True
Set objMailItem = objOutlook.CreateItem(olMailItem)

With objMailItem
.To = Me.artist_email
.CC = strEmail
.Subject = "Week " & GetMonthWeek(Me.gigdate) & " " & Format(Me.gigdate, "mmmm") & " " & strSubject & " - " & act
.HTMLBody = strBody & " Week " & GetMonthWeek(Me.gigdate) & " " & Format(Me.gigdate, "mmmm") & " booking" & "<p>" & "</b>" & "</h2>" & "<p>" & act & "<br>" & gigdate & "<br>" & venue & "<br>" & street & ", " & suburb & "<br>" & start & " - " & finish & "<br>" & "Act Fee: $" & actfee & "  Less Commission: $" & commission & "  Net Pay: $" & netpay & "<p>" & "Payment Details: " & paid & "<p>" & "Please reply OK to confirm this booking" 


.Save
' .Send
blnCreated = True
End With

End If

If blnCreated Then

Else
msgTxt = MsgBox("Email Failed.", vbOKOnly, "")
End If

DoCmd.Hourglass False
End Sub

 

[MikeLeBen]

I would access the recordset of the bookings and prepare the string to use as the body of the e-mail.

Something like:

Dim db as DAO.Database
dim rs as DAO.Recordset
dim strBody as String

Set db = CurrentDB()
Set rs = db.OpenRecordset("bookings")

if not rs.BOF and rs.EOF Then rs.MoveFirst

Do While Not (rs.BOF And rs.EOF)

strBody = strBody & rs.Fields(0) & vbNewLine 'use index, or field name between double quotes
Loop

'now strBody should contain all the bookings

rs.Close

I haven't tested the above so it might need some tweaking.