Can someone help incorporate these two codes together?

[melody.anne]

I have this code that should loop through a query and populate the "Send To" field based on selected values in list boxes:

Private Sub Command39_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("qryNames", dbOpenSnapshot)
 
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
            If IsNull(.Fields(2)) = False Then
                sToName = .Fields(2)             '2 is the field where e-mails are stored
                sSubject = "Invoice # : " 
                sMessageBody = " "
 
                DoCmd.SendObject acSendNoObject, , , _
                    sToName, , , sSubject, sMessageBody, False, False
            End If
            .MoveNext
        Loop
End With
 
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

I am not sure if it is working as my work computer will not allow me to set up an Outlook profile. They want to send mail using Lotus Notes 8.5, for which I have found this code:

[FONT=Courier New]Dim notesdb As Object[/FONT]
[FONT=Courier New]Dim notesdoc As Object[/FONT]
[FONT=Courier New]Dim notesrtf As Object[/FONT]
[FONT=Courier New]Dim notessession As Object[/FONT]
[FONT=Courier New]Set notessession = CreateObject("Notes.Notessession")[/FONT]
[FONT=Courier New]Set notesdb = notessession.getdatabase("", "")[/FONT]
[FONT=Courier New]Call notesdb.openmail[/FONT]
[FONT=Courier New]Rem make new mail message[/FONT]
[FONT=Courier New]Set notesdoc = notesdb.createdocument[/FONT]
[FONT=Courier New]Call notesdoc.replaceitemvalue("Sendto", strSupportEMail)[/FONT]
[FONT=Courier New]Call notesdoc.replaceitemvalue("Subject", "Problem Report")[/FONT]
[FONT=Courier New]Set notesrtf = notesdoc.createrichtextitem("body")[/FONT]
[FONT=Courier New]Call notesrtf.appendtext("Problem Report")[/FONT]
[FONT=Courier New]Call notesrtf.addnewline(2)[/FONT]
[FONT=Courier New]Rem attach Error Report doc[/FONT]
[FONT=Courier New]'s = ActiveDocument.Path + "\" + ActiveDocument.Name[/FONT]
[FONT=Courier New]Call notesrtf.embedObject(1454, "", strCurrentPath, "Mail.rtf")[/FONT]
[FONT=Courier New]Rem send message[/FONT]
[FONT=Courier New]Call notesdoc.Send(False)[/FONT]
[FONT=Courier New]Set notessession = Nothing[/FONT]

My question being, how can I incorporate these together, so that instead of Outlook, my e-mails are sent through Lotus Notes but loop and select e-mail addresses using something like the first code?

 

[CJ_London]

in principle either

1. replace the code in your If IsNull(.Fields(2)) section with the other code

or

2. put the other code in a new sub or function and call it in your if section

in either case you will need to potentially replace certain field names e.g. replace strSupportEMail with fields(2).

Your code would be a lot clearer if you used names that meant something e.g.

field(2) could be field("emailaddress") or simply !emailaddress

 

[melody.anne]

in principle either

1. replace the code in your If IsNull(.Fields(2)) section with the other code

or

2. put the other code in a new sub or function and call it in your if section

in either case you will need to potentially replace certain field names e.g. replace strSupportEMail with fields(2).

Your code would be a lot clearer if you used names that meant something e.g.

field(2) could be field("emailaddress") or simply !emailaddress

I replaced it and it seems to be working! Thank you! (Don't have a lotus account so not 100% sure)

Do I have to code something else if I wanted the program to login automatically, or would it do that after I log in for the first time?

Edit: I have three listboxes. How could I validate so that I only select items from one list box at a time?

 

[CJ_London]

Do I have to code something else if I wanted the program to login automatically, or would it do that after I log in for the first time?

Sorry - no idea

Edit: I have three listboxes. How could I validate so that I only select items from one list box at a time?

Sorry - don't understand, please provide more information

 

[melody.anne]

Sorry - don't understand, please provide more information

Okay, I have one table that has the fields ID, Name, Email, Office & Category
From this I made three listboxes from which I can choose people to send the emails to. The one in the code is from a listbox that contains everyone's name, and you select from there and send the email.

I have another two, one of them you select the category and from that category it should send the email to all the people within that category, and same goes for Office.

They are all in the same page, and I want to avoid people clicking a category and an office simultaneosly to avoid people receiving duplicate emails.

This is what I want to validate. Something that maybe pops up a dialog bog that says something like "you may only select from one list at a time."

 

[CJ_London]

I would be inclined to control the situation by only allowing one selection - so in the afterupdate event of each listbox, simple reset the other listboxes to null - you could have a warning label on the form saying something like 'you can only select from one listbox'