Checking data in 2 columns and sending email [Closed]

Report
Posts
8
Registration date
Tuesday June 8, 2010
Status
Member
Last seen
June 11, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I am trying to get excel to send an email with data based on 2 columns. I want it to look at column c and it the date is equal to or earlier and column V does not have a date in it, then it will list data in the email. As I have it, it is checking column O to see if there is a date and not column V. I can't figure out why. Any ideas?


Dim strbody As String
Dim EmailTo As String, EmailCC As String, Subject As String, ClaimNo As String
Sub MyMacro()
Dim LastRow As Double, rcell As Range, tcell As Range
Dim NoDupes As New Collection

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ClaimNo = "Claim Number" & Chr(9) & Chr(9) & "Patient Name"
strbody = ""

Sheets("Email").Select
For Each tcell In ThisWorkbook.Sheets("Email").Range("B7:B54")
strbody = strbody & tcell.Value & vbNewLine
Next
EmailTo = ThisWorkbook.Sheets("Email").Range("B2")
EmailCC = ThisWorkbook.Sheets("Email").Range("B3")
Subject = ThisWorkbook.Sheets("Email").Range("B6")

Sheets("Daily log").Select
Range("C65536").Select
Selection.End(xlUp).Select
LastRow = ActiveCell.Row
Range("C2").Select

For Each rcell In Range("C2:C" & LastRow)
If rcell.Offset(0, -2).Value <> 0 Then
If rcell.Value <= Date Then
If rcell.Offset(0, 12).Value = "" Then

ClaimNo = ClaimNo & Chr(13) & rcell.Offset(0, 3).Value & Chr(9) & Chr(9) & rcell.Offset(0, 2).Value

End If
End If
End If
Next rcell

strbody = "UR for the claims listed below are due today or overdue and are currently listed as open." & Chr(13) & ClaimNo & Chr(13) & strbody

Call Send_Email


Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
Sub Send_Email()
Dim OutApp As Object
Dim OutMail As Object

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = EmailTo
.cc = EmailCC
.Subject = Subject
.Body = "Dear " & ThisWorkbook.Sheets("Email").Range("C2") & "," & vbNewLine & vbNewLine & strbody
'.Display
.Send
End With
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub




1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
This line
If rcell.Offset(0, 12).Value = ""

is saying that from the current cell, look 12 cells on right.
Current cell is a cell in column C. 12 columns to right will be column O

You want to offset by 19 to hit column V

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!