Help with Macro please...

Hi Everyone

I've come across a macro which I have tried using, but nothing seems to 
happen. This macro is run from Excel and interacts with Outlook, so I've made 
sure to turn on the Microsoft Outlook 11.0 Object Library in TOOLS - 
REFERENCES from the VBE window.

If Outlook is not running, I do get the error message I'm supposed to get 
(i.e. "No message selected"), but beyond that I have absolutely no indication 
that the Macro actually does anything.

The code is as follows:

Sub CopyFromOutlook()
    Dim olApp As New Outlook.Application
    Dim olExp As Outlook.Explorer
    Dim olSel As Outlook.Selection
    Dim myArray(8) As String
    Dim Line As Long, Addr1 As String
    Dim Tabl, str As String, EmailAddress, DOB
    Dim i As Integer, x As Integer, n As Integer, j As Integer
    
    On Error Resume Next
    ' Getting the messages selection
    Set olApp = Outlook.Application
    Set olExp = olApp.ActiveExplorer
    Set olSel = olExp.Selection
    ' Checking if there is at least one message selected
    If olSel.Count < 1 Then
        MsgBox "No message selected", vbExclamation, "Error"
        Exit Sub
    End If
    With Sheets("EditData")
    ' Retrieving the first avaible row to put message in
    Line = .Range("A65000").End(xlUp).Row + 1
    ' looping through message
    For x = 1 To olSel.Count
        DoEvents
        Erase myArray
        mybody = Replace(olSel.Item(x).body, Chr(13), "")
        ' Splitting the message body into an array of substrings,
        ' using the "line feed" characters as separators
        'mybody = Replace(mybody, Chr(10) & Chr(10), Chr(10))
        Tabl = Split(mybody, Chr(10))
        For Each Item In Tabl
            Item = Replace(Item, Chr(10), "")
            Item = Application.Clean(Item)
        Next Item
        ' Looping through these substrings
        For i = 0 To UBound(Tabl)
            ' Looking for the surname field
            If LCase(Left(Tabl(i), 9)) = "last name" Then
                .Cells(Line, 2) = Application.Proper(Mid(Tabl(i), 13, 999))
            ElseIf LCase(Left(Tabl(i), 10)) = "othsurname" Then
                .Cells(Line, 2) = Application.Proper(Mid(Tabl(i), 14, 999))
            ' Looking for the first name field
            ElseIf LCase(Left(Tabl(i), 10)) = "first name" Then
                .Cells(Line, 1) = Application.Proper(Mid(Tabl(i), 14, 999))
            ElseIf LCase(Left(Tabl(i), 12)) = "othfirstname" Then
                .Cells(Line, 1) = Application.Proper(Mid(Tabl(i), 16, 999))
            ' Looking for the zip code
            ElseIf Left(Tabl(i), 11) = "Postcode = " Then
                .Cells(Line, 7) = Mid(Tabl(i), 12, 999)
            ' Looking for the date of birth field
            ElseIf Left(Tabl(i), 3) = "DOB" Then
                If IsDate(Mid(Tabl(i), 7, 999)) Then
                    .Cells(Line, 8) = CDate(Trim(Mid(Tabl(i), 7, 999)))
                End If
            'looking for the address
            ElseIf UCase(Left(Tabl(i), 5)) = "LINE1" Then
                .Cells(Line, 3) = Mid(Tabl(i), 8, 999)
            ElseIf UCase(Left(Tabl(i), 5)) = "LINE2" Then
                .Cells(Line, 4) = Mid(Tabl(i), 8, 999)
            ElseIf UCase(Left(Tabl(i), 6)) = "TOWN =" Then
                .Cells(Line, 5) = Mid(Tabl(i), 7, 999)
            ElseIf UCase(Left(Tabl(i), 11)) = "TOWN/CITY =" Then
                .Cells(Line, 5) = Mid(Tabl(i), 12, 999)
            ElseIf UCase(Left(Tabl(i), 6)) = "COUNTY" Then
                .Cells(Line, 6) = Mid(Tabl(i), 9, 999)
            ' Looking for the email address
            ElseIf UCase(Left(Tabl(i), 7)) = "EMAIL =" Then
                .Cells(Line, 9) = Mid(Tabl(i), 9, 999)
            ElseIf UCase(Left(Tabl(i), 11)) = "FROMEMAIL =" Then
                .Cells(Line, 9) = Mid(Tabl(i), 13, 999)
            End If
        Next i
        Line = Line + 1
    ' Next message
    Next x
    End With
