Results 1 to 3 of 3

Automated email coding

Threaded View

  1. #1
    Registered User
    Join Date
    01-14-2010
    Location
    West Virginia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Automated email coding

    All,
    Below is a copy of the code that I am using to try to automatically send an email to a list of people if any value between I14 and l:400 meets or exceeds the value in F10. I am very close to getting it to work, as it will send an email if I14 exceeds F10, but if the first time the value exceeds is past I14 it will not send the email. I have a For loop in it which will work if I remove the exit for statement, but then I get 386 emails. Clearly, that is not what I want. I have very limited programming knowledge and would love some assistance on this. Thanks

    Sub auto_open()
        ThisWorkbook.Worksheets("OUTTIME LOG").OnEntry = "Update"
    End Sub
    
    Sub Update()
    Dim KeyCells As String
    
    KeyCells = "A14:A400, B14:B400, C14:C400, D14:D400, E14:E400, G14:G400, H14:H400"
    
    If Not Application.Intersect(ActiveCell, Range(KeyCells)) Is Nothing Then Decider
    
    End Sub
    
    Sub Decider()
    Dim cell As Range
        Dim MaxOutTime As Integer
                
        Set wb1 = ActiveWorkbook
        
        MaxOutTime = wb1.Sheets("OUTTIME LOG").Range("F10").Value
        
    For Each cell In wb1.Sheets("OUTTIME LOG").Range("I14:I400")
     If (cell.Value > MaxOutTime) Then
      Mail_workbook_Outlook_2
      Exit For
     End If
    Next cell
    
    End Sub
    
    Sub Mail_workbook_Outlook_2()
    'Working in 2000-2007
        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set wb1 = ActiveWorkbook
    
        If Val(Application.Version) >= 12 Then
            If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
                MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
                       "be no VBA code in the file you send. Save the" & vbNewLine & _
                       "file first as xlsm and then try the macro again.", vbInformation
                Exit Sub
            End If
        End If
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        'Make a copy of the file/Open it/Mail it/Delete it
        'If you want to change the file name then change only TempFileName
        TempFilePath = Environ$("temp") & "\"
        TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
        FileExtStr = "." & LCase(Right(wb1.Name, _
                                       Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
    
        wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
        Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
    
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
        With OutMail
            .To = "[email protected]"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "Hi there"
            .Attachments.Add wb2.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0
    
        wb2.Close SaveChanges:=False
    
        'Delete the file
        Kill TempFilePath & TempFileName & FileExtStr
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        Exit For
    End Sub
    Last edited by netgame27; 01-19-2010 at 09:54 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1