Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    13

    Unanswered: email all records in a query

    Apologies - I have done a search but cannot find the solution: I have a Multiple Form with a query as the record source, which is a list of overdue borrowers of some electrical items. I have a command button which does send an email, but only to the first record. I want it to send the mail to all records/users in the list. I think I maybe need some sort of Recordset and loop to do this but I can't work it out! Any help gratefully received - Mark Norton.

    Below is the code I'm using:

    Private Sub send_email_Click()

    Dim txtUserName As String
    Dim txtItem As String
    Dim txtLoanDate As String
    Dim txtDueDate As String
    Dim txtOverdue As String
    Dim txtUserEmail As String
    Dim BodyText As String


    BodyText = "Hi," & _
    Chr$(13) & "You borrowed the Item " & txtItem & "on " & txtLoanDate & ". With any extension" & _
    "you may have had, your loan is currently " & txtOverdue & "days overdue. " & Chr$(13) & _
    "Please could you return the itme at your earliest convenience, or could you contact" & _
    "the IT Helpdesk requesting a loan extension." & _
    Chr$(13) & "Many Thanks, " & _
    Chr$(13) & "School of Health IT Helpdesk" & Chr$(13) & _
    Chr$(13) & "Tel: x4148"


    Dim objOutlook As Outlook.Application
    Dim objEmail As Outlook.MailItem


    txtUserName = Me!txtUserName
    txtItem = Me!txtItem
    txtLoanDate = Me!txtLoanDate
    txtDueDate = Me!txtDueDate
    txtOverdue = Me!txtOverdue
    txtUserEmail = Me!txtUserEmail


    Set objOutlook = CreateObject("Outlook.application")
    Set objEmail = objOutlook.CreateItem(olMailItem)


    With objEmail
    .To = txtUserEmail
    .Subject = "Your IT Equipment Loan: Overdue Item"
    .CC = "m.norton@tees.ac.uk"
    .Body = "Hi," & _
    Chr$(13) & "You borrowed the Item " & txtItem & "on " & txtLoanDate & ". With any extension" & _
    "you may have had, your loan is currently " & txtOverdue & "days overdue. " & Chr$(13) & _
    "Please could you return the itme at your earliest convenience, or could you contact" & _
    "the IT Helpdesk requesting a loan extension." & _
    Chr$(13) & "Many Thanks, " & _
    Chr$(13) & "School of Health IT Helpdesk" & Chr$(13) & _
    Chr$(13) & "Tel: x4148"
    .Send

    End With



    'objOutlook.Quit
    'Set objEmail = Nothing

    DoCmd.Close
    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's an example of a loop that should get you started:

    Sample Code illustrating looping through a DAO recordset
    Paul

  3. #3
    Join Date
    Jul 2005
    Posts
    13

    Thanks

    Many Thanks - I'll study that later and have a go. I'll let you know how I get on.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •