Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81

    Thumbs up Unanswered: SendObject to all records in filtered form

    Happy to have solved the problem of sending a mail from my default mail program by a click of a button to ONE contact.

    (Simple Macro w/ SendObject, all fields empry but the "To" Field Where I simply put a =[email] expression. For anyone else who has the same problem I had before)

    Strange, though, that Microsoft haven´t made a special mail mask or something from the start. Not a very unique need to want to be able to mail a contact with a simple click of a button.

    However - I would like to extend this feature. I would like to create a macro that sends the same mail to ALL my contacts. Filtered form or nonfiltered form.

    In other words to the [email]field in every record.

    I figure I need to extend the "To"=[email] expression to some kind of:

    [email] from rec 1, [email] from rec 2, [email] from rec 3....[email] from rec N - expression

    with a semicolon in between every mail address so the mailprogram treats the mail-list right.

    Does any helpful soul know the correct syntax for this expression? Can it be done?
    Or do I have to set up some kind of loop, copy [email] from every record in to a field - say [MailContainer] and then set the SendObject "To" field to =[MailContainer].

    Best Regards

    Johnny

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Yes, build a string of eMail addresses in a loop. It'll take five minutes to program and will run in a millisecond.

  3. #3
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81

    Wink

    Thanks for the hint. Now I know in what direction to trial and error. I have the logics of it all figured out. Simple loop, collect address, etc. Prob is I don´t know the syntax. Newbie to VBA, access etc.

    Second prob is that I am in Thailand. They only have literature in Thai... I´ll learn that language too eventually. But that will probably take a lot longer .

    Jo´

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Do you know how to open a recordset in DAO?

    Code:
    Dim rs as RecordSet
    Dim SendString as string
    
    Set rs=CurrentDB().OpenRecordSet(NameOfQueryOrTableWithEMailAddresses)
    
    SendString = ""
    Do Unti rs.EOF
         SendString=SendString & rs(NameOfEMailAddressFieldFromQueryOrTable) & "; "
         rs.MoveNext
    Loop
    
    SendString=left$(SendString,len(SendString)-2)
    
    Set rs = Nothing

  5. #5
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    Not really, mostly been playing around in the macroenvironment. However I hooked it up to a command button to see what it did.

    Having problems w/ the orange line:

    Private Sub Command261_Click()
    Dim rs As Recordset
    Dim SendString As String

    Set rs = CurrentDb().OpenRecordset(FILTERPAGE)


    SendString = ""
    Do Until rs.EOF
    SendString = SendString & rs(PersonalEmail) & "; "
    rs.MoveNext
    Loop

    SendString = Left$(SendString, Len(SendString) - 2)

    Set rs = Nothing

    Exit_Command261_Click:
    Exit Sub

    Err_Command261_Click:
    MsgBox Err.Description
    Resume Exit_Command261_Click

    End Sub

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Where you have FILTERPAGE, it must be the name of a table, or query.

    If FILTERPAGE is the name of a table, or query, you need to put it in quotes, otherwise the code thinks its a variable.

  7. #7
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    Thanks,
    Boy am I glad I found this place. Thailand is nice but talk about being isolated. No books in English, no collegues, no one anywhere to ask.

    Anyways...

    FILTERPAGE is a query.

    I changed the line like this:
    Set rs = CurrentDb().OpenRecordset = "FILTERPAGE"

    But now this line went yellow instead:

    Private Sub Command261_Click()

    And when I try to run it it says:

    Compile Error:
    Argument not optional.

    I tried removing the private bit, from "private sub" into just "sub".

    No protests, however nothing happens.

    Sorry for being such a dummie,

    Jo´
    Last edited by Johnny Dove; 04-08-02 at 11:44.

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    No, just use your original sytax and put it in quotes.

    You had:

    Set rs = CurrentDb().OpenRecordset(FILTERPAGE)

    To put it in quotes means:

    Set rs = CurrentDb().OpenRecordset("FILTERPAGE")

    NOT

    Set rs = CurrentDb().OpenRecordset = "FILTERPAGE"

  9. #9
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    Can´t make it fly.
    Now I get: type mismatch on this line:

    Set rs = CurrentDb().OpenRecordset("FILTERPAGE")

    Complete code looks like this now:

    ------------
    Private Sub Command261_Click()


    Dim rs As Recordset
    Dim SendString As String

    Set rs = CurrentDb().OpenRecordset("FILTERPAGE")

    SendString = ""
    Do Until rs.EOF
    SendString = SendString & rs("PersonalEmail") & "; "
    rs.MoveNext
    Loop

    SendString = Left$(SendString, Len(SendString) - 2)

    Set rs = Nothing


    End Sub
    ------------------

    Also tried opening the recordet in the way access help suggests. But I get

    Compile Error:
    User-defined type not defined.

    This code looks like this:

    -----------------
    Private Sub opendaorssetbutton_Click()


    Dim db As Database
    Dim SendString As String

    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(“FILTERPAGE”)

    SendString = ""
    Do Until rs.EOF
    SendString = SendString & rs("PersonalEmail") & "; "
    rs.MoveNext
    Loop

    SendString = Left$(SendString, Len(SendString) - 2)

    Set rs = Nothing


    End Sub

    --------------------

    Shall I have the PersonalEmail field as above, also in quotes?

    The repeated funtion, SendString, what does it do? I figure it sends the contents of the field somewhere as a string. But where will they land? Do I need to set a destination field?

    Furthermore I need to be able to massmail to contacts that have been filtered. Can I get the records from a form instead of a table or query?

    Thanks for your time

    Jo'
    Last edited by Johnny Dove; 04-09-02 at 03:05.

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    First, let's get past your recordset problem.

    Is FILTERPAGE the name of a table or query?

  11. #11
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    FILTERPAGE is a query. Have also tried to hook it up to the table like this:

    ------------------------
    Private Sub PersonalEmail_DblClick(Cancel As Integer)


    Dim rs As Recordset
    Dim SendString As String

    Set rs = CurrentDb().OpenRecordset("TblCONTACTS")


    SendString = ""
    Do Until rs.EOF
    SendString = SendString & rs("PersonalEmail") & "; "
    rs.MoveNext
    Loop

    SendString = Left$(SendString, Len(SendString) - 2)

    Set rs = Nothing


    End Sub
    -------------

    Same result:

    Run Time Error ´13´
    Type Mismatch


    I run Access 2000 btw

    Jo

  12. #12
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    Managed to solve it!

    Complete code looks like this:

    _______________

    Option Compare Database
    Public SendString As String
    _______________

    Function EmailCONTACTS()


    Dim rstYourContactTableOrQuery As New ADODB.Recordset


    'Open the Recordset to Customers
    rstYourContactTableOrQuery.ActiveConnection = Application.CurrentProject.Connection
    rstYourContactTableOrQuery.CursorType = adOpenKeyset
    rstYourContactTableOrQuery.Open "YourContactTableOrQuery "


    SendString = ""

    If MsgBox("Send Email to all Contacts" & Chr(13) & _
    "using Deafault Mapi Mailer?", 4) = 6 Then

    With rstYourContactTableOrQuery


    Do Until .EOF


    SendString = SendString & !EmailFieldName & ";"
    .MoveNext
    Loop

    SendString = Left$(SendString, Len(SendString) - 2)


    DoCmd.SendObject , "", "", [SendString], "", "", "", "", True, ""

    End With
    End If

    rstYourContactTableOrQuery.Close

    End Function

    _____________

    Thanks for your help! You got me on the right track. Your sendstring loop worked. Only had to remove the space after the semi-colon.

    Jo´

    PS Now to the next phase. How on earth do I get a recordset from a user-custom-filtered form.? I´ll start up a new thread since this is an other issue that can be used for other purposes. DS

  13. #13
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67

    Question

    Solution to your stuff is:

    Dim rstYourContactTableOrQuery As New ADODB.Recordset
    dim strSQL as string

    'Open the Recordset to Customers
    strSQL = "SELECT * FROM " & "YourContactTableOrQuery " _
    & "WHERE YOURFIELD1 =" & CRITERIA1 _
    & " AND YOURFIELD2 = " & CRITERIA2 _
    & " AND YOURFIELD3 =" & CRITERIA3

    in case you are using strings, then replace with
    & " AND YOURFIELD3 ='" & CRITERIA3 & '" _


    rstYourContactTableOrQuery.ActiveConnection = Application.CurrentProject.Connection
    rstYourContactTableOrQuery.CursorType = adOpenKeyset
    rstYourContactTableOrQuery.Open strSQL

    SendString = ""


    btw, are you able to send a few files being attached to the mail that you are trying to send,

    I need to send a file to the users based on a daily event. would like to do a bit of automation on this since at presnt do it manually WEEKLY.
    got any clue, all help would be appriciated.

    BTW I use Access 2000 as DB and Lotus Notes Client as Mail Server and MS Access does not recoginse the Lotus client as they are not MAPI.

  14. #14
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    Your Q,

    If I allow edit message, as I have done in this exemple(the option set to true):

    DoCmd.SendObject , "", "", [SendString], "", "", "", "", True, ""

    The mail will pop up in Outlook, with the address list from my contacts in the right place. Then I can just add the attachment, press send, and of it goes to everyone.

    If you wanna automate it completely, send it off by the date and time function. You don´t seem as free to choose file. You can only chose objects inside access. Thereofore you could make a report and have that sent off by the clock. The first option in the sendobject cmd is Object Type(report, form, etc), the second is Object Name, the third is Output format (rtf, xls etcl?) and the fourth is the mails "to" field. Have a look at the cmd in macro mode.

    However it does seem to be only for MAPI. Probably for Outlook in particular (if I know Microsoft...). But I have seen a lot of loooong codesnippets out there. Don´t know if there is any workarounds using the SendObject cmd specifically. But maybe some other command that acheives the same thing.

    Jo´

  15. #15
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81

    My Q

    My Q

    Thanks for your help. However I am not sure it is what I need. Maybe I have missunderstood. Doesn´t this exemple make the filtering parameters set once for all? Would I not get the same result if I made a query and set that as recordsource? I do not know exactly what the marketing people want to filter. Furthermore the form has about 40 different productinterest checkbuttons. I must let them do the filtering themselves in the form. Then just let them press send to shoot to whomever has shown up in their selections.

    I figure I need a formloop instead. Not a recordsource loop. But don´t know how. Or otherways I need to export the current filtered form to a query or table and use that as recordset. Or even - use the query that Access makes itself when someone filters in a form. But I don´t know how to get that.

Posting Permissions

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