Excel > Outlook

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

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]

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)

 

[Guest]

Ah ha...

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

Gordon.