automated bet placement from email tip

[jonn]

Hello,

I was wondering if anyone does this or knows whether it is possible?

I am looking to find a way of automating bet placements of my email alert tips that come into my googlemail inbox.

The tips follow the same format every time as follows:

Tipster tipster1
Event 4:35 Goodwood
When 28/07/09 16:35
Selection £250 Sugar Rayz
Comments None
Odds 10

Sometimes the tips come in with short notice (as low as 5 mins before start time).

There are variations i would like to expand upon, but first i would like to know if it is feasible and at what cost. It has been suggested that if i could get the text into an excel document gruss software could do it from there?

[throwmeadisc]

Hi John,

I have a bit of Outlook VBA code that I have written to extract tables and text from an email and put into an excel spraedsheet.

It ain't pretty but it does the job. Maybe you could modify it and make it work for you, it would be a good starting block at least.

My code is at home so I'll have to post it later...

Al

[Norwegian Would]

Al - I would appreciate your posting as I have a similar need.

Don't care about "prettyness" - beauty is only skin deep anyway.

Cheers

[throwmeadisc]

OK here goes:

So to get this Outlook code to work I have set up a rule in the Rules Wizard to Run a Script after a message arrives. You can put whatever criteria you like on it as long as it triggers for you.

I also run this from a Google account using POP3, I tried IMAP before but I had problems but don't remember why, it might have been something to do with how they differently send HTML emails. Not sure but POP3 works for me.

My recommendation is to just have a separate Tips Email account or something similar for this, it makes things a whole lot easier and it doesn't get mixed up with your regular email.

The code that I have is pretty much just copied this out of my Outlook VBA so it's not the tidiest code, but it works for me at least.

Basic Theory behind the code:
It looks for an Excel Workbook called Feeder.xls where I do various formatting of data etc because I find it easier to work with Excel VBA than Outlook. This routine is basically designed to run when an email is received, find a Excel Workbook, Copy any tables in the HTML email to Excel and then copy the email text to one cell in Excel.

From there the Feeder.xls workbook processes that data and then gives a 'Processed' Flag back to the Outlook code to continue. It was done this way so Outlook didn't go through a backlog of emails without the Feeder.xls workbook having chance to process the data before being overwritten.

OK the Outlook Code, you'll need to go into Outlook VBA by pressing Alt+F11 and put this in a new module:

Code: Select all

Sub ToExcel(msg As Outlook.MailItem)
'FOR FEEDER Sheet............
Dim i As Integer
Dim j As Integer
Dim strID As String
Dim olNS As Outlook.NameSpace
Dim insp As Outlook.Inspector

Dim MsgDate As Date

Dim webMsg As MSHTML.HTMLDocument
Dim htable As MSHTML.HTMLTable
Dim hrow As MSHTML.HTMLTableRow
Dim hcell As MSHTML.HTMLTableCell
Dim colTables As MSHTML.IHTMLElementCollection
Dim colRows As MSHTML.IHTMLElementCollection
Dim colCells As MSHTML.IHTMLElementCollection

Dim exc As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

On Error GoTo ErrorSub:

Set exc = GetObject(, "Excel.Application")
Set wb = exc.Workbooks("Feeder.xls")           'References Workbook
Set ws = wb.Sheets("Email In")                     'References Worksheet

exc.Visible = True
ws.Activate

With exc.Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

strID = msg.EntryID
Set olNS = Application.GetNamespace("MAPI")
Set msg = olNS.GetItemFromID(strID)

Set insp = msg.GetInspector
Set webMsg = insp.HTMLEditor

Do Until webMsg.readyState = "complete"    'Waits until Email is fully opened
    DoEvents
Loop

ws.Range("A1:O1000").Clear            'Clears working area

'Extracts HTML Tables from Email
Set colTables = webMsg.getElementsByTagName("table")
i = 2                                           ' offset, change as needed to adjust the blank rows at the top
For Each htable In colTables
    i = i + 1                                  ' sets blank rows between tables
    Set colRows = htable.rows
    For Each hrow In colRows
        i = i + 1                              ' increment row index
        Set colCells = hrow.Cells
        For Each hcell In colCells
            j = hcell.cellIndex + 1
            ws.Cells(i, j) = Trim(hcell.innerText)
        Next
    Next
Next


ws.Range("N1").Value = "Time of Extract:"
ws.Range("O1").Value = Now
ws.Range("N3").Value = "Email Date:"
ws.Range("O3").Value = msg.SentOn
ws.Range("O4").Clear
ws.Range("O4").Value = msg.Body            ' This is any email text

With exc.Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

ws.Range("O2").Value = "Changed"           'To trigger a Macro in Excel to process data

Do
Loop Until ws.Range("O2").Value = "Processed"        'Waits for Excel to finish processing the data from Outlook

ExitSub:
With exc.Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

ErrorSub:
On Error Resume Next
Set exc = Nothing
Set wb = Nothing
Set ws = Nothing
Set msg = Nothing
Set insp = Nothing
Set olNS = Nothing
Set webMsg = Nothing
Set colCells = Nothing

End Sub


I hope some you find this useful. I am no big coding junkie but I am happy to answer any questions on this code. It may not be the best way to do this but I have had it working for over 1.5 years now so I find it quite reliable. Good luck.

Cheers,

Al

p.s. Please remember me if this helps you make it to the big time. I am also happy to hear about quality tipsters or strategies, publicly or privately!