Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel open in outlook if outlook is running | Excel Discussion (Misc queries) | |||
Outlook 11 Outlook 10 Object Library Compatibility Issues | Excel Programming | |||
Outlook 2003 demo back to Outlook XP | Excel Discussion (Misc queries) | |||
Late Binding to Outlook from Excel: Outlook modifies email body | Excel Programming | |||
Display mail application either in Outlook, or Outlook express | Excel Programming |