VBA - Net send form

I've created a script that allows us to access net send through excel, (due to some problems with Outlook at work we needed something that allowed us (in IT) to communicate to everyone, any updates etc..)

Well it works but not as smoothly as I’d like. when I select a the group of users I'd like to send a message too, it's works fine, but then If I select another user group it doesn't send, it just displays a net send result, until I exit out of the excel file and re-enter, very annoying if you want to send a message to multiple user groups.

Any help would be greatly appreciated

Code:

---

Private Sub cmdSend_Click()
On Error GoTo ENDNOW

'Unload frmnetsend

Dim objShell As New WshShell
Dim objExec As WshExec
Dim strComputer As String
Dim strMessage As String
Dim ReadAll As String
Dim Error As String
Error = ""

If Me.cmbNames.Value = "" Then
    Error = Error & "Please Select a group" & Chr(13)
    Me.cmbNames.SetFocus
    MsgBox Error, vbInformation
End If

strMessage = txtmessage

If cmbNames.Value = "Testgroup" Then

    Sheets("USER GROUPS").Select
    Range("Testgroup").Select
    ElseIf cmbNames.Value = "ITTEAM" Then
    Range("ITTEAM").Select
    ElseIf cmbNames.Value = "SeniorLegalSec" Then
    Range("SeniorLegalSec").Select
    ElseIf cmbNames.Value = "SMT" Then
    Range("SMT").Select
    ElseIf cmbNames.Value = "AccidentClaims" Then
    Range("AccidentClaims").Select
    ElseIf cmbNames.Value = "Lawyers" Then
    Range("Lawyers").Select
    ElseIf cmbNames.Value = "ALLSTAFF" Then
    Range("AllSTAFF").Select
   
End If

Do While ActiveCell.Value <> ""

    strComputer = ActiveCell.Value


  Set objExec = objShell.Exec("Net Send " & strComputer & " " & strMessage)


        Do While Not objExec.StdOut.AtEndOfStream
        ReadAll = objExec.StdOut.ReadAll
        Loop

    ActiveCell.Offset(1, 0).Select
Loop

Range("B2").Select

MsgBox ReadAll, vbInformation, "Net Send Results"
ENDNOW:
End Sub

Private Sub UserForm_Initialize()
With cmbNames
    .AddItem "Testgroup"
    .AddItem "IT Team"
    .AddItem "Senior Legal Secretaries"
    .AddItem "SMT"
    .AddItem "Accident Claims"
    .AddItem "Lawyers"
    .AddItem "All Staff"
End With

End Sub

---

put something between endnow: and exit sub to determine your error

as you have nothing to handle any error the sub will just exit,
for testing purposes just take out the on error goto line then the line which errors should highlight

also you can probably take out the long if /elseif and replace with
Range(cmbnames.value).Select

i would also try to avoid the use of select and selection, just work with the range like

vb Code:

for each mycell in range(cmbnames.value)
   'send message
next

Cheers Fella,

Although, it's really strange, it works fine for some groups and then skips a load of script for others, like when I ran it through on Testgroup it sent the message everytime, but then I tested it on Accident claims the cell was selected but VB desided to skip the below

Code:

---

Do While ActiveCell.Value <> ""

    strComputer = ActiveCell.Value


  Set objExec = objShell.Exec("Net Send " & strComputer & " " & strMessage)


        Do While Not objExec.StdOut.AtEndOfStream
        ReadAll = objExec.StdOut.ReadAll
        Loop

    ActiveCell.Offset(1, 0).Select
Loop

---

Anyone know why?

what is activecell.value at that point?

vb Code:

strComputer = ActiveCell.Value
msgbox strcomputer

as you are using named ranges are you sure the range has not been modified at all, to include a blank cell