End Sub


Any help would be greatly appreciated. I'm running Outlook 2003 and Excel 
2003.

Thanks,

Joe.
-- 
If you can measure it, you can improve it!
0
Utf
12/15/2009 4:53:01 AM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
749 Views

Similar Articles

[PageSpeed] 52

Oops, forgot to mention that I have tried selecting messages in Outlook 
before running the Macro in Excel, but this seems to have no effect. Not 
selecting any messages beforehand also has no effect.


0
Utf
12/15/2009 5:38:01 AM
What happens if you remove the "On Error Resume Next" ?

Tim



"Monomeeth" <Monomeeth@discussions.microsoft.com> wrote in message 
news:EA4DCCC2-5E14-4D91-8B09-6D5FD4083BD6@microsoft.com...
> Oops, forgot to mention that I have tried selecting messages in Outlook
> before running the Macro in Excel, but this seems to have no effect. Not
> selecting any messages beforehand also has no effect.
>
> 


0
Tim
12/15/2009 6:48:07 AM
If you have a genuine error removing "on error resume next'" will highlight 
the vicinity of the error but why not be more pro-active. There are some good 
debugging tools within the VBE environment such as step, local variables and 
breakpoints. The effort to become familiar with these will more than repay 
itself if you want to quickly debug any sort of code especially where the 
error stems from poor or illogical code structure.

-- 
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"Tim Williams" wrote:

> What happens if you remove the "On Error Resume Next" ?
> 
> Tim
> 
> 
> 
> "Monomeeth" <Monomeeth@discussions.microsoft.com> wrote in message 
> news:EA4DCCC2-5E14-4D91-8B09-6D5FD4083BD6@microsoft.com...
> > Oops, forgot to mention that I have tried selecting messages in Outlook
> > before running the Macro in Excel, but this seems to have no effect. Not
> > selecting any messages beforehand also has no effect.
> >
> > 
> 
> 
> .
> 
0
Utf
12/15/2009 12:34:01 PM
Hi Tim

Thanks for the suggestion. I removed it and there was no noticeable 
difference - exactly the same result.

I don't suppose you'd have any other ideas?

Joe.
-- 
If you can measure it, you can improve it!


"Tim Williams" wrote:

> What happens if you remove the "On Error Resume Next" ?
> 
> Tim
> 
> 
> 
> "Monomeeth" <Monomeeth@discussions.microsoft.com> wrote in message 
> news:EA4DCCC2-5E14-4D91-8B09-6D5FD4083BD6@microsoft.com...
> > Oops, forgot to mention that I have tried selecting messages in Outlook
> > before running the Macro in Excel, but this seems to have no effect. Not
> > selecting any messages beforehand also has no effect.
> >
> > 
> 
> 
> .
> 
0
Utf
12/15/2009 10:53:03 PM
Hi Ken

Thanks for the suggestion. I did have a bit of a play (I'm no expert by any 
means) with the debugging tools, but there appears to be no error! I am now 
wondering whether the "data" the macro is looking for is not actually there 
and therefore that's why nothing is getting imported.

What I mean is, if the Macro is looking for something called "last name" but 
Outlook's field is actually called something else like "Surname" then the 
macro isn't going to find it. I might go and investigate this approach and 
see what happens - This isn't my macro so I'm working a little blind here.

Thanks for your help!

