lundi 28 septembre 2020

Use interior in cells to click and unclick e-mail adresses in a specific range. Then store this e-mail adres in a string and use it as recipiant vba

I'm a beginner in vba and i got a question about it. In range a10 till a14 there are names. These names all have a specific e-mail adres like in my code below. A10 = a A11 = b A12 = c and so on. Names and e-mail adresses are fakes ofcourse.

I want to loop through the cells a10 till a14 and then if the interior color of the cells are code 43 then it need to store the string of the e-mail adres in the string "namen" .So if i click A10 and A12 string a and c should be in the string "namen"

Now it only shows string A in my e-mail recipiants.


Sub mailen()
Dim a As String
Dim b As String
Dim c As String
Dim namen As String
Dim inhoud As String


a = "brrr@hotmail.com;"
b = "grrr@hotmail.com;"
c = "crrr@hotmail.com.com;"


If Range("A10").Cells.Interior.ColorIndex = 43 Then
namen = a
ElseIf Range("A10").Cells.Interior.ColorIndex = 43 And Range("A11").Cells.Interior.ColorIndex = 43 Then
namen = a & b
Else: MsgBox "Geen namen geselecteerd"
   End If
    With CreateObject("Outlook.Application").createitem(0)
    inhoud = "Hoi Vincent"
    .to = namen
    .Subject = "Test"
    .body = inhoud
    .attachments.Add ThisWorkbook.FullName
    .display
    End With
End Sub

Aucun commentaire:

Enregistrer un commentaire