Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2012
    Posts
    38

    Unanswered: How Can I Pull Email Addresses from a Table in an Auto-generated Email?

    With Access 2007.

    I have a script written that will send automatically generated emails but I'm stuck on the "To" part. See code below.

    Background: I need to send an email to a certain person so they can approve an order. The Mfg_Cd (manufacturing code) designates which E-Mail address the email will be sent to. This script needs to contain some way to reference the Mfg_Cd and pull the proper E-Mail address so that it can put into the "To" section of my code.

    The script:

    Private Sub AutoRouteMessage()
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment

    Set objOutlook = CreateObject("Outlook.Application")

    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
    Set objOutlookRecip = .Recipients.Add("")
    objOutlookRecip.Type = olTo

    Set objOutlookRecip = .Recipients.Add("")
    objOutlookRecip.Type = olCC

    .Subject = "International Authorization"
    .Body = "Hi Team, Please let me know if the following orders are okay to approve." &

    vbCrLf & vbCrLf
    .Importance = olImportanceHigh

    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send

    End With
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub


    I can manually put an email address into either:

    With objOutlookMsg
    Set objOutlookRecip = .Recipients.Add("")
    objOutlookRecip.Type = olTo

    Set objOutlookRecip = .Recipients.Add("")
    objOutlookRecip.Type = olCC

    but this doesn't let me reference the table. I'm playing with the idea of using a SQL query to reference the table:

    Dim str_SQL As String
    "SELECT E-Mail FROM t_Routing WHERE Mfg_Cd is not null"

    You can ignore the WHERE part because some Mfg_Cd's will have to have an email entered in manually after the fact (but I have an If statement that will avoid this issue in the script at question).

    I tried putting the above SQL in the script like:

    With objOutlookMsg
    Set objOutlookRecip = .Recipients.str_SQL
    objOutlookRecip.Type = olTo

    but with no luck. Any advice?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what do you think this does
    SELECT E-Mail FROM t_Routing WHERE Mfg_Cd is not null
    presumablky you store an email address for a mfg_cd in a table

    I'd expect something like
    select EMAILID from mytable where mfg_cd = 'blah'
    then Id execute the SQL (you may have to do this as a recordset)
    or use dlookup
    email_address = dlookup("E-Mail","mytable","mfg_cd='" & "blah" & "'")
    you'd need to put in some error handling to trap for null /missing email id's
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2012
    Posts
    38
    Quote Originally Posted by healdem View Post
    what do you think this does

    presumablky you store an email address for a mfg_cd in a table

    I'd expect something like
    select EMAILID from mytable where mfg_cd = 'blah'
    then Id execute the SQL (you may have to do this as a recordset)
    or use dlookup
    email_address = dlookup("E-Mail","mytable","mfg_cd='" & "blah" & "'")
    you'd need to put in some error handling to trap for null /missing email id's
    You are correct about what I'm going for with that SQL statement. Unfortunately there are different Mfg_Cd's (liker 20+) and each class of code corresponds to a different email so I wasn't sure how to approach it if I tried

    Where Mfg_Cd = 'something'

    Thanks for the dlookup advice, I'm looking into that.

    Any advice on how to put in an error handling trap?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    providing you know what product(s) should be authorised by what what person(s) and you can replicate that association in the data then it should be fine using dlookup
    error handling could be on error goto
    or a test after the dlookup for a null value

    you can also be a wee bit sneaky and send to a collecting email account if there is no email set for a mfg_cd
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2012
    Posts
    38
    Thanks healdem! I'm going to try using dlookup then. I might be back if I have trouble.

    As for sending null email's to a collecting email account that won't work well for me because I have this AutoRoute sub in an If Then statment and the other part of that statement takes care of the Mfg_Cd's with null emails (if you wanted to know :-p)

  6. #6
    Join Date
    Jun 2012
    Posts
    38
    Dlookup seems to have worked. I used:

    Dim varX As Variant
    varX = DLookup("[E-Mail]", "t_Routing", "[Mfg_Cd] is not null")

    Set objOutlookRecip = .Recipients.Add(varX)
    objOutlookRecip.Type = olTo

    and pasted that into the script in my original post. It would not work when I set Mfg_Cd to one of the actual Mfg_Cd codes but I think this way works fine.


    Not sure if I should be a separate thread but here goes: I'm trying to pull some specific records that correspond to Mfg_Cd's and place them in the body of the email. Is that done with dlookup? I'm having trouble thinking how to approach this.Currently an employee manually copies and pastes the records from a table in the datasheet view.

    EDIT: On second thought, I probably do need the specifc Mfg_Cd in the dlookup because as of now Access would be emailing everyone about all codes rather than just the codes attached to their email.
    Last edited by axkoam; 06-14-12 at 19:30.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    seems to have worked
    in my books doesn't cut it
    either it works or it doesn't
    and the only way to know it does work is to run it against test data that should mimic every possible known combination of data that might be expected to be out there in the real world

    do you have just the one email address that you send these approvals to?
    I would have expected you to pull a specific email address for a specific product or product group, not just any old mfg_cd
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2012
    Posts
    38
    healdem,

    There are different Mfg_Cd's, around 20 of them but let's use these 3 for my example: BLK, HPM, QAD.

    All BLK's get sent to 1 email, all HMP's to another, and all QAD's to a 3rd email address.

    I tried running the query using Dlookup with 3 different email assigned to these 3 Mfg_Cd's and each email got sent a message.

    So that's what I meant by it seems to have worked (I then changed the email's around i.e. using the same email for 2 Mfg_Cd's and a different email for the 3rd code and only those 2 email addresses were messaged).

    The issue I'm facing now is how to replace an employee manually copy/pasting the relevant query output to each email address because it is different for each email and right now I just have 1 generic email that is auto-generated and sent to all of the email addresses that I asked it to.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The dlookup you are using is not giving you what you think it is.
    It's returning the first mtg-cd which is not null, which could be anything.
    I would expect you to specify a mtg-cd or product Id. Effectively the 3rd parameter of the dlookup function is a where clause. A where clause is used to limit the rows returned
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2012
    Posts
    38
    Ok I'll mess around with it to see if I can get it to send to the correct addresses by specifying the where criteria.

    Thanks.


    EDIT: Yeah, this is what I changed it to.

    varX = DLookup("[E-Mail]", "t_Routing", "[Mfg_Cd] ='HPM' or 'BLK' or 'QAD'")

    but in this case I'll have to amend it to contain all Mfg_Cd's that might come into the query, right?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by axkoam View Post
    Ok I'll mess around with it to see if I can get it to send to the correct addresses by specifying the where criteria.

    Thanks.


    EDIT: Yeah, this is what I changed it to.

    varX = DLookup("[E-Mail]", "t_Routing", "[Mfg_Cd] ='HPM' or 'BLK' or 'QAD'")

    but in this case I'll have to amend it to contain all Mfg_Cd's that might come into the query, right?
    No

    I doubt that will work, I'd expect that to return an error
    you know what mfg_cd a specific product belongs to I guess. so use that mfg_cd value as the '=' bit

    bear in mind when using values form elsewhere in a fragment of SQL you need to quote string values so the sql engine knows where the string value starts and stops

    assuming that you have a variable, or control called ProductMfg_CD and it is a string value
    varX = DLookup("[E-Mail]", "t_Routing", "[Mfg_Cd] ='" & ProductMfg_CD & "'")
    if it was numeric
    varX = DLookup("[E-Mail]", "t_Routing", "[Mfg_Cd] =" & ProductMfg_CD & )
    you still need to cater for a missing or corrupt varX = DLookup("[E-Mail]", ProductMfg_CD and a null (not found) mfg_cd

    if you got your code to work using
    varX = DLookup("[E-Mail]", "t_Routing", "[Mfg_Cd] ='HPM' or 'BLK' or 'QAD'") then I suspect you never actually use that line of code.

    in all honesty what you coudl do is extractg the mfg_cd at the same time you pull the product details


    lets say your products table is called Products, somewhere you have associated a producxt with a mfg_cd, lets call it it mfg_cd in products
    then
    select my, column, list,t_Routing.E-MAIL from Products
    left join t_Routing on t_routing.mfg_cd = Products.mfg_cd

    that way round you pull the mfg_cd as part of the basic data extraction.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2012
    Posts
    38
    Thanks a lot healdem, I'm going to try that join you recommended to pull the mcf_cd last. I may be back.

  13. #13
    Join Date
    Jun 2012
    Posts
    38
    Solved. Thanks healdem

    EDIT: I'm actually still having trouble with this.

    Could you explain this part more?

    Quote Originally Posted by healdem View Post
    in all honesty what you coudl do is extractg the mfg_cd at the same time you pull the product details


    lets say your products table is called Products, somewhere you have associated a producxt with a mfg_cd, lets call it it mfg_cd in products
    then
    select my, column, list,t_Routing.E-MAIL from Products
    left join t_Routing on t_routing.mfg_cd = Products.mfg_cd

    that way round you pull the mfg_cd as part of the basic data extraction.
    Last edited by axkoam; 06-20-12 at 14:00.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dlookup should work

    lets assume you are using a control called tbMfg_Cd to store the mfg_cd

    then , assuming mfg_cd is alphanumeric
    EmailAddr = dlookup("EMAIL","T_ROUTING","MFG_CD= '" & tbMfg_Cd & "'")

    assuming mfg_cd is numeric
    EmailAddr = dlookup("EMAIL","T_ROUTING","MFG_CD= " & tbMfg_Cd)

    but you should allow for the possibility that a dlookup may not find a record
    also you shoudl allow for the possibulity that Mfg_cd in your products table isn't defined

    if isnull(tbmfg_cd) or strlen(tbmfg_code) = 0 then 'we don't have any value for mfg_cd
    'do something
    'personally I'd let the process continue, send the email to a collecting account for manual sorting and forwarding
    emailaddress = "axkoam@megacorp.com" 'set to our default address
    else 'we do have a value for mfg_cd
    emailaddress = dlookup("EMAIL","T_ROUTING","MFG_CD= '" & tbMfg_Cd & "'")
    if is null(emailaddress) then 'we couldn't find the specified mfg_code
    emailaddress = "axkoam@megacorp.com" 'set to our default address
    endif
    endif


    this code would send an email to axkoam@megacorp.com so that the correct recipient could be deduced manually and forwarded onto that person
    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
  •