Solved

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

Posted on 2009-04-02
9
366 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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
 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

822 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