Sending meeting request to Multiple Email Addresses

[Dougalsworld]

Hi Guys,

I'm hoping I can pick someones brains to solve what I think is probably a simple problem.

I am building a system that arranges training for people, and when we assign them to a course they are automatically sent a meeting request through outlook.

Now I've got all the tricky stuff working and I think this is my final hurdle...

My problem comes when trying to send to more than one contact. Currently I have the email contact list as a listbox on my form. I've looked across the forum and I've found instances were people have done something similar with standard email but I can't seem to adapt these for my use so I'm hoping by posting the code I'm using someone may have an idea

Private Sub Command21_Click()

' Save record first to be sure required fields are filled.
DoCmd.RunCommand acCmdSaveRecord
' Provide warning if meeting request already sent.
If Me!ReqSent = True Then
MsgBox "A meeting request has already ben issued"
Exit Sub

' Add a new meeting.
Else
Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Set outobj = CreateObject("outlook.Application")
Set outappt = outobj.CreateItem(olAppointmentItem)
With outappt
.Start = [txtCourseDate] & " 09:00"
.Duration = [txtDuration]
.Subject = [txtSubject]
.MeetingStatus = olMeeting
Set myRequiredAttendee = .Recipients.Add(Me!lbxAddresses)
myRequiredAttendee.Type = olRequired
If Not IsNull("test") Then .Body = [txtBody]
If Not IsNull("BDU") Then .Location = _
"BDU"
.Recipients.ResolveAll
.Display
.Send
.Save
End With
End If
' Release the Outlook object variable.
Set outobj = Nothing
' Set the AddedToOutlook flag, save the record, display a message.
Me!ReqSent = True
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Appointment Added!"
Exit Sub
AddAppt_Err:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Exit Sub
End Sub

Any help would be greatly appreciated

 

[Mutdogus]

Your code works for me.

Set myRequiredAttendee = .Recipients.Add(Me!lbxAddresses)

me!lbxAddresses should be me!lbxAddresses.value to get the value.

Make sure you are seperating the email addresses with a ; and you are golden.

 

[Dougalsworld]

Thanks for the info,
Unfortunately on making this change I get an error message
"Invalid use of a null"

I should also add the list bos has the mails listed like:
email1@address.com
email2@address.com
email3@address.com

and not as a string, is this the problem?

 

[Dougalsworld]

OK discovered my problem is that I really don't want to be using a listbox...

so going back to the original code above, is it possible for me to pull a set of email addresses from a seperate query? e.g. all a query for the email addresses of all the participants of a particular training session (could be between 8-30 email addresses in a query result

I suppose my question is how do I take a list
mail1
mail2
mail3

and change it to:
mail1; mail2; mail3

so I can use it in my code above, any ideas?
Cheers

 

[Mutdogus]

The way I do it is as you have described.

I create an SQL statement and open it in an ADO recordset.

Then I loop thru each record something like this.

dim EmailAdd as string
do until rs.eof
    EmailAdd = rs.fields("EmailAddress").value
    Set myRequiredAttendee = .Recipients.Add(EmailAdd)
    rs.movenext
loop

 

[Dougalsworld]

I'm afraid I have to play dumb here, creating an SQL statement is easy enough, but I'm never quite sure what i should be doing with record sets...

 

[Mutdogus]

There are many other examples on this forum of how to work with a recordset.

Just remember that you only get one row at a time, and you have to loop thru it to get all of your email addresses.

Here's one I gave just yesterday.

http://www.access-programmers.co.uk/forums/showpost.php?p=768044&postcount=2