Thread: Excel: Add Mail Recipients by Checkbox selection, via Lotus Notes

Dear vba-Experts,

i am new to this forum and i would like to put in a question about vba-programming for excel.

I have got the following problem:

I would like to send an Excel workbook as an e-mail attachement via Lotus Notes, executed by clicking a CommandButton. This operation works fine so far.

BUT:
Since there might be more than one or two recipients for this mail the user of the wookbook should select multiple recipients by Checkboxes. The sheet contains 18 Checkboxes. Each Checkbox is linked ta a range of cells, with every cell containing one mail address.

The Checkboxes are on worksheet "Input Alert", while the recipients are listed in different coloumns of different lenghts in woorksheet "Recipientsdir" (e.g. A1:A10 for one set of recipients, B1:B15 for another set and so on...).

What I tried to do so far is putting an If-Function to the Checkboxes, which means if the Checkbox is TRUE add the recipients to the SENDTO field of Lotus.

Unfortunately, it happens to be that the code takes the last true Checkbox only, overwriting the previous TRUE cases. So, in my example I select the checkboxes for coloum A and B, only B is inserted into the SENDTO field.

Here's my first try: (in this example, only two single mail adresses are in A2 and A5)
-----------------------------------------------------------------------

Dim Recipient As Variant 
Dim rtitem As Object 
Dim EmbeddedObject As Object 
Dim Tosenden 'german for SendTo
Dim CCsenden 
Dim BCCsenden 
Dim Subject As String 
Dim Text As String 
Dim Cells As Range 
Dim Linkanhang As String 'german for attachement

'Impact 1 ist selected: 
Select Case Impact.Value 
Case "1" 
With Worksheets("Recipientsdir") 
Linkanhang = .Range("A1") 'reads a link in cell A1
Dateianhang = Linkanhang 'takes A1-link as attachement.

'********this is where my problem starts:
If ActiveWorkbook.Worksheets("Input Alert").SDE.Value = True Then 
Tosenden = .Range("A2") '.Resize(.Cells(100, 1).End(xlUp).Row) 
If ActiveWorkbook.Worksheets("Input Alert").SQE.Select Then 
Tosenden = .Range("A5") '.Resize(.Cells(100, 1).End(xlUp).Row) 
'If ActiveWorkbook.Worksheets("Input Alert").FB.Select Then 
' Tosenden = .Range("A2") '.Resize(.Cells(100, 1).End(xlUp).Row) 
'If ActiveWorkbook.Worksheets("Input Alert").FKZyl.Select Then 
' Tosenden = .Range("A2") '.Resize(.Cells(100, 1).End(xlUp).Row) 
'If ActiveWorkbook.Worksheets("Input Alert").FKPleul.Select Then 
' Tosenden = .Range("A2") '.Resize(.Cells(100, 1).End(xlUp).Row) 
etc. 
End If 
'**********************
Subject = .Range("A3") & (" Impact: 1") 
Text = .Range("A4") 
End With 

Dim SessionNotes As Object, NotesDB As Object, NotesDoc As Object 
Set SessionNotes = CreateObject("Notes.NOTESSESSION") 
Set NotesDB = SessionNotes.GetDatabase("", "") 
NotesDB.OPENMAIL 
If NotesDB.IsOpen = False Then 
MsgBox "Please log in!", vbInformation + _ 
vbOKOnly 
Exit Sub 
End If 
Set NotesDoc = NotesDB.CreateDocument 
With NotesDoc 
.Form = "Memo" 
.Subject = Betreff 
.sendto = Tosenden 
.copyto = CCsenden 
.blindcopyto = BCCsenden 
.body = Text 
.DeliveryReport = "B" 
.Importance = "1" 
.SAVEMESSAGEONSEND = True 
.ReturnReceipt = "1" 
.Sign = "1" 

If Trim$(Dateianhang) <> "" Then 
Const embed_ATT = 1454 
Set rtitem = .CREATERICHTEXTITEM("DATEIANHANG") 
Set EmbeddedObject = rtitem.EMBEDOBJECT(embed_ATT, "", Dateianhang, "DATEIANHANG") 
End If 

.SEND False 
End With 
Set SessionNotes = Nothing 
Set NotesDB = Nothing 
Set NotesDoc = Nothing 
Set rtitem = Nothing 
Set EmbeddedObject = Nothing 
End Select

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


I appologize for any german leftovers in the code! The problem really is with the If-function. Does someone know how to get this code ADDING all mail recipients, not overwriting them???
If you have trouble getting me let me know, please.

Thank you in advance for any help on this topic,
Have a nice Sunday

Holger

Hi McMareck
Welcome to excel forum
Possibly ... you need

If ActiveWorkbook.Worksheets("Input Alert").SDE.Value = True Then
Tosenden = Tosenden & .Range("A2") '.Resize(.Cells(100, 1).End(xlUp).Row)
 End If
 If ActiveWorkbook.Worksheets("Input Alert").SQE.Select Then
Tosenden = Tosenden & .Range("A5") '.Resize(.Cells(100, 1).End(xlUp).Row)
End If