Emailing Maintenance Expired Date

[Guest]

I have an Access database for our customers that contains their current
status of maintenance and the date their maintenance expires. My boss would
like to see if there is a way to email our office manager a month before the
maintenance expires to remind her to send out an email to the customer to
renew their maintenance. Is there a way to email a person a month before the
expiration date arrives so renewal notices can be sent out?

 

[Vincent Johns]

The short answer to your question is yes, there's a way.

The long answer is, I'm afraid you'll have to use some VBA to do what I
think you want to do. Here's a suggestion (and you have lots of freedom
in how you organize this).

Suppose you have a Table containing the following kinds of stuff:

[Warranties] Table Datasheet View:

Name Expiration IsNotified? Email
date
---- ---------- ----------- -------------------------
Al 3/1/2006 No (e-mail address removed)
Bob 12/4/2005 Yes (e-mail address removed)
Jane 11/26/2005 No (e-mail address removed)

You'll probably have other fields as well. Now we define a Query like
this:

[Q_Expiring] SQL:

SELECT Warranties.Name,
Warranties.[Expiration date] AS [Date],
Warranties.Email, Warranties.[IsNotified?] AS Sent
FROM Warranties
WHERE (((Warranties.[Expiration date])
<=DateAdd("m",1,Date()))
AND ((Warranties.[IsNotified?])=No))
ORDER BY Warranties.[Expiration date];

[Q_Expiring] Query Datasheet View:

Name Date Email Sent
---- ---------- ------------------ ----
Jane 11/26/2005 (e-mail address removed) No

This will list the about-to-expire warranties. The "Al" record is
omitted because it's not close to expiring, since today is 11/22/2005,
and the "Bob" record because that's already been sent.

