Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Multiple Email (2002)

    In a continuous form, a cmdbtn for each record is used to send email.

    <pre>Private Sub cmdEmail_Click()
    Dim strEmailAddress As String
    strEmailAddress = "mailto:" & Me![Email]
    Application.FollowHyperlink strEmailAddress, , True
    End Sub</pre>


    Might there be a way to send to multiple addresses (Bcc: would be cool) based on the "displayed" records (i.e. Filter by Selection)?
    Being simple minded, I nearly tried this code in the Footer, but there ain't no "Me!" there (or at least he doesn't know who he is).
    . . .if I could just gather all the (diplayed) addresses together in the footer. . .hmmm. . .
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Bryan

    Here is code I recently worked out to send e-mail messages to members selected from a List Box on a form.

    Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Dim strTemp As String

    strTemp = ""
    Set frm = Forms!frmSelect
    Set ctl = frm!lstMembers

    For Each varItm In ctl.ItemsSelected
    If ctl.Column(3, varItm) = "" Then
    MsgBox "There is no e-mail address for " & ctl.Column(2, varItm) & " " & ctl.Column(1, varItm)
    Else
    strTemp = strTemp & ctl.Column(3, varItm) & ";"
    End If
    Next varItm
    If strTemp = "" Then
    MsgBox "Please select one or more names.", vbExclamation
    Me.lstMembers.SetFocus
    Exit Sub
    End If
    DoCmd.SendObject , , , strTemp

    Hope this is of help.

    Tom

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Tom:

    Thanks for the feedback!
    I know pretty much nothing about code (except how to hijack, borrow, beg), but it seems to me that your code works on a known (knowable?) variable (i.e. construct string from a control). In a continuous form the "displayed" records are not assembled in any control per se (at least not one I'm aware of). I wouldn't know what to use to assemble the string. I sure appreciate your input though and, if I'm mistaken, any explanation that would help me understand better.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Bryan
    There may be other ways but you could try this approach...
    1. Build another form which has a Multi-Select List Box which gathers the names of all the people.
    2. Add a command button which, when pressed, will put the names selected into the e-mail TO: line in Outlook Express
    3. On your other form, if you want to send messages to selected persons, put a command button to open the form that has the Multi-Select list box.

    You will, of course, need to change the code I offered to suit your own field names.

    Tom

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Tom:

    Whoa! <img src=/S/confused.gif border=0 alt=confused width=15 height=20> (<-- natural state) . . .you totally lost me there!
    How would the "other" form know how the continuous form was sorted?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Bryan
    I'm assuming that your Continuous Form is based on a table or query.

    In the "other" form, you create your List Box from the same table or query. If you use the Wizard to create the list box, you can indicate how you want it sorted. Or you can go into the RowSource property for the list box, press the elipses (...) and do the sorting in the query that will show (it will look just like any other query, so assuming you want to sort by the persons' last names, you can do that there).

    Tom

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    I'm sorry Tom, I guess I haven't made myself very clear. My continuous form (currently) has as many as 21 different fields to sort by; these are set by (RightClick > Sort By Selection). So then, there could be up to 26 different combinations of selections -- none of which "reside" in a table or query. The continuous form might start out displaying thousands of records, but (through Sort by Selection) it might only end up displaying a few -- it is those records I want to send the batch e-mail to. Did I explain that any better? Sorry for the confusion (as I said, it's my natural condition)<img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Bryan
    Well, then I'm not sure what approach to take.

    I would assume, however, that if there are "thousands of records" from which the continuous form is created, they reside in a table somewhere. And, for the list box, you will only be looking for a few fields (e.g. LastName, FirstName, e-mail address).

    So you could build a List Box right there on your form, based on the selections in your continuous form. That's tricky, though, as it needs to be fed an SQL that creates the List Box, and I would have to see the form in order to go anywhere near it.

    Beyond that, I'm not sure I'm helping much...so one option for you is to make an entirely new post, describing your situation in full detail. I'm sure there's an answer out there for you.

    All the best.

    Tom

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Tom:

    Thanks so much for your input! Some kind soul always comes to the rescue (or tells me to abandon ship), so I'll leave this post open rather than dup the question and see what comes of it. Again, thanks for the comments, they are always appreciated!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Hey Hans:

    Is . . .Me.subSubTotal.Form. . . the name of the current form (my continuous form)?
    If so, then I get a "Method or data member not found" error. I get the error whether or not I edit that line.
    I do have the reference.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Multiple Email (2002)

    Edited by HansV - mistakenly used subform in code

    Try this:

    Private Sub cmdEmail_Click()
    Dim rst As DAO.Recordset
    Dim strEmailAddress As String
    On Error Resume Next
    Set rst = Me.RecordsetClone
    If Me.FilterOn Then
    rst.Filter = Me.Filter
    End If
    rst.MoveFirst
    Do While Not rst.EOF
    strEmailAddress = strEmailAddress & "," & rst!Email
    rst.MoveNext
    Loop
    If Not strEmailAddress = "" Then
    strEmailAddress = "mailto:" & Mid(strEmailAddress, 2)
    Application.FollowHyperlink strEmailAddress, , True
    End If
    Set rst = Nothing
    End Sub

    This code expects a reference to the Microsoft DAO 3.6 Object Library (in Tools | References...)

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

    Re: Multiple Email (2002)

    Sorry, I forgot to adapt the code before posing it (I tested it on a subform). Instead of Me.subSubTotal.Form just use Me. And I also used a wrong field name. I have corrected my reply.

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Hans:

    Absolutely poetic!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    If you don't mind, and considering e-mail etiquette, is there a (simple) way to force it to Bcc:, rather than the default To: ?
    (simple being a relative term)<img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Multiple Email (2002)

    According to MailTo Syntax, it should be possible, but I cannot make it work on my PC.

    However, this should work instead of Application.FollowHyperlink:

    DoCmd.SendObject Bcc:=strEmailAddress, EditMessage:=True

    You can also specify a subject, message etc.

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Email (2002)

    Mine either. . .syntax error.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 2 12 LastLast

Posting Permissions

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