Excel Outlook
 

I have 4 columns in my spreadsheet.

A: The date an email should be sent.
B: The email address a message is to be sent to.
C: The message to be sent.

Can I format column A so the dates themsleves are all hyperlinks that
trigger the formatting of a basic email to contain the message (column B) and
recipients email address (column C)

Blue sky thinking here but any help will be much appreciated.

Thanks

Gordon.

Excel Outlook
 

Gordon,

here is some code. It assumes the dates are in A1:A20. Just click on one to
fire the event code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
I have 4 columns in my spreadsheet.

A: The date an email should be sent.
B: The email address a message is to be sent to.
C: The message to be sent.

Can I format column A so the dates themsleves are all hyperlinks that
trigger the formatting of a basic email to contain the message (column B)
and
recipients email address (column C)

Blue sky thinking here but any help will be much appreciated.

Thanks

Gordon.

Excel Outlook
 

Hi Bob...

Were is the code you speak of below. Either you or I have had too much of
the xmas sherry lol!

Thanks

Gordon

"Bob Phillips" wrote:

Gordon,

here is some code. It assumes the dates are in A1:A20. Just click on one to
fire the event code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
I have 4 columns in my spreadsheet.

A: The date an email should be sent.
B: The email address a message is to be sent to.
C: The message to be sent.

Can I format column A so the dates themsleves are all hyperlinks that
trigger the formatting of a basic email to contain the message (column B)
and
recipients email address (column C)

Blue sky thinking here but any help will be much appreciated.

Thanks

Gordon.

Excel Outlook
 

Hi Gord,

Well, it is Chrsitmas. Try again

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object

If Not Intersect(Target, Me.Range("A1:A20")) Is Nothing Then
If Target.Value = Date Then
Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add(Target.Offset(0, 2).Value)
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
oMailItem.Subject = "Automatic notification"
oMailItem.Body = Target.Offset(0, 1).Value
oMailItem.display

Set oRecipient = Nothing
Set oMailItem = Nothing
Set oNameSpace = Nothing
Set oOutlook = Nothing
End If
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
Hi Bob...

Were is the code you speak of below. Either you or I have had too much of
the xmas sherry lol!

Thanks

Gordon

"Bob Phillips" wrote:

Gordon,

here is some code. It assumes the dates are in A1:A20. Just click on one
to
fire the event code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
I have 4 columns in my spreadsheet.

A: The date an email should be sent.
B: The email address a message is to be sent to.
C: The message to be sent.

Can I format column A so the dates themsleves are all hyperlinks that
trigger the formatting of a basic email to contain the message (column
B)
and
recipients email address (column C)

Blue sky thinking here but any help will be much appreciated.

Thanks

Gordon.

Excel Outlook
 

Hi Bob...

As a demo I've inserted the code into the page, and entered 1 row of info
(Row 3). What triggers the code to run?

Thanks...I hope your're enjoying some mince pies...

Gordon

"Bob Phillips" wrote:

Hi Gord,

Well, it is Chrsitmas. Try again

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object

If Not Intersect(Target, Me.Range("A1:A20")) Is Nothing Then
If Target.Value = Date Then
Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add(Target.Offset(0, 2).Value)
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
oMailItem.Subject = "Automatic notification"
oMailItem.Body = Target.Offset(0, 1).Value
oMailItem.display

Set oRecipient = Nothing
Set oMailItem = Nothing
Set oNameSpace = Nothing
Set oOutlook = Nothing
End If
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
Hi Bob...

Were is the code you speak of below. Either you or I have had too much of
the xmas sherry lol!

Thanks

Gordon

"Bob Phillips" wrote:

Gordon,

here is some code. It assumes the dates are in A1:A20. Just click on one
to
fire the event code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
I have 4 columns in my spreadsheet.

A: The date an email should be sent.
B: The email address a message is to be sent to.
C: The message to be sent.

Can I format column A so the dates themsleves are all hyperlinks that
trigger the formatting of a basic email to contain the message (column
B)
and
recipients email address (column C)

Blue sky thinking here but any help will be much appreciated.

Thanks

Gordon.

Excel Outlook
 

Gordon,

It is event code that is triggered by selecting one of the date cells in
A1:A20. If that date is today, the email fires.

If you wanted, I could amend it to loop through all the dates whenever any
is selected, and send any appropriate ones.

BTW ... I don't like mince pies :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
Hi Bob...

As a demo I've inserted the code into the page, and entered 1 row of info
(Row 3). What triggers the code to run?

Thanks...I hope your're enjoying some mince pies...

Gordon

"Bob Phillips" wrote:

Hi Gord,

Well, it is Chrsitmas. Try again

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object

If Not Intersect(Target, Me.Range("A1:A20")) Is Nothing Then
If Target.Value = Date Then
Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add(Target.Offset(0, 2).Value)
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
oMailItem.Subject = "Automatic notification"
oMailItem.Body = Target.Offset(0, 1).Value
oMailItem.display

Set oRecipient = Nothing
Set oMailItem = Nothing
Set oNameSpace = Nothing
Set oOutlook = Nothing
End If
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
Hi Bob...

Were is the code you speak of below. Either you or I have had too much
of
the xmas sherry lol!

Thanks

Gordon

"Bob Phillips" wrote:

Gordon,

here is some code. It assumes the dates are in A1:A20. Just click on
one
to
fire the event code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
I have 4 columns in my spreadsheet.

A: The date an email should be sent.
B: The email address a message is to be sent to.
C: The message to be sent.

Can I format column A so the dates themsleves are all hyperlinks
that
trigger the formatting of a basic email to contain the message
(column
B)
and
recipients email address (column C)

Blue sky thinking here but any help will be much appreciated.

Thanks

Gordon.

Excel Outlook
 

Ah ha...

Thanks...hope you have a good one...

Gordon.

"Bob Phillips" wrote:

Gordon,

It is event code that is triggered by selecting one of the date cells in
A1:A20. If that date is today, the email fires.

If you wanted, I could amend it to loop through all the dates whenever any
is selected, and send any appropriate ones.

BTW ... I don't like mince pies :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
Hi Bob...

As a demo I've inserted the code into the page, and entered 1 row of info
(Row 3). What triggers the code to run?

Thanks...I hope your're enjoying some mince pies...

Gordon

"Bob Phillips" wrote:

Hi Gord,

Well, it is Chrsitmas. Try again

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object

If Not Intersect(Target, Me.Range("A1:A20")) Is Nothing Then
If Target.Value = Date Then
Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add(Target.Offset(0, 2).Value)
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
oMailItem.Subject = "Automatic notification"
oMailItem.Body = Target.Offset(0, 1).Value
oMailItem.display

Set oRecipient = Nothing
Set oMailItem = Nothing
Set oNameSpace = Nothing
Set oOutlook = Nothing
End If
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
Hi Bob...

Were is the code you speak of below. Either you or I have had too much
of
the xmas sherry lol!

Thanks

Gordon

"Bob Phillips" wrote:

Gordon,

here is some code. It assumes the dates are in A1:A20. Just click on
one
to
fire the event code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gordon" wrote in message
...
I have 4 columns in my spreadsheet.

A: The date an email should be sent.
B: The email address a message is to be sent to.
C: The message to be sent.

Can I format column A so the dates themsleves are all hyperlinks
that
trigger the formatting of a basic email to contain the message
(column
B)
and
recipients email address (column C)

Blue sky thinking here but any help will be much appreciated.

Thanks

Gordon.