Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    42

    Question Unanswered: Sending email using a Loop?

    Problem: I want to use send mail to send an email to multiple recipients.

    Here is my code.

    Private Sub SubmitEngTstPlanBtn_Click()
    Dim stWhere As Variant '-- Project Engineer assigned to project
    Dim varTo As Variant '-- Email Address for Lab Engineer
    Dim stText As String '-- E-mail text
    Dim stSubject As String '-- Subject line of e-mail
    Dim stReqNo As String '-- The requset no
    Dim userName As String '-- Get user name
    Dim strRequestr As Variant '-- Person requesting pur req approval
    Dim OutStart '--Needed to start Outlook
    Dim strLevel As String '--Level 'A' for Lab Engineer
    On Error GoTo Err_SubmitEngTstPlanBtn_Click

    strLevel = "A"
    stReqNo = Forms![engineering Test plan]![Test ID]
    userName = Environ("Username")
    strRequestr = DLookup("[Name]", "Employees", "[usrname] ='" & userName & "'")
    stWhere = DLookup("[Name]", "Employees", "[level] ='" & strLevel & "'")
    '-- Looks up email address from Employees Table
    varTo = DLookup("[email]", "Employees", "name = '" & stWhere & "'")
    '--composing the email
    stSubject = ":: Test Request #" & stReqNo & " Submitted for Approval ::"
    stText = "Test request #" & stReqNo & " has been submitted by " & strRequestr & _
    " for approval." & Chr$(13) & Chr$(13) & _
    "This is an automated message. Please do not respond to this e-mail."
    '--Starting outlook here
    OutStart = Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE /recycle", vbMinimizedNoFocus)
    '--Write the e-mail content for sending to assignee (-1 or true alows abbilty to change email.
    '0 or false does not alow changing of email.
    DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, True
    '--Update the following fields only if the email is sent
    [Forms]![engineering Test plan]![Status].vale = Submitted

    '--Close engineering Test plan form
    DoCmd.Close acForm, "engineering Test plan"
    '--Refresh main form
    [Forms]![Main Switchboard Form].SetFocus
    [Forms]![Main Switchboard Form].Refresh
    Exit Sub

    Exit_SubmitEngTstPlanBtn_Click:
    Exit Sub

    Err_SubmitEngTstPlanBtn_Click:
    'MsgBox Err.Description '-- Can be used to show system generated error
    'Message returned if email not sent
    MsgBox "In order to process this requisition you must send the email.", vbCritical, "Try again"

    Resume Exit_SubmitEngTstPlanBtn_Click

    End Sub

    The code works fine except I want to send an email to all employees that are level "A". I'm pretty sure I can do this using a loop, but I haven't ever used one.

    Thanks for you help in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd sugest you try sending to many recipients, perhaps the BCC be appropriate rather than a single recipient per email. Some MTA's have a problem with a null recipient, some have a limit on the length of the send string. Might be worth working out if proposed means of sending emails limits are.

    you can add recipients if they are separated by a semi colon.

    you don't actualy need to shell out to outlook, it can be controlled via Access or you can attach to the libraires & datasoures direct. The one downside with the attach is that yur email doesn't get sent by that process, it sits in th queue untill outlook sends the email itself.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I presume your problem is that:
    Code:
     stWhere = DLookup("[Name]", "Employees", "[level] ='" & strLevel & "'")
    returns the "first" employee name rather than all?

    A few quickies:
    Try not to use Variants - strings will do fine in your case.
    You obviously don't have Option Explicit set (Tools-> Options-> Require Variable Declaration). It will mean you need to declare your objects like this:
    Code:
     Dim OutStart as Object
    but it will save you many hours tracking down a typo bug.
    Environ is not well recommended for obtaining values. May be a bit pedantic, but try googling for an API function (GetUserName rings a bell - its the name of the function I use anyway).

    To answer the question - you will want to pop the relevent email addresses in a recordset (ADO, DAO - the exciting choice is yours) and then loop i.e.

    Code:
    Do While Not MyRecordset.EOF
     
    varTo = varTo & ";" & MyRecordset.Fields("email").Value
     
    Loop
     
    varTo = Mid(varTo, 1, LEN(varTo) - 1)
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2004
    Posts
    42
    pootle flump,

    I'm kinda a newbe when it come to VBA. When do I want to declare a variant and when do I want to delcare a string. I thought I read somewhere that if I use dlookup I need to declare.

    Anyways
    How do I intergrate the recordset into my code. This is still pretty new to me.

    Thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    ......
    Environ is not well recommended for obtaining values. May be a bit pedantic, but try googling for an API function (GetUserName rings a bell - its the name of the function I use anyway)......
    PKStormy had a neat soluton togetting the network logon using the system object (I Think)
    This thread show the dirty deed in all its glory. It looks a far neater way of getting the info then Dev Ashish's API call. Mu only reservation is which versions of Access will support that method. - still works on the ones I use so am happy.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BRIANGAMER
    pootle flump,

    I'm kinda a newbe when it come to VBA. When do I want to declare a variant and when do I want to delcare a string. I thought I read somewhere that if I use dlookup I need to declare.
    A variant is a bulky variable. The reason for this is that it can contain any value that any other variable can contain. It is a sort of lowest common denominator variable. You only really want to use it where you don't know in advance what data type it will contain.

    The reason it has been suggsted is that DLookup returns null if there is no return. Instead, try:
    Code:
     Dim strRequestr As String '-- Person requesting pur req approval
    
    strRequestr = NZ(DLookup("[Name]", "Employees", "[usrname] ='" & userName & "'"), "")
    If DLookup returns null, the code converts it to a zero length string ("") which a string variable can accept.

    Ayhoo - lots of off topic text.

    If your BE is Access\ Jet, check out DAO in help. Specifically you want to look at how to instantiate a DAO Recordset object using the OpenRecordset method of the DAO Database object. See how you go and post if you get stuck.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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