e-mail warning from Excel

[americanpie3]

Hello,

I have a plan to create an excel sheet DB of softwares I sold to clients. Each license lasts 1 year. Here's an example of my sheet:

Book1
	A	B	C	D	E	F
1	SoftwareDB
2
3	Companyname	Softwaresold	Datesold	Effectivelicensedate	Enddate	Price
4
5	A1	SymantecAV	29/01/2007	31/01/2007	30/01/2008	Toomuch
Sheet1

Of course there will be many more companies and softwares sold.

What I would like is to create a macro that will scan my sheet of my End Date and e-mail me and my boss when the End Date is one month from the license to expire.

Thank you

 

[lotsasmiles]

Wow. I did this EXACT thing for insurance expirations. See the following code and modify to your needs:

Sub InsEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
 
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
 
    On Error GoTo cleanup
    For Each cell In Sheets("PermInfo").Columns("I").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Offset(0, -4).Value Like "?*@?*.?*" And ((cell.Value) <= (Date + 30)) And ((cell.Offset(0, 1).Value) = ("A")) And LCase(cell.Offset(0, 2).Value) <> "sent" Then
            Set OutMail = OutApp.CreateItem(0)
 
            On Error Resume Next
            With OutMail
                .To = cell.Offset(0, -4).Value
                .Subject = "Please Update Your Insurance with Kroll"
                .Body = "Dear " & cell.Offset(0, -7).Value & " " & cell.Offset(0, -8).Value & "," & vbNewLine & _
                        "If you are recieving this email it is because your insurance " & _
                        "has expired or is due to expire within 30 days on " & cell.Value & _
                        ".  Please fax a valid copy of your car insurance card for any vehicle " & _
                        "you use while on business.  We will need an updated " & _
                        "copy every time your insurance is renewed, and you will receive a " & _
                        "similar email to remind you.   & vbNewLine & _
                        "Thank you," & vbNewLine & vbNewLine
                'You can add files also like this
                '.Attachments.Add ("C:\test.txt")
                .Send  'Or use Display
            End With
            On Error GoTo 0


            cell.Offset(0, 2).Value = "sent"

            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Took me FOREVER to get this code, so hopefully this helps you a little faster! Good luck!

 

[americanpie3]

Thank you for your reply.

I would just need the e-mail to warn me and my boss of the company that needs the renewal, the software and the end date to be e-mailed. I don't need to write an e-mail to my clients.

Does the excel sheet ahve to be opened or can it just autorun without my opening the excel sheet.

Thanks

 

[lotsasmiles]

Well, that's why I said "modify to your needs." This would just give you a how-to; I merely copied my code. How you use it is up to you. Unfortunately, as far as I know, you need to actually open an excel spreadsheet to run a macro. If there's another related file you open more frequently, I'd suggest putting the code there and running it from that sheet.

 

[americanpie3]

I've been trying to edit this code for my needs yet, I keep getting compling errors. Unfortunaltely, I'm not a pro with VB.

Can anyone help?

Thanks

 

[Smitty]

I've been trying to edit this code for my needs yet, I keep getting compling errors. Unfortunaltely, I'm not a pro with VB.

Can anyone help?

Not unless you post the code and indicate where you're getting the errors.

This may be a start though:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([E5], Cells(Rows.Count, "E").End(xlUp))
            <SPAN style="color:#00007F">If</SPAN> c.Value <= <SPAN style="color:#00007F">Date</SPAN> + 30 <SPAN style="color:#00007F">Then</SPAN>
                MsgBox "The license for " & _
                    c.Offset(, -3) & _
                        ", purchased by " & _
                    c.Offset(, -4) & _
                        " will expire on " & _
                    c.Value, vbInformation + vbOKOnly, _
                        "Expiration imminent"
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

If it does what you want you can replace the MsgBox with e-mail code.

HTH,

Smitty

 

[americanpie3]

Hey Smitty,

What would the e-mail code be? Also can I include that into the Macro?

Thanx

 

[Smitty]

What would the e-mail code be? Also can I include that into the Macro?

If your e-mail client is Outlook, see www.rondebruin.nl/sendmail

I'd try to get one of Ron's routines to work for you, then the code I posted can be ammended to call it.

Smitty

 

[americanpie3]

URL no good.

Outlook is my e-mail client. I have here Outlook 2003 and 2007.

Also, according to your code, all it did was setup filters on my excel sheet.

 

[Smitty]

Looks like Ron changed his site recently, try: http://www.rondebruin.nl/tips.htm

Also, according to your code, all it did was setup filters on my excel sheet

Where do you see that?

Did you actually try what I posted?

Smitty