Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating records and email information (2003)

    Hello,

    I have form that is filter for users to approve invoices. I want to create a code to put in the " ON Close" event to send an email to a staff notifying them that invoices have been approve. Below is the code I am using to notify managers that they have invoices pending, but this is done through a command buttom in each invoice.
    On Error GoTo ErrorHandler_err


    Dim strmsg As String
    strmsg = "Dear " & Me![ManagerLastName] & "," & vbCrLf & vbCrLf & _
    "The Invoice Above is pending your approval."

    DoCmd.SendObject To:=Me![ManagerLastName] (this would change to joesmith@doe.com), Subject:="Invoice" & " " & Me![InvoiceStatusID] _
    & " " & "Amount $" & Me![InvoiceAmount], messagetext:=strmsg

    ErrorHandler_err:
    MsgBox ("You did not send the email")
    Exit Sub

    Is there I way I can lump all the invoices approved and added to the code above or should I develop a new code? Any help would be great. Thank you.
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating records and email information (2003)

    How can we tell that an invoice has been approved? Is there a Yes/No field Approved or something like that?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating records and email information (2003)

    Yes. there is such a field.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating records and email information (2003)

    You could do something like this. You'll have to substitute the appropriate names.

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strmsg As String
    ' Open recordset of approved invoices
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT InvoiceID FROM tblSomething WHERE Approved = True", dbOpenDynaset)
    ' Loop through the records
    Do While Not rst.EOF
    ' Add invoice number to string
    strMsg = strMsg & ", " & rst!InvoiceID
    rst.MoveNext
    Loop
    ' Get rid of first ", "
    strMsg = Mid(strMsg, 3)
    ' Close recordset
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    ' Assemble message
    strmsg = "Dear " & Me![ManagerLastName] & "," & vbCrLf & vbCrLf & _
    "The following invoices are pending: " & vbCrLf & vbCrLf & strMsg
    ' Rest of code
    ...

Posting Permissions

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