Joe.
-- 
If you can measure it, you can improve it!


"K_Macd" wrote:

> If you have a genuine error removing "on error resume next'" will highlight 
> the vicinity of the error but why not be more pro-active. There are some good 
> debugging tools within the VBE environment such as step, local variables and 
> breakpoints. The effort to become familiar with these will more than repay 
> itself if you want to quickly debug any sort of code especially where the 
> error stems from poor or illogical code structure.
> 
> -- 
> Ken
> "Using Dbase dialects since 82"
> "Started with Visicalc in the same year"
> 
> 
> "Tim Williams" wrote:
> 
> > What happens if you remove the "On Error Resume Next" ?
> > 
> > Tim
> > 
> > 
> > 
> > "Monomeeth" <Monomeeth@discussions.microsoft.com> wrote in message 
> > news:EA4DCCC2-5E14-4D91-8B09-6D5FD4083BD6@microsoft.com...
> > > Oops, forgot to mention that I have tried selecting messages in Outlook
> > > before running the Macro in Excel, but this seems to have no effect. Not
> > > selecting any messages beforehand also has no effect.
> > >
> > > 
> > 
> > 
> > .
> > 
0
Utf
12/15/2009 11:00:02 PM
Your code works for me in that it will fetch the body of all the
messages selected in my outlook inbox.

So, if you're not getting any data the problem must be in your parsing
code.
Hard to tell without a sample message to look at.

Comment on this:

        Tabl =3D Split(mybody, Chr(10))
        For Each Item In Tabl
            Item =3D Replace(Item, Chr(10), "") '**** not required
            Item =3D Application.Clean(Item)
        Next Item

If you've split the text into lines on Chr(10), there should be no Chr
(10) in any of the lines, so you can omit that line.

You might also consider splitting out the "search" part into a
separate function

Function GetValue(arrLines, LookFor As String) As String
Dim x As Integer
Dim rv As String, line As String

    rv =3D ""
    For x =3D LBound(arrLines) To UBound(arrLines)
        line =3D arrLines(x)
        If LCase(line) Like LookFor & "*" Then
            rv =3D Mid(line, Len(LookFor) + 4, 999)
            Exit For
        End If
    Next x
    GetValue =3D rv

End Function

Call using something like:

      tmp =3D GetValue(Tabl,"first name")
      .Cells(2).value =3D Application.Proper(tmp)

If you could post an example (altered if required) of a typical
message body that would help.

Tim

On Dec 15, 2:53=A0pm, Monomeeth <Monome...@discussions.microsoft.com>
wrote:
> Hi Tim
>
> Thanks for the suggestion. I removed it and there was no noticeable
> difference - exactly the same result.
>
> I don't suppose you'd have any other ideas?
>
> Joe.
> --
> If you can measure it, you can improve it!
>
>
>
> "Tim Williams" wrote:
> > What happens if you remove the "On Error Resume Next" ?
>
> > Tim
>
> > "Monomeeth" <Monome...@discussions.microsoft.com> wrote in message
> >news:EA4DCCC2-5E14-4D91-8B09-6D5FD4083BD6@microsoft.com...
> > > Oops, forgot to mention that I have tried selecting messages in Outlo=
ok
> > > before running the Macro in Excel, but this seems to have no effect. =
Not
> > > selecting any messages beforehand also has no effect.
>
> > .- Hide quoted text -
>
> - Show quoted text -

0
Tim
12/16/2009 1:56:14 AM
Reply:

Similar Artilces:

Help getting SheetName into a cell
I need to refer to the sheet name in a formula and can't figure out how to do it. I can't find a Function which will do this. I did discover ActiveSheet.Name but I have been unsuccessful in getting it work in a function. Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c" and need to get these names into cell C3 of each sheet. The sheets may not be in order and there may be missing sheets. Thanks in advance for any help. Omar Hi You could put somthing like this in a macro or attatch ...