In a Module, I define a new public function SendExpirationNotices() as
follows (but it's possible that your boss might prefer different wording):

'------------------------------------------------------------
' Email notices to people who are about to
' be cut off
'
' NOTE: YOU NEED TO ADD ERROR HANDLING TO THIS CODE!!
'------------------------------------------------------------
Public Function SendExpirationNotices()

Dim strEmail As String 'Email address
Dim strExpiration As String 'Expiration date
Dim rstExpiring As Recordset 'List of expiring warranties
Dim strName As String 'Name of addressee
Dim strMessage As String 'Text of email message

Set rstExpiring = CurrentDb.QueryDefs("Q_Expiring") _
.OpenRecordset(Type:=dbOpenDynaset)

With rstExpiring

If .BOF Then 'If no records are present...

MsgBox "There are no notices to be sent."

Else 'if there are notices to be sent...

.MoveFirst

While Not .EOF

strName = .Fields("Name")
strEmail = .Fields("Email")
strExpiration = Format$(.Fields("Date"), "mmm. dd")

strMessage = "Hey there, " _
& strName _
& ", how's it going?" _
& vbCrLf & vbCrLf _
& "Watch out! Your warranty will expire on " _
& strExpiration _
& "." & vbCrLf & vbCrLf _
& "You need to fork over a bundle " _
& "to renew it. Have a nice day." _
& vbCrLf & vbCrLf _
& " Sincerely, " _
& vbCrLf & vbCrLf _
& " The money grabber"

'Send email without attaching any report, etc.
DoCmd.SendObject _
ObjectType:=acSendNoObject, _
ObjectName:="Q_Expiring", _
OutputFormat:="MS-DOSText(*.txt)", _
To:=strName & " <" & strEmail & ">", _
Subject:="Your Widget warranty " _
& "will expire " & strExpiration & "!", _
MessageText:=strMessage, _
EditMessage:=True

.Edit 'Mark record as sent.
.Fields("Sent") = True
.Update

.MoveNext 'Get next addressee

Wend 'Not .EOF
End If '.BOF ...
End With 'rstExpiring

End Function 'SendExpirationNotices()

This function will do the heavy lifting of formatting your email
message, inspiring your user to send in the cash right away.

Now, let's define a Macro to activate the function. It's pretty simple,
just one Action (unless you want to add something like popping up a
message box when you're finished):

[M_Send emails] Actions:

RunCode: SendExpirationNotices ()


Running [M_Send emails] will send off the emails to selected customers.

The email message that is sent looks like this (you get to edit each one
before it's sent):

To: Jane <[email protected]>
Subject: Your Widget warranty will expire Nov. 26!

Hey there, Jane, how's it going?

Watch out! Your warranty will expire on Nov. 26.

You need to fork over a bundle to renew it. Have a nice day.

Sincerely,

The money grabber

If you try to run the [M_Send emails] Macro again, you get a message box
saying, "There are no notices to be sent."

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Ok, some of this helps, but I don't need an automatic notice sent out to the
customer. I just need an email sent to the office manager stating that xxx
number of customers maintenance is about to expire so she can send out the
appropriate emails.

What part of this will actually just email my office manager of upcoming
expiration dates for maintenance? I currently only have 1 table and each
entry in it contains ALL of the information for our customer (including
warranty expiration date).

Thanks,
Roxanne

The short answer to your question is yes, there's a way.

The long answer is, I'm afraid you'll have to use some VBA to do what I
think you want to do. Here's a suggestion (and you have lots of freedom
in how you organize this).

Suppose you have a Table containing the following kinds of stuff:

[Warranties] Table Datasheet View:

Name Expiration IsNotified? Email
date
---- ---------- ----------- -------------------------
Al 3/1/2006 No (e-mail address removed)
Bob 12/4/2005 Yes (e-mail address removed)
Jane 11/26/2005 No (e-mail address removed)

You'll probably have other fields as well. Now we define a Query like
this:

[Q_Expiring] SQL:

SELECT Warranties.Name,
Warranties.[Expiration date] AS [Date],
Warranties.Email, Warranties.[IsNotified?] AS Sent
FROM Warranties
WHERE (((Warranties.[Expiration date])
<=DateAdd("m",1,Date()))
AND ((Warranties.[IsNotified?])=No))
ORDER BY Warranties.[Expiration date];

[Q_Expiring] Query Datasheet View:

Name Date Email Sent
---- ---------- ------------------ ----
Jane 11/26/2005 (e-mail address removed) No

This will list the about-to-expire warranties. The "Al" record is
omitted because it's not close to expiring, since today is 11/22/2005,
and the "Bob" record because that's already been sent.

In a Module, I define a new public function SendExpirationNotices() as
follows (but it's possible that your boss might prefer different wording):

'------------------------------------------------------------
' Email notices to people who are about to
' be cut off
'
' NOTE: YOU NEED TO ADD ERROR HANDLING TO THIS CODE!!
'------------------------------------------------------------
Public Function SendExpirationNotices()

Dim strEmail As String 'Email address
Dim strExpiration As String 'Expiration date
Dim rstExpiring As Recordset 'List of expiring warranties
Dim strName As String 'Name of addressee
Dim strMessage As String 'Text of email message

Set rstExpiring = CurrentDb.QueryDefs("Q_Expiring") _
.OpenRecordset(Type:=dbOpenDynaset)

With rstExpiring

If .BOF Then 'If no records are present...

MsgBox "There are no notices to be sent."

Else 'if there are notices to be sent...

.MoveFirst

While Not .EOF

strName = .Fields("Name")
strEmail = .Fields("Email")
strExpiration = Format$(.Fields("Date"), "mmm. dd")

strMessage = "Hey there, " _
& strName _
& ", how's it going?" _
& vbCrLf & vbCrLf _
& "Watch out! Your warranty will expire on " _
& strExpiration _
& "." & vbCrLf & vbCrLf _
& "You need to fork over a bundle " _
& "to renew it. Have a nice day." _
& vbCrLf & vbCrLf _
& " Sincerely, " _
& vbCrLf & vbCrLf _
& " The money grabber"

'Send email without attaching any report, etc.
DoCmd.SendObject _
ObjectType:=acSendNoObject, _
ObjectName:="Q_Expiring", _
OutputFormat:="MS-DOSText(*.txt)", _
To:=strName & " <" & strEmail & ">", _
Subject:="Your Widget warranty " _
& "will expire " & strExpiration & "!", _
MessageText:=strMessage, _
EditMessage:=True

.Edit 'Mark record as sent.
.Fields("Sent") = True
.Update

.MoveNext 'Get next addressee

Wend 'Not .EOF
End If '.BOF ...
End With 'rstExpiring

End Function 'SendExpirationNotices()

This function will do the heavy lifting of formatting your email
message, inspiring your user to send in the cash right away.

Now, let's define a Macro to activate the function. It's pretty simple,
just one Action (unless you want to add something like popping up a
message box when you're finished):

[M_Send emails] Actions:

RunCode: SendExpirationNotices ()


Running [M_Send emails] will send off the emails to selected customers.

The email message that is sent looks like this (you get to edit each one
before it's sent):

To: Jane <[email protected]>
Subject: Your Widget warranty will expire Nov. 26!

Hey there, Jane, how's it going?

Watch out! Your warranty will expire on Nov. 26.

You need to fork over a bundle to renew it. Have a nice day.

Sincerely,

The money grabber

If you try to run the [M_Send emails] Macro again, you get a message box
saying, "There are no notices to be sent."

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I have an Access database for our customers that contains their current
status of maintenance and the date their maintenance expires. My boss would
like to see if there is a way to email our office manager a month before the
maintenance expires to remind her to send out an email to the customer to
renew their maintenance. Is there a way to email a person a month before the
expiration date arrives so renewal notices can be sent out?

 

[Vincent Johns]

Just emailing a list to your manager will be easier (though, once you've
set either one up, both would be easy to use).

You can define a Macro that contains only a "SendObject" action to send
the results of your Query as an attachment. If you want to express it
in VBA, it might look like this:

Option Compare Database
Option Explicit

'------------------------------------------------------------
' M_SendList
'
'------------------------------------------------------------
Public Function M_SendList()

' Send list of expiring warranties
DoCmd.SendObject _
ObjectType:=acQuery, _
ObjectName:="Q_Expiring", _
OutputFormat:="MS-DOSText(*.txt)", _
To:="Office manager <[email protected]>", _
Cc:="", _
Bcc:="", _
Subject:="Expiring warranty list", _
MessageText:="List of expiring warranties is attached.", _
EditMessage:=False

End Function 'M_SendList()

.... but you don't need to use VBA, just use a Macro and fill in the
"SendObject" action's parameters similarly to what I've put into the VBA
code here.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Ok, some of this helps, but I don't need an automatic notice sent out to the
customer. I just need an email sent to the office manager stating that xxx
number of customers maintenance is about to expire so she can send out the
appropriate emails.

What part of this will actually just email my office manager of upcoming
expiration dates for maintenance? I currently only have 1 table and each
entry in it contains ALL of the information for our customer (including
warranty expiration date).

Thanks,
Roxanne

:

The short answer to your question is yes, there's a way.

The long answer is, I'm afraid you'll have to use some VBA to do what I
think you want to do. Here's a suggestion (and you have lots of freedom
in how you organize this).

Suppose you have a Table containing the following kinds of stuff:

[Warranties] Table Datasheet View:

Name Expiration IsNotified? Email
date
---- ---------- ----------- -------------------------
Al 3/1/2006 No (e-mail address removed)
Bob 12/4/2005 Yes (e-mail address removed)
Jane 11/26/2005 No (e-mail address removed)

You'll probably have other fields as well. Now we define a Query like
this:

[Q_Expiring] SQL:

SELECT Warranties.Name,
Warranties.[Expiration date] AS [Date],
Warranties.Email, Warranties.[IsNotified?] AS Sent
FROM Warranties
WHERE (((Warranties.[Expiration date])
<=DateAdd("m",1,Date()))
AND ((Warranties.[IsNotified?])=No))
ORDER BY Warranties.[Expiration date];

[Q_Expiring] Query Datasheet View:

Name Date Email Sent
---- ---------- ------------------ ----
Jane 11/26/2005 (e-mail address removed) No

This will list the about-to-expire warranties. The "Al" record is
omitted because it's not close to expiring, since today is 11/22/2005,
and the "Bob" record because that's already been sent.

In a Module, I define a new public function SendExpirationNotices() as
follows (but it's possible that your boss might prefer different wording):

'------------------------------------------------------------
' Email notices to people who are about to
' be cut off
'
' NOTE: YOU NEED TO ADD ERROR HANDLING TO THIS CODE!!
'------------------------------------------------------------
Public Function SendExpirationNotices()

Dim strEmail As String 'Email address
Dim strExpiration As String 'Expiration date
Dim rstExpiring As Recordset 'List of expiring warranties
Dim strName As String 'Name of addressee
Dim strMessage As String 'Text of email message

Set rstExpiring = CurrentDb.QueryDefs("Q_Expiring") _
.OpenRecordset(Type:=dbOpenDynaset)

With rstExpiring

If .BOF Then 'If no records are present...

MsgBox "There are no notices to be sent."

Else 'if there are notices to be sent...

.MoveFirst

While Not .EOF

strName = .Fields("Name")
strEmail = .Fields("Email")
strExpiration = Format$(.Fields("Date"), "mmm. dd")

strMessage = "Hey there, " _
& strName _
& ", how's it going?" _
& vbCrLf & vbCrLf _
& "Watch out! Your warranty will expire on " _
& strExpiration _
& "." & vbCrLf & vbCrLf _
& "You need to fork over a bundle " _
& "to renew it. Have a nice day." _
& vbCrLf & vbCrLf _
& " Sincerely, " _
& vbCrLf & vbCrLf _
& " The money grabber"

'Send email without attaching any report, etc.
DoCmd.SendObject _
ObjectType:=acSendNoObject, _
ObjectName:="Q_Expiring", _
OutputFormat:="MS-DOSText(*.txt)", _
To:=strName & " <" & strEmail & ">", _
Subject:="Your Widget warranty " _
& "will expire " & strExpiration & "!", _
MessageText:=strMessage, _
EditMessage:=True

.Edit 'Mark record as sent.
.Fields("Sent") = True
.Update

.MoveNext 'Get next addressee

Wend 'Not .EOF
End If '.BOF ...
End With 'rstExpiring

End Function 'SendExpirationNotices()

This function will do the heavy lifting of formatting your email
message, inspiring your user to send in the cash right away.

Now, let's define a Macro to activate the function. It's pretty simple,
just one Action (unless you want to add something like popping up a
message box when you're finished):

[M_Send emails] Actions:

RunCode: SendExpirationNotices ()


Running [M_Send emails] will send off the emails to selected customers.

The email message that is sent looks like this (you get to edit each one
before it's sent):

To: Jane <[email protected]>
Subject: Your Widget warranty will expire Nov. 26!

Hey there, Jane, how's it going?

Watch out! Your warranty will expire on Nov. 26.

You need to fork over a bundle to renew it. Have a nice day.

Sincerely,

The money grabber

If you try to run the [M_Send emails] Macro again, you get a message box
saying, "There are no notices to be sent."

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


Roxanne wrote:

I have an Access database for our customers that contains their current
status of maintenance and the date their maintenance expires. My boss would
like to see if there is a way to email our office manager a month before the
maintenance expires to remind her to send out an email to the customer to
renew their maintenance. Is there a way to email a person a month before the
expiration date arrives so renewal notices can be sent out?

 

[Guest]

Thank you Vincent! The query/macro combination works well. I was wondering
if there was a way to automate or schedule this so that someone doesn't have
to go into the database every month and run the macro.

Also, for this to email to the hiring manager, I take it a mail program has
to be set up on the server where this database resides - we use outlook, and
I was successful getting an email to send from my computer (since the
database was on my PC at the time).

Thanks again for your expertise - I appreciate it!

Just emailing a list to your manager will be easier (though, once you've
set either one up, both would be easy to use).

You can define a Macro that contains only a "SendObject" action to send
the results of your Query as an attachment. If you want to express it
in VBA, it might look like this:

Option Compare Database
Option Explicit

'------------------------------------------------------------
' M_SendList
'
'------------------------------------------------------------
Public Function M_SendList()

' Send list of expiring warranties
DoCmd.SendObject _
ObjectType:=acQuery, _
ObjectName:="Q_Expiring", _
OutputFormat:="MS-DOSText(*.txt)", _
To:="Office manager <[email protected]>", _
Cc:="", _
Bcc:="", _
Subject:="Expiring warranty list", _
MessageText:="List of expiring warranties is attached.", _
EditMessage:=False

End Function 'M_SendList()

.... but you don't need to use VBA, just use a Macro and fill in the
"SendObject" action's parameters similarly to what I've put into the VBA
code here.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Ok, some of this helps, but I don't need an automatic notice sent out to the
customer. I just need an email sent to the office manager stating that xxx
number of customers maintenance is about to expire so she can send out the
appropriate emails.

What part of this will actually just email my office manager of upcoming
expiration dates for maintenance? I currently only have 1 table and each
entry in it contains ALL of the information for our customer (including
warranty expiration date).

Thanks,
Roxanne

:

The short answer to your question is yes, there's a way.

The long answer is, I'm afraid you'll have to use some VBA to do what I
think you want to do. Here's a suggestion (and you have lots of freedom
in how you organize this).

Suppose you have a Table containing the following kinds of stuff:

[Warranties] Table Datasheet View:

Name Expiration IsNotified? Email
date
---- ---------- ----------- -------------------------
Al 3/1/2006 No (e-mail address removed)
Bob 12/4/2005 Yes (e-mail address removed)
Jane 11/26/2005 No (e-mail address removed)

You'll probably have other fields as well. Now we define a Query like
this:

[Q_Expiring] SQL:

SELECT Warranties.Name,
Warranties.[Expiration date] AS [Date],
Warranties.Email, Warranties.[IsNotified?] AS Sent
FROM Warranties
WHERE (((Warranties.[Expiration date])
<=DateAdd("m",1,Date()))
AND ((Warranties.[IsNotified?])=No))
ORDER BY Warranties.[Expiration date];

[Q_Expiring] Query Datasheet View:

Name Date Email Sent
---- ---------- ------------------ ----
Jane 11/26/2005 (e-mail address removed) No

This will list the about-to-expire warranties. The "Al" record is
omitted because it's not close to expiring, since today is 11/22/2005,
and the "Bob" record because that's already been sent.

In a Module, I define a new public function SendExpirationNotices() as
follows (but it's possible that your boss might prefer different wording):

'------------------------------------------------------------
' Email notices to people who are about to
' be cut off
'
' NOTE: YOU NEED TO ADD ERROR HANDLING TO THIS CODE!!
'------------------------------------------------------------
Public Function SendExpirationNotices()

Dim strEmail As String 'Email address
Dim strExpiration As String 'Expiration date
Dim rstExpiring As Recordset 'List of expiring warranties
Dim strName As String 'Name of addressee
Dim strMessage As String 'Text of email message

Set rstExpiring = CurrentDb.QueryDefs("Q_Expiring") _
.OpenRecordset(Type:=dbOpenDynaset)

With rstExpiring

If .BOF Then 'If no records are present...

MsgBox "There are no notices to be sent."

Else 'if there are notices to be sent...

.MoveFirst

While Not .EOF

strName = .Fields("Name")
strEmail = .Fields("Email")
strExpiration = Format$(.Fields("Date"), "mmm. dd")

strMessage = "Hey there, " _
& strName _
& ", how's it going?" _
& vbCrLf & vbCrLf _
& "Watch out! Your warranty will expire on " _
& strExpiration _
& "." & vbCrLf & vbCrLf _
& "You need to fork over a bundle " _
& "to renew it. Have a nice day." _
& vbCrLf & vbCrLf _
& " Sincerely, " _
& vbCrLf & vbCrLf _
& " The money grabber"

'Send email without attaching any report, etc.
DoCmd.SendObject _
ObjectType:=acSendNoObject, _
ObjectName:="Q_Expiring", _
OutputFormat:="MS-DOSText(*.txt)", _
To:=strName & " <" & strEmail & ">", _
Subject:="Your Widget warranty " _
& "will expire " & strExpiration & "!", _
MessageText:=strMessage, _
EditMessage:=True

.Edit 'Mark record as sent.
.Fields("Sent") = True
.Update

.MoveNext 'Get next addressee

Wend 'Not .EOF
End If '.BOF ...
End With 'rstExpiring

End Function 'SendExpirationNotices()

This function will do the heavy lifting of formatting your email
message, inspiring your user to send in the cash right away.

Now, let's define a Macro to activate the function. It's pretty simple,
just one Action (unless you want to add something like popping up a
message box when you're finished):

[M_Send emails] Actions:

RunCode: SendExpirationNotices ()


Running [M_Send emails] will send off the emails to selected customers.

The email message that is sent looks like this (you get to edit each one
before it's sent):

To: Jane <[email protected]>
Subject: Your Widget warranty will expire Nov. 26!

Hey there, Jane, how's it going?

Watch out! Your warranty will expire on Nov. 26.

You need to fork over a bundle to renew it. Have a nice day.

Sincerely,

The money grabber

If you try to run the [M_Send emails] Macro again, you get a message box
saying, "There are no notices to be sent."

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


Roxanne wrote:


I have an Access database for our customers that contains their current
status of maintenance and the date their maintenance expires. My boss would
like to see if there is a way to email our office manager a month before the
maintenance expires to remind her to send out an email to the customer to
renew their maintenance. Is there a way to email a person a month before the
expiration date arrives so renewal notices can be sent out?

 

[Vincent Johns]

Thank you Vincent! The query/macro combination works well. I was wondering
if there was a way to automate or schedule this so that someone doesn't have
to go into the database every month and run the macro.

If you're running Windows XP, I expect that you can go to the Control
Panel, to Scheduled Tasks, to run the database once a month. (But I
tried doing that just now, trying to get it to run the database once
every couple of minutes, and it didn't work for me.)

What I did was to set up a Form that doesn't do much, just contains a
Label control saying that email is scheduled. My Form's "On Activate"
event invokes a Macro that sends the email and displays a Message Box
saying that email has been sent, then runs a "Quit" Action to turn off
Access.

I set the database's startup options (on Tools --> Startup) to display
(and thus to activate) the Form on startup but not to display the
Database Window. So, when you run the database file, about all it does
is to send the email, display the Message Box, and exit. You could set
this up so the database that does this reads its data from another file
(with linked Tables), so it would have no other purpose than to send the
emails. The other database file, containing the Tables, is the one
you'd use for maintenance, such as for updating the Tables to indicate
who had renewed his maintenance agreement.

That part works for me, but apparently I haven't properly specified to
the Scheduled Tasks utility that I want that file run on schedule.
Maybe you can fiddle with it and determine how to do that. Good luck.

Also, for this to email to the hiring manager, I take it a mail program has
to be set up on the server where this database resides - we use outlook, and
I was successful getting an email to send from my computer (since the
database was on my PC at the time).

Thanks again for your expertise - I appreciate it!

Good -- I'm happy it worked.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.