BUTTON "O.K."-CANCEL

[verluc]

I have a macro with the following line:
Dim olApp As Object, olMail As Object
Dim rngeAddresses As Range, rngeCell As Range
Set olApp = CreateObject("Outlook.Application")
Set rngeAddresses = Application.InputBox(prompt:="Give a text"
For Each rngeCell In rngeAddresses.Cells
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = rngeCell.Value
I can use the O.K. button,but by press the CANCEL-button,I receive an errormessage on the line : Set rngeAddresses.....
Can somebody give me a solution ?
Many thanks.

 

[Ivan F Moala]

Try;

<pre/>
Dim olApp As Object, olMail As Object
Dim rngeAddresses As Range, rngeCell As Range

Set olApp = CreateObject("Outlook.Application")

On Error Resume Next
Set rngeAddresses = Application.InputBox(prompt:="Give a text", Type:=8)
If Err Then Exit Sub
On Error GoTo 0

For Each rngeCell In rngeAddresses.Cells
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = rngeCell.Value
Next
</pre>

 

[verluc]

On 2002-04-29 00:11, Ivan F Moala wrote:
Try;

<pre/>
Dim olApp As Object, olMail As Object
Dim rngeAddresses As Range, rngeCell As Range

Set olApp = CreateObject("Outlook.Application")

On Error Resume Next
Set rngeAddresses = Application.InputBox(prompt:="Give a text", Type:=8)
If Err Then Exit Sub
On Error GoTo 0

For Each rngeCell In rngeAddresses.Cells
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = rngeCell.Value
Next
</pre>

Hi Ivan,

Still a little question.
In this macro I must filled in a range:
exeample : A5 : A20

Is it possible that this macro can filled in automatickly the range.
The range is from A5 to the last row of column A.So I must not each time filled in manualy the range and I still have the possibility to change the range.
Thank for your time.

 

[Ivan F Moala]

On 2002-04-29 02:29, verluc wrote:

On 2002-04-29 00:11, Ivan F Moala wrote:
Try;

<pre/>
Dim olApp As Object, olMail As Object
Dim rngeAddresses As Range, rngeCell As Range

Set olApp = CreateObject("Outlook.Application")

On Error Resume Next
Set rngeAddresses = Application.InputBox(prompt:="Give a text", Type:=8)
If Err Then Exit Sub
On Error GoTo 0

For Each rngeCell In rngeAddresses.Cells
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = rngeCell.Value
Next
</pre>

Hi Ivan,

Still a little question.
In this macro I must filled in a range:
exeample : A5 : A20

Is it possible that this macro can filled in automatickly the range.
The range is from A5 to the last row of column A.So I must not each time filled in manualy the range and I still have the possibility to change the range.
Thank for your time.

If I understand you correctly then

<pre/>
Dim olApp As Object, olMail As Object
Dim rngeAddresses As Range, rngeCell As Range

Set olApp = CreateObject("Outlook.Application")

On Error Resume Next
Set rngeAddresses = Range(Range("A5"), Range("A5").End(xlDown))
If Err Then Exit Sub
On Error GoTo 0

For Each rngeCell In rngeAddresses
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = rngeCell.Value
Next
</pre>

 

[verluc]

On 2002-04-29 02:59, Ivan F Moala wrote:

On 2002-04-29 02:29, verluc wrote:

On 2002-04-29 00:11, Ivan F Moala wrote:
Try;

<pre/>
Dim olApp As Object, olMail As Object
Dim rngeAddresses As Range, rngeCell As Range

Set olApp = CreateObject("Outlook.Application")

On Error Resume Next
Set rngeAddresses = Application.InputBox(prompt:="Give a text", Type:=8)
If Err Then Exit Sub
On Error GoTo 0

For Each rngeCell In rngeAddresses.Cells
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = rngeCell.Value
Next
</pre>

Hi Ivan,

Still a little question.
In this macro I must filled in a range:
exeample : A5 : A20

Is it possible that this macro can filled in automatickly the range.
The range is from A5 to the last row of column A.So I must not each time filled in manualy the range and I still have the possibility to change the range.
Thank for your time.

If I understand you correctly then

<pre/>
Dim olApp As Object, olMail As Object
Dim rngeAddresses As Range, rngeCell As Range

Set olApp = CreateObject("Outlook.Application")

On Error Resume Next
Set rngeAddresses = Range(Range("A5"), Range("A5").End(xlDown))
If Err Then Exit Sub
On Error GoTo 0

For Each rngeCell In rngeAddresses
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = rngeCell.Value
Next
</pre>

Thanks Ivan,that's what I need.
Thanks for your time.