Help with SQL Query 06-30-10
We have a distinct list of email addresses in alpha order and we need to transform it from a single column into a grid of three columns maintaining the alpha order. The list is contained in a temp table inside of our query. We then use the temp table list and perform case statement with a mod on the row_number in a select statement to columnze the data. However, the columnar data contains a null value in two of the three columns and we are needing to remove the nulls and have the actual values on each row in the output. Here is our current sql: CREATE TABLE #tmpTable ( Email_Add...

Messages not delivered to another Routing Group HELP NEEDED
I have three routing groups ( exchange in the 3 of them are Exch 2k sp3 ). Mail from one server ( in my headquarter routing group ) to another ( in one branch office routing group ) is getting stucked in MESSAGES WITH UNREACHABLE DESTINATION and not routed to that server. Connectors seems to be working just fine. After add my other exchange server from my headqurter group to the connector to be albe to send mail it shows the queue now in the connector but does not sends anything. Otherwise, mail from my branch office to my headquarter correctly sends mail. Any help would be gre...

Run macro only if
Hi, i need a macro to run if A1 contains "John" and C1 contains "Mari", and if D1 is blank. If this criteria is not mached, then the macro to display a message. Can this be done? Thanks! Hi, Is this what you mean Sub somemacro() Set sht = Sheets("Sheet1") ' change to suit With sht If UCase(.Range("A1")) <> "JOHN" _ Or UCase(.Range("C1")) <> "MARI" _ Or .Range("D1") <> "" Then MsgBox "Criteria not met" Exit Sub End If End With 'Your code End Su...

Need help with Combo Box?
I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. Sure...

global macro.xls
I have an Excel file that if I open it on a machine with office 2k not 2k3 and click on cells it gives a message "cant find global macro.xls." No such file exists on my machines, but it works fine in office 2k3. Any thoughts? Interesting. Can you send it to me? You can clear out any proprietary data first it you want. -- Jim Rech Excel MVP "spammeblind" <spammeblind@discussions.microsoft.com> wrote in message news:B5F2A847-73D9-4937-A181-EA7647B667A5@microsoft.com... |I have an Excel file that if I open it on a machine with office 2k not 2k3 and click on cel...

Macro to send grab email address and open Outlook
Hi there I am trying to write a simple macro to be able to send an email based on an emai address in a form. tblAgency Details has a field EmailAddress (data type is text) I have a Command Button on a form frmAdd Agency Details to run a macro. Macro name is SendEmail - Action is only SendObject There are only two items in the Action Arguments To: =[EmailAddress] Edit Message: Yes (I only want Outlook to load with the email address) So far it does not work. When I have a field for a Web Address and the data type is a hyperlink that works well for me....

Help making BINGO cards
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am quite the novice at using Excel. I'm trying to create a set of BINGO cards for a church group and I'm having trouble figuring it out. I saw a post about something called RAND function but I don't know what that means. <br><br>What I need is 56 unique cards with 5 columns and 5 rows each. The cards will be using the numbers 1-24 leaving the center square blank or &quot;0&quot;. <br><br>Can anyone please assist? This is a multi-part message in MIME format. ----...

Calculating Averages
Hi, I am using the newest version of Excel on Windows 7, and I need some help with a complex calculation. In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells B1-B100, I have percentages ranging from 0%-100%. I want to break out the cells in A1-A100 in several groups, like follows: $1-$99, $100-$199, $200-$299, and so on up to $1000. Then, I need to calculate the average percentage for jobs in those categories. So, for the category of $1-$99, lets say there are two cells with amounts in that range, A1 and A2. Their percentages in B1 and B2 are 40% and 6...

REALLY NEED HELP
Hi guys, i'm hoping someone could lend me a hand. I'm setting up an excel file to input our fees received from our customers sent to collections. I have a main page with all the customers names, and each name is a hyper link to that customers separate worksheet - where a running tab is kept. I have a button called "update" and i've assigned the following macro (also called UPDATE) move the info over to the respective customers worksheet. I'm trying to get a loop going. A# is the customers name (first name will be starting at A4. B# to F# (first transaction...

