+ Reply to Thread
Results 1 to 3 of 3

Generate email from worksheet based on multiple cell values

Hybrid View

  1. #1
    Registered User
    Join Date
    07-10-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Generate email from worksheet based on multiple cell values

    Hi. I have a worksheet measuring staff attendance on various courses. Using date-today, a 'days lapsed' value is calculated in column D. If the value of the cell in column D exceeds 365 days, an email is generated to that member of staff using the following code in a module;

    Sub TestFile()
        Dim OutApp As Object
        Dim OutMail As Object
        Dim cell As Range
        Application.ScreenUpdating = False
        Set OutApp = CreateObject("Outlook.Application")
        On Error GoTo cleanup
        For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value Like "?*@?*.?*" And _
               Cells(cell.Row, "D").Value > 365 Then
                Set OutMail = OutApp.CreateItem(0)
                On Error Resume Next
                With OutMail
                    .To = cell.Value
                    .Subject = "Fire Course Reminder"
                    .Body = "Dear " & Cells(cell.Row, "A").Value _
                          & vbNewLine & vbNewLine & _
                            "course update is due. Please contact E-learning to update this course"
                    .Display  
                End With
                On Error GoTo 0
                Set OutMail = Nothing
            End If
        Next cell
    cleanup:
        Set OutApp = Nothing
        Application.ScreenUpdating = True
    End Sub
    This works fine on a single column, but if I wanted to add another set of columns e.g. if I had columns F, I and J set to measure different lapses e.g.100, 200 or 300 days respectively, could I incorporate this code into the same module? I have tried using a new module for each column, but come up with
    Ambiguous name detected; Testfile
    warnings. Any info gratefully received,
    Mattbro
    Last edited by mattbro; 03-07-2012 at 03:23 PM. Reason: Solved!

  2. #2
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Generate email from worksheet based on multiple cell values

    Hi

    You could just rename your other macros to Testfile2, Testfile and Testfile4, then each would run independently.
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  3. #3
    Registered User
    Join Date
    07-10-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Generate email from worksheet based on multiple cell values

    Job done-have renamed, added a run multiple macro sub and got it all to fire off the one button. Excellent-thanks Roger!
    Mattbro

+ Reply to Thread

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