Need help searching for "\\192" in body of new email

13 Nov 2007 7:41 PM

mikedavid00

Hi,

It's rare I post for help without thoroughly searching, but I can't
seem to find anything on this.

We get automated email pointing to a network share for a file that is
accessed by everyone. However, we need to copy it over to our local
computers before we can work with it.

I need to be able to scan the body of the email for the URL and then
copy the file over locally. This is how the URL would look in the body
of the email:

\\192.168.0.2\Orders\SpreadsheetOrder20071112.XLS

The file name changes daily so this code needs to search the body for
"\\192" and then *select the block of text* then use the CopyFile
method to copy the spreadsheet over.

If this was word I'd have no problems, but I honestly have no clue at
all how to do this using Item.Body.

I do faintly recall an old vb function that split all words into an
array, did an Instr, and then returned the matching full world. But I
simply cannot find it it's been so long. I guess that would work to.

Any help would be greatly appreciated.

(Also, is there a way to test my vb code a bit faster when working
with Mail.Items?)

13 Nov 2007 10:03 PM

JP

I'm no expert on Outlook VB but here is something that might get you
started. This code will check unread emails for your string and then
pop up a msgbox with some info about the email.

Sub CheckNewEmails()

Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olInbox As Outlook.MAPIFolder
Dim Item As Outlook.MailItem
Dim i As Long

Application.ScreenUpdating = False

Set olApp = GetObject(, "outlook.application")
Set olNS = olApp.GetNamespace("MAPI")
Set olInbox = olNS.GetDefaultFolder(olFolderInbox)

If olInbox.UnReadItemCount > 0 Then
i = 0
    For Each Item In olInbox.Items.Restrict("[Unread] = True")
            If instr(1,item.Body,"\\192") <> 0 Then
                    i = i + 1
                    MsgBox "Message #" & i & " of " &
olInbox.UnReadItemCount & " contains the string you are looking for."
& vbCr & "To: " & Item.To & vbCr & "From: " & Item.SenderName & _
                           vbCr & "Subject: " & Item.Subject
    Next Item
Else
    MsgBox "No new/unread emails", vbInformation
End If

End sub

I didn't test this code. Hopefully it helps.

--JP

On Nov 13, 2:41 pm, mikedavi***@HOTMAIL.COM wrote:
Show quote

> Hi,
>
> It's rare I post for help without thoroughly searching, but I can't
> seem to find anything on this.
>
> We get automated email pointing to a network share for a file that is
> accessed by everyone. However, we need to copy it over to our local
> computers before we can work with it.
>
> I need to be able to scan the body of the email for the URL and then
> copy the file over locally. This is how the URL would look in the body
> of the email:
>
> \\192.168.0.2\Orders\SpreadsheetOrder20071112.XLS
>
> The file name changes daily so this code needs to search the body for
> "\\192" and then *select the block of text* then use the CopyFile
> method to copy the spreadsheet over.
>
> If this was word I'd have no problems, but I honestly have no clue at
> all how to do this using Item.Body.
>
> I do faintly recall an old vb function that split all words into an
> array, did an Instr, and then returned the matching full world. But I
> simply cannot find it it's been so long. I guess that would work to.
>
> Any help would be greatly appreciated.
>
> (Also, is there a way to test my vb code a bit faster when working
> with Mail.Items?)

14 Nov 2007 2:16 PM

mikedavid00

>     For Each Item In olInbox.Items.Restrict("[Unread] = True")
>             If instr(1,item.Body,"\\192") <> 0 Then
>                     i = i + 1
>                     MsgBox "Message #" & i & " of " &
> olInbox.UnReadItemCount & " contains the string you are looking for."

Thanks for the help.

I guess what I need is a way to say, once "//192" is found in the body
of the text, how does it select the 'block' of text once the wildcard
has found a match? I need to isolate the URL address within the email.
Double click on any of the words in this message and that's what I
mean.

I guess I really need to find a Vb split functions which will split
all the words in the email into an array and then search each one for
"http" then choose that full word for the URL.

I might be able to find this somewhere. Thanks for the help in
narrowing down what I need though.

14 Nov 2007 2:51 PM

mikedavid00

Here's the solution. I just did it myself. It basically will take a
string (mailitem.body) and then split everything into words and return
your specified 'word' that matches your wildcard.

For the below example, you would search "//192" and you would get
back: "\\192.168.0.1\Orders\order2007111210.XLS"

That network share URL then gives you the ability to to use CopyFile
to copy that file over locally despite that the file name changes each
day.

Sub TestString()

Dim str As String
str = "Hello A new order file has been downloaded. \\192.168.0.1\Orders
\order2007111210.XLS 11/12/2007 11:00:15 AM"

Debug.Print GetURLfromWildCard(str, "\\192")

End Sub

Function GetURLfromWildCard(str As String, srchstring As String) As
String

Dim arrName As Variant

arrName = Split(str, " ")

For Each Item In arrName
    If InStr(1, Item, srchstring) <> 0 Then
        GetURLfromWildCard = Item
    End If
Next

End Function

14 Nov 2007 3:48 PM

JP

Great job! Split or Instr or even Find would help. You could use
"Instr" on Item.Body to get the position of the "\\192" then the Mid$
function to return the exact string you want, starting from the
position of "\\192" in the email body.

--JP

On Nov 14, 9:51 am, mikedavi***@HOTMAIL.COM wrote:
Show quote

> Here's the solution. I just did it myself. It basically will take a
> string (mailitem.body) and then split everything into words and return
> your specified 'word' that matches your wildcard.
>
> For the below example, you would search "//192" and you would get
> back: "\\192.168.0.1\Orders\order2007111210.XLS"
>
> That network share URL then gives you the ability to to use CopyFile
> to copy that file over locally despite that the file name changes each
> day.
>
> Sub TestString()
>
> Dim str As String
> str = "Hello A new order file has been downloaded. \\192.168.0.1\Orders
> \order2007111210.XLS 11/12/2007 11:00:15 AM"
>
> Debug.Print GetURLfromWildCard(str, "\\192")
>
> End Sub
>
> Function GetURLfromWildCard(str As String, srchstring As String) As
> String
>
> Dim arrName As Variant
>
> arrName = Split(str, " ")
>
> For Each Item In arrName
>     If InStr(1, Item, srchstring) <> 0 Then
>         GetURLfromWildCard = Item
>     End If
> Next
>
> End Function