In my Excel File I have a CommandButton which sends an E-Mail if cells E1, K2 and K5 are filled.
Unfortunately my code does not really work.
Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim nameList As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error GoTo cleanup
If Sheets("Sheetname").Range("E1").Value = "" Or _
Sheets("Sheetname").Range("K2").Value = "" Or _
Sheets("Sheetname").Range("K5").Value = "" Then
MsgBox "The Cells E1, K2 and K5 has to be filled!"
Else
MsgBox "All Cells are filled, send E-Mail now"
For i = 2 To 22
If Sheets("efforts and risks").Range("BB2").Value <> "" Then
nameList = nameList & ";" & Sheets("efforts and risks").Range("BB" & i).Value
End If
Next
With OutMail
.To = nameList
.Subject = "subject line"
.Body = "Body Text"
.Send
End With
End If
cleanup: Set OutApp = Nothing End Sub
If the cells are filled Excel shows the MsgBox, but it does not send the mail. Which part of my code do I have to change?
Hope someone can help me. Thanks a lot
Aucun commentaire:
Enregistrer un commentaire