Multiple Email Addresses in Lotus Notes through Access Query

[KeithIT]

I am trying to create a module whereby access can automatically send an emila through Lotus Notes. I have a query set up to return all the email addresses for a specific subset of participants in our program, however I have not been able to get access to dump all of these addresses into the sendto field as a large group (right now it is sending an individual email to each participant who fits the criteria). Is it possible to have access combine all of the results into one group and then send one email out to everyone rather than individual emails?

 

[TKnight]

Post your code and i'll have a look. Have done this many times with outlook and groupwise but not notes. If you're using a query I assume you're accessing the addresses as a recordset. Are you then feeding them as strings or building an array?

Tom.

 

[KeithIT]

I'm new to VB code but experienced with Access. This is based on code I found here on the forum, with a little tweaking (which could very well be the problem). I have removed the parts relating to attachments because the emails being sent from this database never have attachments, although I'll probably put them back in just to make it possible in the future. Here is what I have so far:

Private Sub cmdSendEmail_Click()

    Dim nSession As Object
    Dim CurrentUser As String
    Dim DataBaseName As String
    Dim nDatabase As Object
    Dim nMailDoc As Object
    Dim nSendTo(120) As Variant
    Dim stLinkCriteria As String
    Dim i As Variant
    Dim ctl As Control
    Dim z As Integer

    Me.Visible = False
    DoCmd.OpenForm "frmSendList", , , stLinkCriteria
    
    z = 0
    For Each i In ctl.ItemsSelected
        nSendTo(z) = "[chrEmail]"
        z = z + 1
    Next i
            
    Set nSession = CreateObject("Notes.NotesSession")
    CurrentUser = nSession.UserName
    DataBaseName = Left$(CurrentUser, 1) & Right$(CurrentUser, _
         (Len(CurrentUser) - InStr(1, CurrentUser, " "))) & ".nsf"
    
    Set nDatabase = nSession.GETDATABASE("", DataBaseName)
    Call nDatabase.OPENMAIL

    
    Set nMailDoc = nDatabase.CREATEDOCUMENT
    With nMailDoc
             .Form = "Memo"
             .Body = "body goes here"
             .SendTo = nSendTo
             .Subject = "Subject of e-mail"
             .Importance = "0"
             .Send (False)
    End With

    Set nDatabase = Nothing
    Set nMailDoc = Nothing
    Set AttachME = Nothing
    Set nSession = Nothing
    Set EmbeddedObj = Nothing

End Sub
[CODE][/QUOTE]

I have taken the query and displayed the names in a list box on a form. I'm sure there is a better way to do this, but as I've just started using VB code I'm afraid I'm not able to create very sophisticated modules. Also, I believe that I have set this up as a sub procedure rather than a module, probably not the best way to do this, huh?

Thanks in advance for your help!

 

[TKnight]

I'm not 100% sure on this but you might have a problem defining nSendTo as a variant. When you assign multiple values to a variable it becomes an array and you specify the number of values that it will hold in the brackets (in your case 120)

Try

Dim nSendTo(120) As string

instead

Also, if the user isn't going to select exactly 120 recipients each time it may cause problems. It might be a good idea to set the paramater of the array like this;

intRecipients = ctl.ItemsSelected.Count
Dim nSendTo(intRecipients) As String

Hope this helps,

Tom

 

[KeithIT]

Constant Expression required

When I try to compile the code I'm getting an error that says "Constant expression required" and highlights the part of the code

Dim nSendTo([B]intRecipients[/B]) As String

Should I be setting up inRecipients as a constant integer prior to defining it as ctl.ItemsSelected.Count?

I looked into the variants deal and you are absolutely correct on that, thank you.

Thanks for your help!

 

[TKnight]

Yeah I had the same problem, VB seems to have a problem in defining an array straight off with a variable. You have to define the array and then re-define it (Redim) with the variable. This is from Access help:

ReDim Statement Example
This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1.

Dim MyArray() As Integer ' Declare dynamic array.
Redim MyArray(5) ' Allocate 5 elements.
For I = 1 To 5 ' Loop 5 times.
MyArray(I) = I ' Initialize array.
Next I

The next statement resizes the array and erases the elements.

Redim MyArray(10) ' Resize to 10 elements.
For I = 1 To 10 ' Loop 10 times.
MyArray(I) = I ' Initialize array.
Next I

The following statement resizes the array but does not erase elements.

Redim Preserve MyArray(15) ' Resize to 15 elements.

That should solve your problem. Take note of the option base statement outlined here. If you have option base set to 1 you need to start your counter at 1. Otherwise you will be fine with your code.

Hope that solves it...

Tom

P.S. Read Mile-O-Philes VBA Programming Practises in the FAQ section. It's well worth it!

 

[KeithIT]

Well that solved the array problem.

Thanks so much for your help!!

Where do I find this Mile-O-Phile document you were talking about? The FAQ section of...?

Thanks again, hopefully I can return the favor sometime.

 

[TKnight]

The FAQ section of this forum. There is a link to it in the menu bar above.

It's well worth trawling through there and digesting all the good bits... and the code repository for that matter. Some of the people that contribute on this forum seriously know their stuff and I guarantee you'll find loads of useful titbits, most of them with demo DB's which always helps

Glad it solved the problem.

Tom