Excel 2003 VB CODING
 

Can some one please look at the following and tell me why i'm getting a
mismatch error?

Set EmailAddr = Application.InputBox("Select Email Addresses, Click on the
Email Worksheet" & vbCrLf &
'"Hold down Contrl Key to select multiple addresses", Type:=8)
Destination = ""
For Each cell In EmailAddr
If Destination = "" Then
'Destination = cell
'Else
Destination = Destination & ";" & cell
'End If

Next cell

Excel 2003 VB CODING
 

Handle the error as below...

Sub Macro()

Dim myRange As Range

On Error Resume Next
Application.DisplayAlerts = False
Set myRange = Application.InputBox(Prompt:= _
"Select Email addresses", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True

If myRange Is Nothing Then
MsgBox "You cancelled the operation"
Exit Sub
Else
For Each cell In myRange
If cell.Text < "" Then Destination = Destination & ";" & cell
Next
Destination = Mid(Destination, 2)
End If


End Sub

--
Jacob


"Neil Holden" wrote:

Can some one please look at the following and tell me why i'm getting a
mismatch error?

Set EmailAddr = Application.InputBox("Select Email Addresses, Click on the
Email Worksheet" & vbCrLf &
'"Hold down Contrl Key to select multiple addresses", Type:=8)
Destination = ""
For Each cell In EmailAddr
If Destination = "" Then
'Destination = cell
'Else
Destination = Destination & ";" & cell
'End If

Next cell

Excel 2003 VB CODING
 

Thanks for the reply Jacob, however this is a strange one, columns A, B and C
are hidden and it says operation canceled when the email address is selected.
If i unhide he columns it works?

Thanks again.

"Jacob Skaria" wrote:

Handle the error as below...

Sub Macro()

Dim myRange As Range

On Error Resume Next
Application.DisplayAlerts = False
Set myRange = Application.InputBox(Prompt:= _
"Select Email addresses", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True

If myRange Is Nothing Then
MsgBox "You cancelled the operation"
Exit Sub
Else
For Each cell In myRange
If cell.Text < "" Then Destination = Destination & ";" & cell
Next
Destination = Mid(Destination, 2)
End If


End Sub

--
Jacob


"Neil Holden" wrote:

Can some one please look at the following and tell me why i'm getting a
mismatch error?

Set EmailAddr = Application.InputBox("Select Email Addresses, Click on the
Email Worksheet" & vbCrLf &
'"Hold down Contrl Key to select multiple addresses", Type:=8)
Destination = ""
For Each cell In EmailAddr
If Destination = "" Then
'Destination = cell
'Else
Destination = Destination & ";" & cell
'End If

Next cell