Solved

Follow-up to "Generate Emails in Excel on Certain Days"

Posted on 2009-04-02
9
364 Views
Last Modified: 2012-06-27
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.
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

Open in new window

0
Comment
Question by:bpfsr
  • 5
  • 4
9 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24049739
You can use the following code and it will do what you are looking for...
Saurabh...

Sub x()
 
 
 
 

End Sub

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 IsDate(cell.Value) = True Then

            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

        End If
 

    Next Cel

    Application.ScreenUpdating = True

End Sub

Open in new window

0
 

Author Comment

by:bpfsr
ID: 24049832
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?
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24049891
Yes change this line to...
If IsDate(cell.Value) = True Then
to this...
If IsDate(cells(cell.row,"A").value) = True Then
Saurabh...
0
 

Author Comment

by:bpfsr
ID: 24050061
getting the same error on the same line - object required. Do I maybe need to set a range?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24050099
No you should not, can i see your workbook...
Saurabh...
0
 

Author Comment

by:bpfsr
ID: 24050515
Sure, thank you, here is a sample...
EE-Example.xls
0
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 500 total points
ID: 24050684
Ahh use this one and it will do what you are looking for...
Saurabh...

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 IsDate(Cells(Cel.Row, "A").Value) = True Then

            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

        End If

 

    Next Cel

    Application.ScreenUpdating = True

End Sub

Open in new window

0
 

Author Comment

by:bpfsr
ID: 24055974
I'm sorry, I don't see where you made the change...
0
 

Author Comment

by:bpfsr
ID: 24056338
Okay I got it, thanks.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now