bpfsr
asked on
Follow-up to "Generate Emails in Excel on Certain Days"
I received help yesterday with a macro that generates emails fom an Excel sheet on a daily basis. The macro given to me worked fine, but when I modified my sheet and subsequently the macro I am getting and error. In particular I am getting a "type mismatch" error on this line:
If Now() - Cells(Cel.Row, "A") > 21 And LCase(Cells(Cel.Row, "E").Value) <> "yes" Then
[Now], of course, returns a Date type but [Cells(Cel.Row, "A")] is returning a string with the value "Date". This because Line 1 is a header line. Can somebody please adjust the macro to start on Line 2 and terminate on the first blank line? Thank you.
If Now() - Cells(Cel.Row, "A") > 21 And LCase(Cells(Cel.Row, "E").Value) <> "yes" Then
[Now], of course, returns a Date type but [Cells(Cel.Row, "A")] is returning a string with the value "Date". This because Line 1 is a header line. Can somebody please adjust the macro to start on Line 2 and terminate on the first blank line? Thank you.
Sub TranSEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim Cel As Range
Dim i As Integer
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
For Each Cel In Columns("D").Cells.SpecialCells(xlCellTypeConstants)
If Now() - Cells(Cel.Row, "A") > 21 And LCase(Cells(Cel.Row, "E").Value) <> "yes" Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = Cel.Value
.Subject = "Order Number " & Cells(Cel.Row, "F").Value
.Body = "Hello " & Cells(Cel.Row, "C").Value & "!" & vbNewLine & vbNewLine _
& "I am writing about your recent purchase of " & Cells(Cel.Row, "B").Value _
& ". I want to make sure all went well with your order. If you have any questions or concerns at all please let me know by email at bookitcorp@gmail.com. I appreciate this opportunity to serve you and sincerely hope you are having a nice day!" _
& vbNewLine & vbNewLine & "Very Sincerely Yours, " & vbNewLine & " Brian"
.Display
End With
Set OutMail = Nothing
Cells(Cel.Row, "E").Value = "Yes"
End If
Next Cel
Application.ScreenUpdating = True
End Sub
ASKER
Thank you, I am now getting Run-time error '424' - Object required on line:
If IsDate(cell.Value) = True Then
I'm thinking because the date column is actually A and the email column is D? Is the code above looking for dates in D or A?
If IsDate(cell.Value) = True Then
I'm thinking because the date column is actually A and the email column is D? Is the code above looking for dates in D or A?
Yes change this line to...
If IsDate(cell.Value) = True Then
to this...
If IsDate(cells(cell.row,"A") .value) = True Then
Saurabh...
If IsDate(cell.Value) = True Then
to this...
If IsDate(cells(cell.row,"A")
Saurabh...
ASKER
getting the same error on the same line - object required. Do I maybe need to set a range?
No you should not, can i see your workbook...
Saurabh...
Saurabh...
ASKER
Sure, thank you, here is a sample...
EE-Example.xls
EE-Example.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm sorry, I don't see where you made the change...
ASKER
Okay I got it, thanks.
Saurabh...
Open in new window