Workbook there but not visible! Help!
I was working on some VBA code for an excel application I am writing. I switched between the VBE and the spreadsheet and all of a sudden my workbook is no longer visible! In the VBE project explorer window it shows my project and my code but in the excel window...no workbook! Nothing! Ran a test procedure calling IsAddin to see if somehow I accidentally clicked a button/box telling Excel to make this file an addin but it returned false. I did close the file and re-open it. Any ideas? Maybe it's just off the visible screen: Window|Arrange|tiled (and resize manually) or maybe y...

Add a new code macro
I have workbook that I am trying to use with a macro form another post on this form. It inserts a new row with the formulas and contents of the selected row. The problem is that it copies the row below the one selected, and because of this, I can not make a copy of row 2 below the column headers. Any ideas? Thanks. Here is the code: '/=======Start of Code==========================/ Sub InsertRowsAndFillFormulas() 'adds desired # of lines below the current line and ' copies the formulas to that/those lines 'added selection of more than one worksheet ' - Gary L. Brown &#...

How do I create a new macro in Outlook 2007?
Below is the macro, copied from MS Word, where it works perfectly! In Outlook I get all sorts of error messages and debugging, which I don't understand. Thanks for any help. Sub SafariCare() ' ' SafariCare Macro ' Macro recorded 6/4/2007 by Eva Wilson ' Selection.TypeText Text:= _ "Thank you for your participation in the SafariCare program! " Selection.TypeText Text:= _ " Attached are the program guidelines for your convenience. " Selection.TypeParagraph Selection.TypeParagraph Selection.TypeText Text:= _ ...

Please help Password Trouble
I am using outlook 2000 on a DSL line on XP Home. I have chosen to not save my password, and the problem that I am having is that everytime I go to check my mail it makes me type in my password. There must be a way (like in express) that allows you to just type in your password the first time you log into outlook and keeps you logged in until you decide to log out?? Thanks for your help!!!! -- Virtualliance, Inc. Mark Needham 7 Kimball Lane Bldg A Lynnfield, Ma 01940 T 001-781-224-4700 F 001-781-224-2414 C 001-617-799-4597 www.virtuallianceinc.com mneedham@virtuallianceinc.com im: vaincmar...

Please help....
I have a question regarding bank reconciliations that I am hoping someone can help me with. It concerns a USD bank account that I use. At the end of the month I prepare the bank reconciliation in GP. After completing the reconciliation I get a print-out called the "Reconciliation Posting Journal". This print-out provides me with the folowing: Bank Statement Balance Oustanding Cheques(-) Depoits in Transit(+) =Adjusted Bank Balance All of these amounts are in USD. Then I go to "Financial - Inquiry - Summary" and pull the summary balance for this GL ...

