Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: BUTTON "O.K."-CANCEL

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try;


    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


    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    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



    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.

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    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



    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


    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


    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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;


    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



    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


    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


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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •