All,
I am developing a spreadsheet that will be used to track maintenance cost
and work performed. With this I would like to be able to have the sheet
notify the used of pending maintenance to be done. The idea is that it would
be based on date of service. My question is; that I have been told that code
can be written in VB to do this. This is beyond my scope just yet, can
someone help me get started .
Thank you

If you could be more specific and give example it would be more helpful



"Mercer" wrote:

All,
I am developing a spreadsheet that will be used to track maintenance cost
and work performed. With this I would like to be able to have the sheet
notify the used of pending maintenance to be done. The idea is that it would
be based on date of service. My question is; that I have been told that code
can be written in VB to do this. This is beyond my scope just yet, can
someone help me get started .
Thank you

Here are some old code fragments (which I haven't tested lately) that may
give you some idea of how to create and send an email directly from Excel. I
have stripped out some stuff, so it is incomplete, but will show you how to
declare your objects and use them to create and send an email.

HTH,

Eric


Option Explicit
'
' Global variables
'
Public olObj As Object ' This will be used to hook into the Outlook
application
'
Sub CreateMailMerge()
Dim i As Long, j As Long
Dim row1 As Long, row2 As Long
'
' Make sure Outlook is actually running.
'
On Error GoTo Not_Running
Set olObj = GetObject(, "Outlook.Application")
On Error GoTo 0
'
' Get the range of rows for which emails will be generated
' (This is stuff I had to generate a series of emails from an Excel worksheet)
'
email_Range_Form.Show
'
row1 = email_Range_Form.StartRowTextBox.Value
row2 = email_Range_Form.EndRowTextBox.Value
If (row1 <= 1) Then Exit Sub
If (row2 < row1) Then Exit Sub
'
For i = row1 To row2 Step 1
Call DBEntryNotice(i)
Next i
'
Exit Sub
'
Not_Running:
MsgBox "Outlook does not appear to be running. Please run Outlook first."
'
End Sub
'
' DBEntryNotice Macro
' (This sub takes the data from one row of my worksheet and
' creates the email message)
'
Sub DBEntryNotice(theItem As Long)
Dim i As Long, j As Long, k As Long
'
Dim olItem As Object
Dim olRecipient As Object
'
' Get the required information from the Excel worksheet
'
' (Here is where I grabbed information from each row to create
' my emails - I stripped that out)
'
' Create a new blank mail message.
'
Set olItem = olObj.CreateItem(olMailItem)
Set myRecipients = olItem.Recipients
olItem.BodyFormat = olFormatPlain
olItem.Display
olItem.Subject = "Mail Concerning (Your Subject Here)"
'
' (Here you can add one or more recipients for the email)
'
Set olRecipient = olItem.Recipients.Add("valid email address string here")
'
If Not myRecipients.ResolveAll Then ' Outlook doesn't know who this is
MsgBox "Unable to resolve an email address for row number " & theItem
Exit Sub
End If
olItem.Body = "This is some body text. " & Chr(10)
olItem.Body = olItem.Body & "Here I am adding another line of text to
the body." & Chr(10)
olItem.Body = olItem.Body & "etc, etc" & Chr(10)
'
' Set some outlook options:
' Delivery receipt, Read receipt, Priority
'
olItem.OriginatorDeliveryReportRequested = True
olItem.ReadReceiptRequested = True
olItem.Importance = olImportanceHigh
'
' I was saving the emails and then sending them manually...
'
olItem.Close olSave
'
' But you can easily just send them as you go...
'
' olItem.Send
'
Exit Sub
'
End Sub

All, here are some specifics;
The spread sheet is a simpe list .
The first section (12 colums ) is the equipment description (vin#, make, year)
Second section is maintenance information i.e. date last pm was done, date
of next full service
the final section contains information about equipment paperwork such as
registration expiration date, or inspection date.
The idea is to have excel notify a site manager based on these dates and
times.
For example, is a vehicles registration will expire in 30 days, the site
manager will recieve and email stating" vehcle #7 registration expires in 30
days", then again at 15 dyas and again at 5days. Once the item is corrected
and the date changed the clock resets until the next time. This same pattern
would occur with all other items that expire or require attention.
I now this will require VB so i'm not sure if the better route would be to
go Access for the database.
P.S. Eric thanks for the info, I will review it tonight.

"Eric G" wrote:

Here are some old code fragments (which I haven't tested lately) that may
give you some idea of how to create and send an email directly from Excel. I
have stripped out some stuff, so it is incomplete, but will show you how to
declare your objects and use them to create and send an email.

HTH,

Eric


Option Explicit
'
' Global variables
'
Public olObj As Object ' This will be used to hook into the Outlook
application
'
Sub CreateMailMerge()
Dim i As Long, j As Long
Dim row1 As Long, row2 As Long
'
' Make sure Outlook is actually running.
'
On Error GoTo Not_Running
Set olObj = GetObject(, "Outlook.Application")
On Error GoTo 0
'
' Get the range of rows for which emails will be generated
' (This is stuff I had to generate a series of emails from an Excel worksheet)
'
email_Range_Form.Show
'
row1 = email_Range_Form.StartRowTextBox.Value
row2 = email_Range_Form.EndRowTextBox.Value
If (row1 <= 1) Then Exit Sub
If (row2 < row1) Then Exit Sub
'
For i = row1 To row2 Step 1
Call DBEntryNotice(i)
Next i
'
Exit Sub
'
Not_Running:
MsgBox "Outlook does not appear to be running. Please run Outlook first."
'
End Sub
'
' DBEntryNotice Macro
' (This sub takes the data from one row of my worksheet and
' creates the email message)
'
Sub DBEntryNotice(theItem As Long)
Dim i As Long, j As Long, k As Long
'
Dim olItem As Object
Dim olRecipient As Object
'
' Get the required information from the Excel worksheet
'
' (Here is where I grabbed information from each row to create
' my emails - I stripped that out)
'
' Create a new blank mail message.
'
Set olItem = olObj.CreateItem(olMailItem)
Set myRecipients = olItem.Recipients
olItem.BodyFormat = olFormatPlain
olItem.Display
olItem.Subject = "Mail Concerning (Your Subject Here)"
'
' (Here you can add one or more recipients for the email)
'
Set olRecipient = olItem.Recipients.Add("valid email address string here")
'
If Not myRecipients.ResolveAll Then ' Outlook doesn't know who this is
MsgBox "Unable to resolve an email address for row number " & theItem
Exit Sub
End If
olItem.Body = "This is some body text. " & Chr(10)
olItem.Body = olItem.Body & "Here I am adding another line of text to
the body." & Chr(10)
olItem.Body = olItem.Body & "etc, etc" & Chr(10)
'
' Set some outlook options:
' Delivery receipt, Read receipt, Priority
'
olItem.OriginatorDeliveryReportRequested = True
olItem.ReadReceiptRequested = True
olItem.Importance = olImportanceHigh
'
' I was saving the emails and then sending them manually...
'
olItem.Close olSave
'
' But you can easily just send them as you go...
'
' olItem.Send
'
Exit Sub
'
End Sub

Hi Mercer

Start with example 2 on this page
http://www.rondebruin.nl/mail/folder3/message.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Mercer" wrote in message ...
All, here are some specifics;
The spread sheet is a simpe list .
The first section (12 colums ) is the equipment description (vin#, make, year)
Second section is maintenance information i.e. date last pm was done, date
of next full service
the final section contains information about equipment paperwork such as
registration expiration date, or inspection date.
The idea is to have excel notify a site manager based on these dates and
times.
For example, is a vehicles registration will expire in 30 days, the site
manager will recieve and email stating" vehcle #7 registration expires in 30
days", then again at 15 dyas and again at 5days. Once the item is corrected
and the date changed the clock resets until the next time. This same pattern
would occur with all other items that expire or require attention.
I now this will require VB so i'm not sure if the better route would be to
go Access for the database.
P.S. Eric thanks for the info, I will review it tonight.

"Eric G" wrote:

Here are some old code fragments (which I haven't tested lately) that may
give you some idea of how to create and send an email directly from Excel. I
have stripped out some stuff, so it is incomplete, but will show you how to
declare your objects and use them to create and send an email.

HTH,

Eric


Option Explicit
'
' Global variables
'
Public olObj As Object ' This will be used to hook into the Outlook
application
'
Sub CreateMailMerge()
Dim i As Long, j As Long
Dim row1 As Long, row2 As Long
'
' Make sure Outlook is actually running.
'
On Error GoTo Not_Running
Set olObj = GetObject(, "Outlook.Application")
On Error GoTo 0
'
' Get the range of rows for which emails will be generated
' (This is stuff I had to generate a series of emails from an Excel worksheet)
'
email_Range_Form.Show
'
row1 = email_Range_Form.StartRowTextBox.Value
row2 = email_Range_Form.EndRowTextBox.Value
If (row1 <= 1) Then Exit Sub
If (row2 < row1) Then Exit Sub
'
For i = row1 To row2 Step 1
Call DBEntryNotice(i)
Next i
'
Exit Sub
'
Not_Running:
MsgBox "Outlook does not appear to be running. Please run Outlook first."
'
End Sub
'
' DBEntryNotice Macro
' (This sub takes the data from one row of my worksheet and
' creates the email message)
'
Sub DBEntryNotice(theItem As Long)
Dim i As Long, j As Long, k As Long
'
Dim olItem As Object
Dim olRecipient As Object
'
' Get the required information from the Excel worksheet
'
' (Here is where I grabbed information from each row to create
' my emails - I stripped that out)
'
' Create a new blank mail message.
'
Set olItem = olObj.CreateItem(olMailItem)
Set myRecipients = olItem.Recipients
olItem.BodyFormat = olFormatPlain
olItem.Display
olItem.Subject = "Mail Concerning (Your Subject Here)"
'
' (Here you can add one or more recipients for the email)
'
Set olRecipient = olItem.Recipients.Add("valid email address string here")
'
If Not myRecipients.ResolveAll Then ' Outlook doesn't know who this is
MsgBox "Unable to resolve an email address for row number " & theItem
Exit Sub
End If
olItem.Body = "This is some body text. " & Chr(10)
olItem.Body = olItem.Body & "Here I am adding another line of text to
the body." & Chr(10)
olItem.Body = olItem.Body & "etc, etc" & Chr(10)
'
' Set some outlook options:
' Delivery receipt, Read receipt, Priority
'
olItem.OriginatorDeliveryReportRequested = True
olItem.ReadReceiptRequested = True
olItem.Importance = olImportanceHigh
'
' I was saving the emails and then sending them manually...
'
olItem.Close olSave
'
' But you can easily just send them as you go...
'
' olItem.Send
'
Exit Sub
'
End Sub