VbPanicStation
Technical User
Hi all,
I hope that you can help me. I've only just started programming and I am struggling.
I have modified the code below (that I copied out of a VBA Book) to send one email per record in a query. Now I would like to modify it again such that it send one email to all the people in the query/ record set. I have got the program counting the number of records in the recordset but that's as far as I've got. Any help will be greatly appreciated. I am using VBA in access 2000 & outlook 2000. The code I have got so far is as follows:
Thanks
K
I hope that you can help me. I've only just started programming and I am struggling.
I have modified the code below (that I copied out of a VBA Book) to send one email per record in a query. Now I would like to modify it again such that it send one email to all the people in the query/ record set. I have got the program counting the number of records in the recordset but that's as far as I've got. Any help will be greatly appreciated. I am using VBA in access 2000 & outlook 2000. The code I have got so far is as follows:
Code:
Public Sub ReOrder1()
Dim db As Database ' current database
Dim recAssigned As Recordset ' recordset of items to order
Dim objOutlook As New Outlook.Application ' outlook object
Dim objMessage As MailItem ' outlook mail message
Dim strSQL As String ' sql string
Dim strOrder As String ' string of order details
Dim strItems As String ' just the order items
Dim strEmailAdd As String ' just the order items
Dim strCount As Integer 'count
Set db = CurrentDb()
'query holding relevant information only
Set recAssigned = db.OpenRecordset("qryEmailPeopleWhoHaveBeenAssignedDcr")
' now loop through the the query to get all names, email addresses etc
While Not recAssigned.EOF
'record count
strCount = recAssigned.RecordCount
'MsgBox strCount
' gets email addresses from the query
strEmailAdd = recAssigned("EmailAdd")
' define body for email
strOrder = recAssigned("Name") & _
vbCrLf & vbCrLf & _
"PUT THE BODY OF THE EMAIL MESSAGE IN HERE"
' skip those people without an email address
If Not IsNull(recAssigned("EmailAdd")) Then
' now create the mail message
Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
.To = strEmailAdd
.Subject = "New Order"
.Body = strOrder
.Send
End With
recAssigned.MoveNext
End If
Wend
' tidy up
recAssigned.Close
Set recAssigned = Nothing
Set objOutlook = Nothing
Set objMessage = Nothing
End Sub
Thanks
K