Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable Disappears (XP; SR3)

    I am having an issue with the following code. It works just fine for the first pass but has an issue during the second and subsequent passes where the variable "oTo" errors out with the following error message.

    Sub NewEmailCode()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem


    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)

    Sheets("Sheet1").Select
    Cells(13, ActiveCell.Column).Offset(1, 0).Select
    Do Until IsEmpty(Cells(ActiveCell.Row, 3))
    If ActiveCell = "" Then
    oTo = Cells(ActiveCell.Row, 3) Contains the e-mail address
    oSubject = Range("R_Comment1").Comment.Text

    With objOMail
    .To = oTo
    .Subject = "Test " & Format(Cells(13, ActiveCell.Column), "MMMm YYYY")
    .Body = .Body & vbCrLf & " "
    .Body = oSubject
    ' .Display
    .Send
    End With
    End If
    ActiveCell.Offset(1, 0).Select
    Loop

    Set objOMail = Nothing
    Set objOLook = Nothing
    MsgBox "Finished"
    End Sub


    Any assistance would be appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Variable Disappears (XP; SR3)

    I do not think it is oTo that is gone (If you hover over oTo is that in valid?), I think it is "ObjMail" since you sent it and it is no longer there

    I don't use Outlook so I don't work with outlook VB (which is what you are doing), but I suspect that you need to put the set objOmail in the loop to create a new item to mail

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Disappears (XP; SR3)

    Steve,

    Your suggestion about the missing line of code worked.

    Thanks,
    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Disappears (XP; SR3)

    How does one go about distinguishing between XP and 2000. The Tools>References is having an issue with the Outlook Object between versions. The EndUsers have different Office versions and I would like to include something in the code to resolve the issue. In this case Outlook 9.0 Object refers to Office 2000 while 10 to XP.

    Thanks,
    John

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variable Disappears (XP; SR3)

    If you create and test the workbook in Excel 2000, it should work ok in Excel 2002 and 2003. The reverse is not true.

    You could also use late binding instead of early binding. See the second half of <post#=482143>post 482143</post: >.

Posting Permissions

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