Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sending mail (2003)

    All

    The following code works the first time around but will not loop so no second or subsequent mail is ever created - Can anybody please advise why?
    Option Explicit

    Public Sub email()

    Dim olApp As Outlook.Application
    Dim olMail As MailItem
    Dim cell As Range
    'Application.ScreenUpdating = False
    Set olApp = New Outlook.Application
    For Each cell In Sheets("Sheet2").Columns("AI").Cells.SpecialCells( xlCellTypeConstants)
    If cell.Offset(0, 1).Value <> "" Then
    If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "Yes" Then
    ' If cell.Value Like "*,*" And cell.Offset(0, 1).Value = "yes" Then

    Set olMail = olApp.CreateItem(olMailItem)
    With olMail
    .to = cell.Value
    .Subject = "GDV3 upgrade machine not returned"
    .Body = "Dear " & cell.Offset(0, -25).Value & vbNewLine & vbNewLine & _
    "Since the GDV.3 Windows installation we have identified a number of machines that have failed to have the technical improvements required by this upgrade. " & vbNewLine & vbNewLine & _
    "We are conducting an in-depth review to chase such machines and your name has arisen as being the owner of the following such machine:" & vbNewLine & vbNewLine & _
    "Barcode: " & cell.Offset(0, -32).Value & vbNewLine & _
    "Serial No: " & cell.Offset(0, -30).Value & vbNewLine & _
    "Type: " & cell.Offset(0, -28).Value & vbNewLine & vbNewLine & _
    "To return any outstanding laptops or desktops, could you please telephone 4444 and log a call for collection and reply to this email advising of the current position relating to the machine." & vbNewLine & vbNewLine & _
    "Regards" & vbNewLine & vbNewLine & _
    "Matt Baldwin" & vbNewLine & _
    "ICT Watford" & vbNewLine & _
    "710 4122" & vbNewLine & vbNewLine

    .Send 'Or use Display
    End With
    Set olMail = Nothing
    End If
    End If
    Next cell
    Set olApp = Nothing
    'Application.ScreenUpdating = True

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

    Re: sending mail (2003)

    Your code works OK in a demo workbook I whipped up, so perhaps there is something unexpected in the data columns you're working with?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sending mail (2003)

    Hans

    You may me right so I changed the code to:

    ThisWorkbook.Worksheets("Sheet2").Activate
    For j = 2 To 700
    If Cells(j, 35).Value Like "*@*" And Cells(j, 36).Value = "Yes" Then
    Set olMail = olApp.CreateItem(olMailItem)
    With olMail

    End With
    Set olMail = Nothing
    End If
    Next j

    Then it worked fine -

    Thanks

Posting Permissions

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