CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Location
    Lubbock, TX
    Posts
    15

    [RESOLVED] Outlook 2003 Automatic E-Mail

    Hello all,

    I have a working module that will send an e-mail through Access via Outlook, so this post isn't another one seeking code to get something like that working.
    What I'm wondering, is if there is a way to specify WHO to send the e-mails to. Basically, I am sending out e-mails to a specified list of people to let them know that job tickets have been "out" for 5, 10, or 15 days (or longer). At 5 days, the clerks (secretaries) get the message, at 10 days the managers and accounts receivable people get the message, so on... The 10 day list does not need to receive the 5 day list, so I have created three separate tables in order to prevent this from happening.
    What I would LIKE to do, is consolidate these tables into one, and have a field within the table that specifies whether they are a 5, 10, or 15 day message recipient. Also, the 5 day list receives both the 10 and 15 day message, and the 10 day list receives the 15 day message. I hope this all makes sense..
    I appreciate any help y'all can give!

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Outlook 2003 Automatic E-Mail

    Just use a 1,2, or 3 for the type, and the date as the parameter. Subtract that date from NOW to determine what to send.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Aug 2012
    Location
    Lubbock, TX
    Posts
    15

    Re: Outlook 2003 Automatic E-Mail

    Thanks for the reply!
    So here's what I'm going to try..
    In the table containing the e-mail addresses, I'll assign a "type" to the names in the table (5-Day addresses will be type 3, 10-Dayers will be type 2, and 15-dayers will be type 1).
    I then plan to use an "If" statement to determine which names to send the e-mail to.
    Code:
    'If ![Type] = 3 Then
    ' The next line sends the E-Mail
    DoCmd.SendObject acSendForm, "5-Day Notification", , ![E-Mail Address], , , "5-Day Reminder!", "Hello " & ![First Name] & ", " & Chr(10) & "You have tickets that have been out for 5 days or longer. Please get them processed as soon as possible.", False
    .MoveNext ' Moves to the next record in the recordset
    Loop
    End If
    End With
    That was the code I just tried, and got an error message that said "Compile Error: Loop without Do"
    So I know I have SOMETHING wrong. Maybe I misunderstood your advice?

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Outlook 2003 Automatic E-Mail

    there is no DoCmd. Check DO WHILE... before LOOP
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Aug 2012
    Location
    Lubbock, TX
    Posts
    15

    Re: Outlook 2003 Automatic E-Mail

    Code:
    Private Sub btnSendEmail_Click()
    Dim rs As DAO.Recordset
    Dim stDocName As String
    Dim StrAttach As String
    Set rs = CurrentDb.OpenRecordset("5-Day E-Mail Addresses")
    With rs
    If .EOF And .BOF Then ' If no tickets were found, no e-mails will be sent.
    MsgBox " No emails will be sent because there are no records from the query '5-Day Notification' "
    Else
    'Do Until .EOF ' Do until the end of the recordset
    Do While ![Type] = "3"
    
    ' The next line sends the e-mail
    DoCmd.SendObject acSendForm, "5-Day Ticket Notification Subform", , ![E-Mail Address], , , "5-Day Reminder!", "Hello " & ![First Name] & ", " & Chr(10) & "You have tickets that have been out for 5 days or longer. We ask that you delay no further in getting them processed. Please see the attached document for details.", False
    .MoveNext ' Moves to the next e-mail address
    Loop
    End If
    End With
    
    ' Clean up memory at the end
    If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
    End If
    End Sub
    This was my best shot. It sent the e-mail to the first person in the list whose type was 3, but I can't get it to send to any of the other threes in the list.

Tags for this Thread

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured