Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    5

    Unanswered: Sendobject question - dynamic addresses

    Does anyone know of a good tutorial or a posted solution to do the following?

    I want to send an email (via sendobject cmd) only to those people who are included on my report. These names will change from week to week. Can this be automated?

    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Sendobject question - dynamic addresses

    Originally posted by Bluezman
    Does anyone know of a good tutorial or a posted solution to do the following?

    I want to send an email (via sendobject cmd) only to those people who are included on my report. These names will change from week to week. Can this be automated?

    Thanks
    Function sendMyTable()
    Dim ToStr$

    ToStr$ = "To@aol.com; Also@aol.com; Another@aol.com"
    ToStr$ = Me!To1 & "; " & Me!To2 & "; " & Me!To3

    'SendObject Method format
    'DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]

    DoCmd.SendObject acSendTable, "MyTable", acFormatTXT, ToStr$, , , "Data Transfer", "Attached is MyTable", False

    End Function

  3. #3
    Join Date
    Apr 2003
    Posts
    5
    Robt917

    Thanks for your reply. I built a test form and populated a couple of controls with Email addresses. I used your example above and it worked great.

    On the Function example you provided....is this line really necessary?

    <<ToStr$ = "To@aol.com; Also@aol.com; Another@aol.com">>

    I used it on my test Function...and didn't really bother to change what you had and everything seemed to work. Of course, these addresses don't correspond to the actual addresses I'm using. Can this line be deleted?

    I really appreciate your help...i was dreading tackling this, but with your help, it was a piece of cake!

    a BIG Thank You!

  4. #4
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67
    Originally posted by Bluezman
    Robt917

    Thanks for your reply. I built a test form and populated a couple of controls with Email addresses. I used your example above and it worked great.

    On the Function example you provided....is this line really necessary?

    <<ToStr$ = "To@aol.com; Also@aol.com; Another@aol.com">>

    I used it on my test Function...and didn't really bother to change what you had and everything seemed to work. Of course, these addresses don't correspond to the actual addresses I'm using. Can this line be deleted?

    I really appreciate your help...i was dreading tackling this, but with your help, it was a piece of cake!

    a BIG Thank You!

    No, delete it. It was an example of how the email address should look.


  5. #5
    Join Date
    Apr 2003
    Posts
    5
    Robt917

    This function is working really well. But, I did run into a small problem.

    I'm basically emailing 13 different people...and most of these names will change from week to week.....because of work schedules. If the person filling out the form fails to enter a name for a certain control, then the email fails to send.

    I have used traps before, where it makes the user go back and fill out the field that was left blank.....but, it would be nice if the email function would work even if one or more names were left out.

    Do you (or anyone else) know of a way around this?

    Thanks Robt917 for your help!

    btw....I sent you a pvt msg...please ignore it, as I've solved that problem. Creating Macros and then converting them into Modules...is a great way to learn about writing Functions......cool tool!

  6. #6
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67
    Originally posted by Bluezman
    Robt917

    This function is working really well. But, I did run into a small problem.

    I'm basically emailing 13 different people...and most of these names will change from week to week.....because of work schedules. If the person filling out the form fails to enter a name for a certain control, then the email fails to send.

    I have used traps before, where it makes the user go back and fill out the field that was left blank.....but, it would be nice if the email function would work even if one or more names were left out.

    Do you (or anyone else) know of a way around this?

    Thanks Robt917 for your help!

    btw....I sent you a pvt msg...please ignore it, as I've solved that problem. Creating Macros and then converting them into Modules...is a great way to learn about writing Functions......cool tool!
    You will get several different answers on how to do this. One will seem easier to you than others based on your use of Access tools.

    Here is one:

    Make a table or query with [email address] and [emailee's names]

    Place a listbox on your form with this new table/query as the content.
    make sure that the multiselect property is set.

    Within your send command add this to compile the ToStr list.

    dim i as variant, ToStr$

    For Each i In me!listboxOfEmailees.ItemsSelected
    Tostr = iif(ToStr = "","",ToStr &";") & me!listboxOfEmailees.itemdata(i)
    next

  7. #7
    Join Date
    Apr 2003
    Posts
    5
    I see how this solution would work, but it appears that I would have to do a major makeover of my form page. I'm assuming you would use the list box to select the names.

    Right now, I'm using combo boxes that are keyed to job assignments related to an Incident Command system. Each person selected has to be associated with a job assignment. Not sure how that could happen with the use of a single list box with multiple selections.

    You mentioned there are several other methods. Can you think of one that would be compatible with my current form?

    As always, thanks!

  8. #8
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67
    Originally posted by Bluezman
    I see how this solution would work, but it appears that I would have to do a major makeover of my form page. I'm assuming you would use the list box to select the names.

    Right now, I'm using combo boxes that are keyed to job assignments related to an Incident Command system. Each person selected has to be associated with a job assignment. Not sure how that could happen with the use of a single list box with multiple selections.

    You mentioned there are several other methods. Can you think of one that would be compatible with my current form?

    As always, thanks!
    I would assume that an email does not go out if the combobox is empty?

    If so:

    Dim ToStr$

    if len(trim(Me!cboAssignment_1)) > 0 then ToStr$ = Me!assignment_1.column(2)

    if len(trim(Me!cboAssignment_2)) > 0 then Tostr$ = iif(Tostr$="","",ToStr$ & ";" & Me!assignment_2.column(2)

    if len(trim(Me!cboAssignment_3)) > 0 then Tostr$ = iif(Tostr$="","",ToStr$ & ";" & Me!assignment_3.column(2)
    '...
    if len(trim(Me!cboAssignment_13)) > 0 then Tostr$ = iif(Tostr$="","",ToStr$ & ";" & Me!assignment_13.column(2)

    You will probably need to write a query for the comboboxes to include the email address.
    The Email Address Column replaces the "2" (column count -1)

Posting Permissions

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