Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    automated emailing (2000-SR1)

    I am using the following function to send email from a form. I would like it to offer me the choice of different email lists (from different queries and with differntly named fields). Ideally, I think this would be a pop-up form from which I could choose the correct list ("Parents", "Students" or "Assistants", e.g.). Maybe I need a separate table listing those options and their corresponding field names for the email addresses? Any help would be appreciated. Right now, I am going in manually and changing the two spots in the code for the "Set MyRS = MyDB.OpenRecordset("qryEMailList")" and "TheAddress = MyRS![Email1]" lines.
    Thanks!

    Function SendMessages(Optional AttachmentPath)

    'Dim MyDB As Database
    'Dim MyRS As Recordset
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim TheAddress As String
    'this code supplied by John Hutchinson to allow the replies to go
    'to someone other than me, like Bozo the Clown
    Dim strReplyaddress As String
    'from the next line on, this is the previous code
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset("qryEMailList")
    MyRS.MoveFirst

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    Do Until MyRS.EOF
    ' Create the e-mail message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    TheAddress = MyRS![Email1]

    With objOutlookMsg
    ' Add the To recipients to the e-mail message.
    Set objOutlookRecip = .Recipients.Add(TheAddress)
    objOutlookRecip.Type = olTo

    ' Add the Cc recipients to the e-mail message.
    If (IsNull(Forms!frmMail!CCAddress)) Then
    Else
    Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
    objOutlookRecip.Type = olCC
    End If

    ' Set the Subject, the Body, and the Importance of the e-mail message.
    .Subject = Forms!frmMail!Subject
    .Body = Forms!frmMail!MainText
    .Importance = olImportanceHigh 'High importance

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

    Re: automated emailing (2000-SR1)

    Change the header of the function to

    Function SendMessages(QueryName As String, FieldName As String, Optional AttachmentPath)

    Change

    Set MyRS = MyDB.OpenRecordset("qryEMailList")

    to

    Set MyRS = MyDB.OpenRecordset(QueryName)

    and

    TheAddress = MyRS![Email1]

    to

    TheAddress = MyRS.Fields(FieldName)

    Call like this:

    SendMessages "qryEmailList", "Email1"

    or

    SendMessages "qryOther", "MailAddress", "C:Report.doc"

    or

    SendMessages Me.cboQueries, Me.cboFields, Me.txtAttachment

  3. #3
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automated emailing (2000-SR1)

    Sorry for my ignorance, but how do I "call" ?
    Right now, the function SendMessages is being called by a macro "EMail" from a command button on the form. When I run the macro, I get the error: "The object doesn't contain the Automation object '<<QueryName>>'

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

    Re: automated emailing (2000-SR1)

    I almost never use macros because they are inflexible, hard to maintain and hard to debug. VBA code is much easier.

    I gave three examples in my previous reply of how you can call the function

  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automated emailing (2000-SR1)

    Pardon my ignorance again, please. I didn't recognize the "SendMessages" to be something to be included in the code.
    Got it figured out and working acceptably for now.
    Thanks again for your time.
    Warren

  6. #6
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automated emailing (2000-SR1)

    Now that I have that part functioning OK using the combo boxes, how could I do a combo box to specify where the "Reply To" goes?

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

    Re: automated emailing (2000-SR1)

    Expand the function header once more:
    <code>
    Function SendMessages(QueryName As String, FieldName As String, ReplyTo As String, Optional AttachmentPath)
    </code>
    Somewhere between With objOutlookMsg and End With, add the following lines:
    <code>
    If Not ReplyTo = "" Then
    .ReplyRecipients.Add ReplyTo
    End If
    </code>
    Let's say you create a combo box cboReplyTo from which the user selects a 'Reply To' address. The way to call the function now becomes something like
    <code>
    SendMessages Me.cboQueries, Me.cboFields, Me.cboReplyTo, Me.txtAttachment</code>

  8. #8
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automated emailing (2000-SR1)

    That's all working appropriately, but one more modification would be very helpful for the longer term:
    There are three lists to choose from right now, with their corresponding email address fields, selected by the combo boxes (cboQueries and cboFields) thus:
    qryEMailList, EMail1
    qryStudentEmail, StudentEMail
    qryTestEmail, EMail2

    How might I have just the three choices in a combo box ("Parents", "Students", "Test") and have the code indicate the correct query and email field? (Rather than the separate combo boxes for the query and the field.)

    Thanks once more,
    Warren

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

    Re: automated emailing (2000-SR1)

    Something like this:

    Dim strQuery As String
    Dim strField As String

    Select Case Me.cboChoice ' name of combo box
    Case "Parents"
    strQuery = "qryEMailList"
    strField = "EMail1"
    Case "Students"
    strQuery = "qryStudentEMail"
    strField = "StudentEMail"
    Case "Test"
    strQuery = "qryTestEMail"
    strField = "EMail2"
    End Select

    SendMessages strQuery, strField, Me.cboReplyTo, Me.txtAttachment

  10. #10
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automated emailing (2000-SR1)

    Would it be any easier/cleaner if I had a separate table with the fields:
    SendTo (with the entries of "Parents", "Students" and "Test"
    QueryNames
    FieldNames

    each with the corresponding data

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

    Re: automated emailing (2000-SR1)

    That would have the advantage that it's easy to change a table or field name, and also that it's easy to add or remove an entire category - simply edit the table.

    You would use the table as row source for the combo box (with Row Source Type set to Table/Query), with the 2nd and 3rd field as hidden columns (width 0). The code would change to

    SendMessages Me.cboChoice.Column(1), Me.cboChoice.Column(2), Me.cboReplyTo, Me.txtAttachment

    The column index starts at 0, so Column(1) refers to the 2nd column etc.

  12. #12
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automated emailing (2000-SR1)

    That worked great! Thanks so much!
    Warren

Posting Permissions

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