Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email group of people based on two variables? (2000)

    Hi,

    Looking to do the following: -

    We have two variable fields which correspond to:

    a) salesman
    [img]/forums/images/smilies/cool.gif[/img] which office that salesman is base

    What we want to do is that to notify everyone else when a particular field changes record changes it looks at the salesman id and gets the office that they belond to and sends an e-mail to all the other people from that office except that person informing them of the change, ant ideaS?
    Best Regards,

    Luke

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

    Re: Email group of people based on two variables? (2000)

    I would not recommend sending out e-mails each time a field is changed. Users edit a field, notice a mistake, correct the mistake, etc. - this would cause an enormous amount of e-mail traffic most of which is useless.
    Whether sending out e-mails when a record is changed is useful/feasible depends on how often records are modified. If this happens frequently, I would recommend against it. It would be very annoying to receive loads of e-mails just announcing a smalkl change in a record in a table in a database...

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email group of people based on two variables? (2000)

    Okay, if I chnage it to a command button.

    Whena price of a house changes, we have to by law send a letter to the owner confirming the action, with that command button when we send the letter we could also send a e-mail to everyone else?
    Best Regards,

    Luke

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

    Re: Email group of people based on two variables? (2000)

    Yes; you can open a recordset on the table containing the names and e-mail addresses you need, loop through its records and add the e-mail addresses to the recipients list. Details depend on the way you send the message now, and on the structure of the table.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email group of people based on two variables? (2000)

    Hans,

    The e-mails go via the outlook vba code

    The tblsalesman is contructed like the attached picture
    Best Regards,

    Luke

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

    Re: Email group of people based on two variables? (2000)

    In the declaration section at the beginning of your code:

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    In the body of the code, somewhere between With NewMail and the corresponding End With:

    Set cnn = CurrentProject.Connection
    rst.Open "SELECT EAddress FROM tblSalesman WHERE SalesID <> " & Me.SalesID, _
    cnn, adOpenForwardOnly, adLockOptimistic, adCmdText
    Do While Not rst.EOF
    With .Recipients.Add(rst!EAddress)
    .Type = olCC
    End With
    rst.MoveNext
    Loop

    And in the "cleanup" section near the end:

    rst.Close
    Set rst = Nothing
    Set cnn = Nothing

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email group of people based on two variables? (2000)

    rst.Open "SELECT EAddress FROM tblSalesman WHERE SalesID <> " & Me.SalesID, _

    On that line how can i add another condition?

    I have specified earlier on

    strlogon = Environ("Username")
    varoffice = DLookup("tblSalesMan![officebased]", "tblsalesman", "Winlogon=" & Chr(34) & strlogon & Chr(34))


    And now I want to add the condition

    officebased = varoffice
    Best Regards,

    Luke

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

    Re: Email group of people based on two variables? (2000)

    Try this:

    rst.Open "SELECT EAddress FROM tblSalesman WHERE SalesID <> " & Me.SalesID & " AND OfficeBased = " & varOffice, _
    ...

  9. #9
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email group of people based on two variables? (2000)

    thank you, last thing...

    is there a away to put the person default signature in att he end of the e-mail in vba?
    Best Regards,

    Luke

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

    Re: Email group of people based on two variables? (2000)

    The Outlook VBA model is very limited, there is no direct way of inserting a signature. There are roundabout ways, see for example Insert user signature with VBA.

Posting Permissions

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