newbie

dholcomb · August 14th, 2004, 10:04 PM

Hi,

Actually I have been using VBA to control my access applications for years, but I haven't been able to figure out how to do what I would suspect is a very SIMPLE thing. I don't have any hair left to pull out: Help!

So I have this command-button on a form, which creates a new table based on a lot of information I select on that form. I also have a query based on this table which gets rid of unnecessary duplicate information.

I want to create a (somewhat lengthy) string from the query based on two fields ([Person] & [e-mail]) for all of the records in the query.

Is it obvious where I am going with this? I can open my e-mail client, and send a message using VBA, I just can't figure out how to get the addresses I want into the send field.

Thanks for any help!

BrianB · August 16th, 2004, 03:19 AM

Something like this :-

Code:

    '- RECIPIENTS
    For rw = 1 To Addresses.Rows.Count
        If Addresses.Cells(rw, 1).Value <> "" Then
            MailRecipient = Addresses.Cells(rw, 2).Value
            MyRecipients.Add (MailRecipient)
        End If
    Next

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.

BrianB · August 16th, 2004, 03:23 AM

Sorry, left off this bit, where MyItem is the name I give to the MailItem :-

Code:

Set MyRecipients = MyItem.recipients

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.

dholcomb · August 16th, 2004, 09:44 AM

Thanks Brian. I have to admit that I am completely lost with what you are suggesting. I have been working along these lines:

Code:

            Dim cnn As ADODB.Connection

Code:

            Dim rst As ADODB.Recordset
            Dim varOutput As Variant
            Dim strQuery As String

            Set cnn = CurrentProject.Connection
            Set rst = New ADODB.Recordset
            strQuery = "SELECT person, e-mail " & _
                        "FROM [Export Table e-mail Query]"

            rst.Open strQuery, cnn, adOpenStatic, adLockReadOnly, adCmdText

            If rst.RecordCount > 0 Then
            'Use all defaults: get all rows, TAB column delimiter, CARRIAGE RETURN
            'row delimiter, empty-string null delimiter
                varOutput = rst.GetString(adClipString)
                Debug.Print [Person] & " <" & [e-mail] & ">"
                Debug.Print varOutput
            Else
                Debug.Print "No names found to e-mail to" & vbCr
            End If

            rst.Close

I am using borrowed code here, and trying to adapt it to my needs (everyone does this, right?), but I can't get past the "rst.open" command. Ther error tells me that there is "No value given for one or more required parameters." I can't find sufficient help on these parameters. I'll admit that I don't really know what I am searching for, but I can't find anything. Once I get the rst opened, I was also going to try:

Code:

        Do Until rst.EOF
            If strTo <> "" Then
                strTo = strTo & ", "
                End If
            strTo = strTo & [Person] & " <" & [e-mail] & ">"
            rst.MoveNext
        Loop

Again more borrowed code modified for my use.

Thanks again for any help.