Linking live Excel data to Outlook automatically

[Roark]

Hello,


My problem is that my company’s investment team uses a spreadsheet that has a live feed from Reuters. This spreadsheet provides intraday changes on all of the firm’s equities and portfolios. Currently one person opens the spreadsheet and copy and pastes the data into an e-mail and sends it out every 20 or so minutes.

The investment team hates this, as it is a burden on the person that has to copy and paste the data into a new e-mail about 20 times a day. Also if this person is away from the desk then the e-mail cannot be sent. I want to know if it is possible to set up Excel and Outlook so that Outlook automatically sends an e-mail to the investment team every 20 minutes with the latest data from the excel spreadsheet. This is a little beyond my knowledge and I don't even know if it is possible. Any help would be much appreciated.

Roark

[firefytr]

Hi there, welcome to the board!

Well of course this can be done! I'm moving this question into the Excel forum.

Here is a shell of what you may need ...

[vba]Option Explicit

Sub Start_Procedure()
Application.OnTime Now + TimeValue("00:00:02"), "My_Procedure"
End Sub

Sub My_Procedure()
Dim EndHour As Long
Dim Msg As VbMsgBoxResult
EndHour = 17 'military time, 5:00 pm
Msg = MsgBox("Hello. Continue?", vbYesNo, "Keep going?")
If Msg = vbNo Then Exit Sub
If Hour(Now) > EndHour Then Exit Sub
Call Start_Procedure
End Sub[/vba]

This gives you a general idea of what you may need. Of course this can be tweaked, but it's the basics. I put the message box in there because I wanted a way to stop it. Now, this may not suit your purposes as you would have to have an actual person click 'Yes' to run the routine. But if you took that out, it would run no matter what. This can sometimes be dangerous, unless you are under strict control of the situation.

If you'd like to automate this further, which it sounds like we can, just post all your details.

[Roark]

thanks for the welcome!

your reply was helpful in getting me started in the right direction...i am not an IT professional, i am the unfornuate soul that has to copy the data from excel and send out an e-mail every 20 minutes. I must admit to not understanding 100% of your answer

if it helps my dream scenario would be to have an icon on my desktop that i could click that would send the updated information in an e-mail to 4 receipants...barring that if an automatic email was generated every 20 minutes that contained the newly copied data from excel starting at 09:30 until 16:00 that would be fantastic

its difficult to plan this because the live data feed from reuters is only from mon-fri and i hardly have a spare moment when the markets are open to play around with this. but it gives me hope to know that it can be done though

[firefytr]

Originally Posted by Roark

if it helps my dream scenario would be to have an icon on my desktop that i could click that would send the updated information in an e-mail to 4 receipants...barring that if an automatic email was generated every 20 minutes that contained the newly copied data from excel starting at 09:30 until 16:00 that would be fantastic

We could have a file you place on your desktop that when you opened it, this would start such a routine to send out an email every 20 minutes. The problem lies in the fact that it must be started by a user. If this is something that you would open every morning or when you wanted to start sending out emails, then I think we could work something out.

Question: What exactly do you want to send out in your email? Is it only the 4 recipients? Will any of the information ever change? For their ease, do you have a naming convention you would like to title the emails (subject)?

[Roark]

the data i would like to send is from a tab in excel called "Summary" it lists our 8 funds and the dollar amounts they are up or down, this data is changing every second as the summary numbers are linked to an information provider reuters

opening the file is no problem, it is much easier to click on an icon once than to crl V the data into an e-mail every 20 minutes

so think of a table like so

Net Change % Change
Fund X $44,000.87 1.2
Fund Y $1.23 0.01
Fund Z $-87,776.34 -2.3

the dollar figures are a formula that is calculated from a live feed...everyone on the investment team likes to know how much we are up or down every 20 minutes. Right now i copy the above table and paste it into an e-mail and send it out. The e-mail is titled "update" and contains nothing but the table. It goes to 5 people with a conventional e-mail adress Nick.Kinsley@Officeexperts.com

[firefytr]

Okay, well, the code is the easy part. If you are using Outlook, then Outlook is the hard part. First things first, here is some code which works for me...


[vba]Option Explicit

Sub Start_Procedure()
Application.OnTime Now + TimeValue("00:20:00"), "SendEmails"
End Sub

Sub SendEmails()

Dim wb As Workbook
Dim Addys(1 To 5) As String
Dim strWBname As String, strWBpath As String
Dim SUBJECT As String
Dim i As Long
Dim WasOpen As Boolean
Dim ws As Variant

If Time > TimeValue("16:00:00") Then
MsgBox "Quitting time!"
Exit Sub
End If

Application.ScreenUpdating = False

strWBpath = "C:\Documents and Settings\Rob\Desktop\" 'change to suit
strWBname = "Test.xls" 'change to suit
ws = "" 'Enter sheet name, or leave blank to assume first sheet in book
If ws = "" Then ws = CLng(1)
Addys(1) = "zackb@portofmorrow.com" 'change as needed..
Addys(2) = "zackb2@portofmorrow.com"
Addys(3) = "zackb3@portofmorrow.com"
Addys(4) = "zackb4@portofmorrow.com"
Addys(5) = "zackb5@portofmorrow.com"
SUBJECT = "My Email Test" 'change to desired
If Not IsWbOpen(strWBname) Then
Set wb = Workbooks.Open(strWBpath & strWBname): WasOpen = False
Else: Set wb = Workbooks(strWBname): WasOpen = True
End If
For i = LBound(Addys) To UBound(Addys)
'set filename as last syntax, change as desired...
Email_Sheet wb.Name, ws, Addys(i), SUBJECT, "temp" & i
Next i
If Not WasOpen Then wb.Close False

Application.ScreenUpdating = True

Call Start_Procedure

End Sub

Function IsWbOpen(wbName As String) As Boolean
On Error Resume Next
IsWbOpen = Len(Workbooks(wbName).Name)
End Function

Sub Email_Sheet(ByVal strWb As String, _
ByVal strWs As Variant, _
ByVal strAddress As String, _
ByVal strSubject As String, _
ByVal strSaveAs As String)
' Files will NOT BE SAVED!
Dim wbSend As Workbook
Dim wsSend As Worksheet
Set wsSend = Workbooks(strWb).Sheets(strWs)
wsSend.Copy
Set wbSend = ActiveWorkbook
With wbSend
.SaveAs "C:\" & strSaveAs & ".xls"
.SendMail strAddress, strSubject
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Set wsSend = Nothing
Set wbSend = Nothing
End Sub[/vba]

But the problem comes into this due to the fact that Outlook will not let anything send automatically on it's own without prior authorization from a user. This is just default Outlook for ya. The only way (that I know how to right now) to combat this with any effectiveness, is to use Outlook Redemption. That you would have to do on your own and can be found quite easily by Googling it. Your best bet is either OutlookCode.com or SlipStick.com, two of the worlds most leading Outlook web sites in the world.

Oh, and the other method I know of is using SendKeys, which is really unreliable, especially for an 'unmanned' station process.

HTH