Results 1 to 13 of 13
  1. #1
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2

    Answered: Look up Email address and send email

    I have a user form that enters data into a table. It also sends an email with the following code.

    Code:
    Private Sub Command16_Click()
    Dim myOb As Object
    Dim AutoSend As Boolean
    
    Set myOb = CreateObject("Outlook.Application")
    Set oMail = myOb.CreateItem(olMailItem)
    
    
    AutoSend = True
    
    With oMail
        .Body = "There is a Shift available on," & vbNewLine & Me.TBDate.Value & Me.LBShift.Value & vbNewLine
      
        
        .Subject = "Available Transport"
        .To = "email address"
        
        If AutoSend Then
            .Send
        Else
            .Display
        End If
    End With
    
        Set oMail = Nothing
    End Sub
    This works great. What I would like is for the Name that is in the List Box List21 to then look for that name in the field Employee Name in the Table TBLMaleStaff and use the email address that is in the Field Email of Table TBLMaleStaff.

    I have tried the following DLookup code but this does not work any ideas?


    Code:
    DLookup("Email", "TBLMaleStaff", "Employee Name=ME.List21.Value")

  2. Best Answer
    Posted by healdem

    "no its very clear what you are trying to do

    you are trying to pull use the value of a control or column or variable called name as the parameter in a dlookup statement. but your code is saying lookup in the table TBLMaleStaff the employee who's employees name is name



    what we don't know is whether there is a control, column or variable called name in your form, thats one source of error, but lets assume their is a control / columkn or variable called name inthe current scope of the form

    but there are so many things wrong here

    1) you are using reserved words or symbols, google MS access reserved word to find a list of words and symbols you should NOT use in an Access application. near the top of that list is a space. so Employee Name is an invalid name for a column or table. Access muddies the waters by allowing a workaround by enclosing the invalid name in square brackets
    Code:
    .to = DLookup("Email", "TBLMaleStaff", "[Employee Name]= Name")
    2) as said before unless you want to use the literal value name you need to 'drop in/out' of direct quoted text to use a column/variable/control when building a string
    Code:
    .to = DLookup("Email", "TBLMaleStaff", "[Employee Name] = " &  Name)
    3) as said before text literals MUST be delimited by a ' or "
    Code:
    .to = DLookup("Email", "TBLMaleStaff", "[Employee Name] = '" &  Name & "'")
    BTW your code will fail if the dlookup cannot find the specified employee, dlookup returns NULL if it cannot find the specified row
    BTW 2 your code will fail if the specified employee name includes a ', eg "Shamus O'Subcontractor""


  3. #2
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    I should have said that this code is for the email address like below.

    .to= DLookup("Email", "TBLMaleStaff", "Employee Name=ME.List21.Value")

  4. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    To use a value from a control or variable it needs to be directly referenced instead of
    Code:
    DLookup("Email", "TBLMaleStaff", "Employee Name=ME.List21.Value")
    Use
    Code:
    DLookup("Email", "TBLMaleStaff", "Employee Name=" &  ME.List21.Value)
    however a string / text value MUST be delimited, either ' or ". So thats
    Code:
    DLookup("Email", "TBLMaleStaff", "Employee Name = '" & ME.List21.Value & "'")
    FYI date literals should be either iso yyyy/mm/dd or US format mm/dd/yyyy AND delimited by #

    One thing to bear in mind is that some peoples surnames may have an apostrophe eg O'BlastedIrishman. So you may need to cater for that.
    Code:
    dim empname as string
    Empname = replace (list21, "'", "/'")
    DLookup("Email", "TBLMaleStaff", "Employee Name =  '" & empname & "'")
    I'd rather be riding on the Tiger 800 or the Norton

  5. #4
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2

    Not working

    Code:
    .To = DLookup("Email", "TBLMaleStaff", "Employee Name= Name")
    The error says I have a missing operator in the expression 'Employee Name = Name'.

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Doug60M View Post
    Code:
    .To = DLookup("Email", "TBLMaleStaff", "Employee Name= Name")
    The error says I have a missing operator in the expression 'Employee Name = Name'.
    Thats hardly surprising.....
    Compare your code with the code in post #3
    I'd rather be riding on the Tiger 800 or the Norton

  7. #6
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2

    Kind of stuck!

    healdem, thank you for your help. I tried all of the variations from your reply and none worked. I then tried to enter the criteria that would be taken from the form, but that does not work either. I think there is something wrong with trying to pull the email address from the table TBLMaleStaff.

    Code:
    Private Sub Command16_Click()
    Dim myOb As Object
    Dim AutoSend As Boolean
    
    Set myOb = CreateObject("Outlook.Application")
    Set oMail = myOb.CreateItem(olMailItem)
    
    
    AutoSend = True
    
    With oMail
        .Body = "There is a Shift available on,"
      
        
        .Subject = "Available Transport"
        .To = "email address"
        
        If AutoSend Then
            .Send
        Else
            .Display
        End If
    End With
    
        Set oMail = Nothing
    End Sub
    The line of code in RED is where I enter this code
    Code:
    .To = DLookup("Email", "TBLMaleStaff", "Employee Name= Name")
    Directly entering the email adesss works great. Trying to retrieve the email address from the table, not so good.

  8. #7
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    Maybe it is not clear what I am trying to do. I have a Table [TBLMaleStaff] with Fields [Email] and [Employee Name]. I need to send an email to the address that matches the record with the Employee Name.

  9. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no its very clear what you are trying to do

    you are trying to pull use the value of a control or column or variable called name as the parameter in a dlookup statement. but your code is saying lookup in the table TBLMaleStaff the employee who's employees name is name



    what we don't know is whether there is a control, column or variable called name in your form, thats one source of error, but lets assume their is a control / columkn or variable called name inthe current scope of the form

    but there are so many things wrong here

    1) you are using reserved words or symbols, google MS access reserved word to find a list of words and symbols you should NOT use in an Access application. near the top of that list is a space. so Employee Name is an invalid name for a column or table. Access muddies the waters by allowing a workaround by enclosing the invalid name in square brackets
    Code:
    .to = DLookup("Email", "TBLMaleStaff", "[Employee Name]= Name")
    2) as said before unless you want to use the literal value name you need to 'drop in/out' of direct quoted text to use a column/variable/control when building a string
    Code:
    .to = DLookup("Email", "TBLMaleStaff", "[Employee Name] = " &  Name)
    3) as said before text literals MUST be delimited by a ' or "
    Code:
    .to = DLookup("Email", "TBLMaleStaff", "[Employee Name] = '" &  Name & "'")
    BTW your code will fail if the dlookup cannot find the specified employee, dlookup returns NULL if it cannot find the specified row
    BTW 2 your code will fail if the specified employee name includes a ', eg "Shamus O'Subcontractor"
    Last edited by healdem; 03-08-16 at 12:04.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #9
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2

    got it

    healdem, thank you so much for your help.

    Changed some of the names of the fields etc.

    this is what worked.

    Code:
    .to = DLookup("Email", "TBLMaleStaff", "EmployeeName = '" & Me.Combo27.Value & "'")

  11. #10
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    Now that I got it working I'm trying to have it send more than one email. One each to the name in 2 different combo boxes.

    Code:
    .to = DLookup("Email", "TBLMaleStaff", "EmployeeName = '" & Me.Combo27.Value & "'")
    .to = DLookup("Email", "TBLMaleStaff", "EmployeeName = '" & Me.Combo28.Value & "'")
    It works fine If they both have a name selected. It fails if Combo28 is empty? this may happen where only one email is needed.

  12. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Multiple email recipients are added too the .to (or CC, BCC) using a semi colon.

    For the user interface there are several approaches you could use.
    1 allow the user to select more than one person in the combo/list box. Then have a command button which when pressed iterates through the list/combo boxes selected collection adding each email to the to property
    2 transfer a selected email/person from available to selected combo/list box. Clicking on the selected box removes the person and places 'em back in the available list. Again pressing a command button then transfers from the selected to the .to property of the email.
    3 a simpler approach is to add email ids as the user clicks on 'em.

    The adding of emails to the .to property should probably be in a function. Eg
    Code:
    Private function addEmail(thisEmail as string, EMailObject as ?????) as boolean
    addEmail = vbfalse 'set up or default return value
    'The ???? Should be replaced with what ever email client object you are using. It could be of type OBJECT or variant  if you want to keep it generic ;) the reason for this is to write generic functions. So if required you could use the same function in another form (having moved the code to a code module and made it public)
    ' do some validation. never trust supplied data in a function. but ultimately its your call. if you trust the dtaa or don't want to validate your call.
    ' An email MUST have 3 components.
    'a destination
    'an @ symbol
    'a domain
    'If it doesnt validate return false
    With EMailObject
      If not isnull(.to) and len(.to) > 0 then
        .to = .to & ";" ' add a separator if there is already something there
      Endif
      .to = .to & thisEmail 'append the email address
    End with
    addEmail= vbtrue
    End function
    if the email address is coming from a combo boix then when populating the combo box I'd suggest you also sanitise the data by escaping any ' or " symbols so you cna use the data straight fromt he combo/list box bear in mind you don't have to use a a dlookup to get an email address for a person IF the person is coming from a combo or list box. you can stuff other values into a combo and manipulate them using the .column property. but its a trade off between grabbing all the data to populate a list box or grabbing all the names and then getting the email address when required. this is why some application allow you to type say 3 characters before filling a list / combo box. it would not make sense to say populate a list box with 1,000 names and email addresses, if you find you are reloading the form (and combo box) frequently. if the data is fairly static then its fine. dont' commit to any design paradigm without understanding the underlying network and or performance issues
    Last edited by healdem; 03-09-16 at 06:25.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #12
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2
    Now that I have the code sending emails, thanks to healdem, I am wondering about controlling the email account that the email comes from.

    The above code sends the email from the account that is open in Outlook. If I have Outlook set up with 2 different accounts can I tell the code to send from one of the accounts?

  14. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Offhand dont know how youd do that. But if you can do that in outlooknitself, then it should be doable in ACcess VBA with an Outlook object.

    So you need to investigate the Outlook object and find which properties / methods you need to use. I would expect the thing you are looking for will be in the top level outlook object. IE MyOB.

    Two ways of doing this. Either try to find the required stuff on t'internet or in code explore what properties / methods you have access to by typing myob. And see what the intellesense suggests. But that assumes your outlook object is called myob AND you refer to it BEFORE you create the email.

    Heck for all I know what you are looking for may be an optional parameter on the initialisation of the outlook object itself, or the email object for that matter
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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