From Outlook to Excel in VBA 1

[LuAlPa]

Hello, everyone there ...
I receive about one hundred e-mails per day where, most of the time, I need to pick up order numbers (7 digits) and paste it to Excel or to navigate another appliication (Hummingbird) - I have succeded in interfacing Excel with Hummingbird but I have failed miserably in picking up data from an email in Outlook. Any suggestion? code?
Thank you in advance ---- LuAlPa

 

[CautionMP]

LuAlPa,
I have no idea what you have tried, but here is an idea for getting data from Outlook to Excel.
VBA Visual Basic for Applications (Microsoft) Forum: exporting data from Outlook to Excel

As far as pulling the 7 digit order number[ul]
[li]Where is the number (body, Subject...)?[/li]
[li]Is there a prefix that identifies the order number ([tt]i.e. Order # nnnnnnn[/tt])?[/li]
[li]Can you provide a sample of what your working with?[/li][/ul]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[LuAlPa]

You are right, CMP --- I was less than clear.
The order number can be either in the subject or in the textwith no particular identifier. I would be glad to be able to double click on the number (select) and then with a keystroke combination run the code that would take me to Exce or Hummingbird

 

[CautionMP]

LuAlPa,
I see the issue, I can't find a way to reference the Selected Text in Outlook without using a UserForm.

Here is a starting point, it works but it's not real pretty. If you select the text you want and Copy it to the Clipboard you could then use this routine to move it to Excel (I used a Toolbar button that fired the Macro).

Public Sub SelectedInspectorTextToExcel()
Dim appExcel As Object
Dim objWorkBook As Object, objWorksheet As Object
Dim strCopiedValue As String
strCopiedValue = Trim(ClipBoard_GetData)
If strCopiedValue <> "" Then
  Set appExcel = CreateObject("Excel.Application")
  Set objWorkBook = appExcel.WorkBooks.Open("C:\Documents and Settings\Brett\Desktop\OutlookPaste.xls")
  Set objWorksheet = objWorkBook.sheets("Sheet1")
  objWorksheet.Cells(objWorksheet.UsedRange.Rows.Count + 1, 1) = strCopiedValue
  Set objWorksheet = Nothing
  With objWorkBook
    .Save
    .Close
  End With
  Set objWorkBook = Nothing
  appExcel.Quit
  Set appExcel = Nothing
End If
End Sub

And here is the ugly piece that gets the information from the Clipboard, I usually keep it in it's own module.

Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function GetClipboardData Lib "User32" (ByVal wFormat As Long) As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal dwBytes As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Function ClipBoard_GetData()
Dim hClipMemory As Long
Dim lpClipMemory As Long
Dim MyString As String
Dim RetVal As Long
If OpenClipboard(0&) = 0 Then
    MsgBox "Cannot open Clipboard. Another app. may have it open"
    Exit Function
End If

' Obtain the handle to the global memory block that is referencing the text.
hClipMemory = GetClipboardData(CF_TEXT)
If IsNull(hClipMemory) Then
    MsgBox "Could not allocate memory"
    GoTo Clean_Up
End If

' Lock Clipboard memory so we can reference the actual data string.
lpClipMemory = GlobalLock(hClipMemory)
If Not IsNull(lpClipMemory) Then
    MyString = Space$(MAXSIZE)
    RetVal = lstrcpy(MyString, lpClipMemory)
    RetVal = GlobalUnlock(hClipMemory)
    ' Peel off the null terminating character.
    MyString = Mid(MyString, 1, InStr(1, MyString, Chr$(0), 0) - 1)
Else
    MsgBox "Could not lock memory to copy string from."
End If
Clean_Up:
RetVal = CloseClipboard()
ClipBoard_GetData = MyString
End Function

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)