can't define email recipients from cell?

[fabrecass]

Hi guys,

this looks to see if a date is in the past, then if column H is NOT at 100% it sends and email, but i can't get it to use the email address i enter into column L?

can anyone help? always debugs at .To = .Cells(2, "L").Value

Private Sub Workbook_Open()

     '#
     '# declare private variables
     '#
          Dim OutApp As Object
          Dim OutMail As Object
          
     '#
     '# only send an e-mail when the date in column E is before today and column H is less than 100%
     '#
          With ThisWorkbook.Worksheets("Sheet1")
          
               If .Cells(2, "E").Value <= Date And LenB(.Cells(2, "H") < 1) Then
     
               '#
               '# start an instance of the Outlook application to send the e-mail
               '#
                    Set OutApp = CreateObject("Outlook.Application")
                    OutApp.Session.Logon
                    Set OutMail = OutApp.CreateItem(0)
    
               '#
               '# create and send the e-mail
               '#
                    With OutMail
                         .To = .Cells(2, "L").Value
                         .CC = vbNullString
                         .BCC = vbNullString
                         .Subject = ThisWorkbook.Name
                         .Body = "Hi Sarah," & vbLf & "This range is either late for handing over, or the PIC hasn't populated a date in CT HO column " & ThisWorkbook.Name
                         '.Send
                         .display
                    End With
     
               '#
               '# highlight the date in column "E"
               '#
                    With .Cells(2, "E").Font
                         .Color = -16776961
                         .TintAndShade = 0
                    End With
               End If
          End With
    
    '#
    '# housekeeping
    '#
          Set OutMail = Nothing
          Set OutApp = Nothing
     End Sub

[fabrecass]

ok, i've solved initial issue, just removed the . from .cells.

but another issue has arisen.

it's supposed to check the dates in column E, if that's before today it then checks to see if column H states 100% - if it doesn't, it should send an email FOR EACH row this occurs, using column L to determine recipient - however it only brings up 1 email for the first row of data.

can anyone help?

[fabrecass]

please? pretty desperate!

[OllieB]

The .Send has been disabled in your code and only .Display is activated. Is that on purpose? I have added the requested loop

Private Sub Workbook_Open()

     '#
     '# declare private variables
     '#
          Dim OutApp As Object
          Dim OutMail As Object
          Dim lngRowNumber As Long
          
     '#
     '# initialise
     '#
          Set OutApp = CreateObject("Outlook.Application")
          OutApp.Session.Logon
     
     '#
     '# only send an e-mail when the date in column E is before today and column H is less than 100%
     '#
          With ThisWorkbook.Worksheets("Sheet1")
               For lngRowNumber = 2 To .UsedRange.Rows.Count
                    If .Cells(lngRowNumber, "E").Value <= Date And LenB(.Cells(lngRowNumber, "H") < 1) Then
                    
                    '#
                    '# create and send the e-mail
                    '#
                         Set OutMail = OutApp.CreateItem(0)
                         OutMail.to = .Cells(2, "L").Value
                         OutMail.CC = vbNullString
                         OutMail.BCC = vbNullString
                         OutMail.Subject = ThisWorkbook.Name
                         OutMail.Body = "Hi Sarah," & vbLf & "This range is either late for handing over, or the PIC hasn't populated a date in CT HO column " & ThisWorkbook.Name
                         'OutMail.Send
                         OutMail.display
     
                    '#
                    '# highlight the date in column "E"
                    '#
                         With .Cells(lngRowNumber, "E").Font
                              .Color = -16776961
                              .TintAndShade = 0
                         End With
                    End If
               Next lngRowNumber
          End With
    
    '#
    '# housekeeping
    '#
          Set OutMail = Nothing
          Set OutApp = Nothing
     End Sub

[fabrecass]

thank you Ollie! yes that is on purpose, just so i can see it works before i activate auto send.

I shall test this now.

[fabrecass]

Hi Ollie,

the loop worked but it just kept creating emails based on the first row of data, when i each row checked and an email created for that row if it meets the conditions.

[fabrecass]

Ollie, you're a genius sir. works a treat. thank you.

**edit, told me i'm not allowed to give you any more rep until i've given some other users some! but i will as soon as i can Ollie!

[OllieB]

give me a sec

[OllieB]

Note. CORRECTION

Private Sub Workbook_Open()

     '#
     '# declare private variables
     '#
          Dim OutApp As Object
          Dim OutMail As Object
          Dim lngRowNumber As Long
          
     '#
     '# initialise
     '#
          Set OutApp = CreateObject("Outlook.Application")
          OutApp.Session.Logon
     
     '#
     '# only send an e-mail when the date in column E is before today and column H is less than 100%
     '#
          With ThisWorkbook.Worksheets("Sheet1")
               For lngRowNumber = 2 To .UsedRange.Rows.Count
                    If .Cells(lngRowNumber, "E").Value <= Date And LenB(.Cells(lngRowNumber, "H") < 1) Then
                    
                    '#
                    '# create and send the e-mail
                    '#
                         Set OutMail = OutApp.CreateItem(0)
                         OutMail.to = .Cells(lngRowNumber, "L").Value
                         OutMail.CC = vbNullString
                         OutMail.BCC = vbNullString
                         OutMail.Subject = ThisWorkbook.Name
                         OutMail.Body = "Hi Sarah," & vbLf & "This range is either late for handing over, or the PIC hasn't populated a date in CT HO column " & ThisWorkbook.Name
                         'OutMail.Send
                         OutMail.display
     
                    '#
                    '# highlight the date in column "E"
                    '#
                         With .Cells(lngRowNumber, "E").Font
                              .Color = -16776961
                              .TintAndShade = 0
                         End With
                    End If
               Next lngRowNumber
          End With
    
    '#
    '# housekeeping
    '#
          Set OutMail = Nothing
          Set OutApp = Nothing
     End Sub