Help! New to Publisher
What is the A...at the bottom of a page inside a small box? I am typing a newsletter. Shouldn't one page flow to the next like in Microsoft Office? If it is text overflow, I cannot retrieve. What am I doing wrong? Please advise. That indicates that there is more text inside that area that cannot be seen. If you stretch that text box down, you'll see the rest of your text. To make it flow from one box to another, you need to set it up for that. (I will admit that I break mine manually because I've never taken the time to learn how to do it correctly.) -- The problem with ...

Help! Lost all email from inbox
Hi, hoping someone can advise me. Somehow my fingers fumbled when reaching for mouse to open an email and every email in my inbox disappeared. I have outlook 2000 and do have it set to automatically delete when i highlight and press delete key. But in this case, i didnt highlight anything and must have hit some combination of keys and poof - over 100 emails from inbox disappeared. They are not in deleted items folder and many were not even opened yet. tia kate <kate@discussions.microsoft.com> wrote: > Hi, hoping someone can advise me. Somehow my fingers fumbled when >...

Outlook 2002 backup question...please help!
Hi all, I am having a problem with windows which may require me to reformat. I cannot get into outlook (2002) via windows, but I can access files from dos. Can someone please tell me where the data files for outlook 2002 are stored, and which ones I'd need to copy? Thanks in advance for any help. Jim See if this info helps: http://www.howto-outlook.com/howto/backupandrestore.htm "Jim" <lakerfan426@yahoo.com> wrote in message news:OUhIH0oTGHA.4132@TK2MSFTNGP11.phx.gbl... > Hi all, > > I am having a problem with windows which may require me to reformat. I >...

Need Help Using A Custom Session Manager
Hi. I have some questions about session management. I have decided to use a custom session manager class to have more control over session state in my web site. I have started out by using Stephan Prodan's Session Class here: http://stefanprodan.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3DC%2523 You'll need to take a look (which I appreciate immensely) to get a sense of what I am talking about. 1) My first question pertains to how I persist and access my session information after a user authenticates (or doesn't). In his exam...

Help: MAPI can't find PSTPRX.DLL
Hi there Can anyone suggest how I can make outlook 2002 work properly. Everytime I press send/receive it says MAPI can not locate PSTPRX.DLL. I have done search and it is not on my computer. Please can anyone assist? just trying out 1st time >-----Original Message----- >Hi there > >Can anyone suggest how I can make outlook 2002 work >properly. Everytime I press send/receive it says MAPI can >not locate PSTPRX.DLL. I have done search and it is not >on my computer. Please can anyone assist? >. > See if this info helps: http://support.microsoft.com/default.asp...

Selecting Charts in a Macro
I have a worksheet which contains 7 charts: 1st Chart = Chart 12 2nd Chart = Chart 7 3rd Chart = Chart 11 4th Chart = Chart 13 5th Chart = Chart 24 6th Chart = Chart 16 7th Chart = Chart 26 I have a macro which copies the worksheet it over to a new worksheet within the same workbook, the charts are then labelled: 1st Chart = Chart 9 2nd Chart = Chart 7 3rd Chart = Chart 13 4th Chart = Chart 10 5th Chart = Chart 2 6th Chart = Chart 12 7th Chart = Chart 15 The macro then tries to select each chart and change the source data, what I don't know is how excel will treat the chart numbers for...

Macro to copy cell down
I want to create a macro that will copy the cell above to the active cell. OR the built-in key that will do that tenbob@optonline.net Bob A few seconds with the macro recorder set to "Relative Reference" gave me this. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 11/3/2003 by Gord Dibben ' ' Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub Gord Dibben XL2002 On Tue, 04 Nov 2003 04:26:28 GMT, Bob K. <tenbob@optonline.net> wrote: >I want to create a mac...

Please explain the steps to use the Dlookup function in Access?
explain me the steps to use the dlookup function like how we use in EXCEL See: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Balu" <Balu@discussions.microsoft.com> wrote in message news:00601393-C86C-47BD-9103-52D0FAA292C9@microsoft.com... > explain me the steps to use the dlookup function like how we use in EXCEL ...

Help with formatting text in a textbox
Hi All, I have been trying everything I can think of, and still not getting the results I'm after. I am trying to summarize an order in a textbox, by building a string and assigning it to the caption property of the text box. I create a recordset based on what's in the table, then loop through it and build my string(s). I want to display everything nicely, so I am trying to space each field so that I can put in a header row of labels, and then have each record show nicely spaced below. I am now finding that even when I take into account the length of the data inthe field, I still ca...

search help
I have a form which contain 2 unbound text boxes and a subform. 1st text box is where I input client's ID and 2nd textbox is for client's name. I have set the child and master field for subform to clients ID on subform and 1st testbox on main form. This works fine if I only have to search by client's ID. My problem is how do I make the subform to requery if ,say the client forget his ID # or search using ID produce no result, and I have to search by name using 2nd textbox as a search string. I tried changing the master and child field link using code but that didn't work...li...