Looking Up Names In A Global Address Book

  • I'm writing an excel macro to send form emails by pasting in text from cells in a particular row of my workbook. First of all, I would appreciate if anyone can direct me to an example of this that I can modify.


    Secondly, the only difficulty I foresee is that I want people to be able to just type the person's name in and have it be directed to their email address in our firm's global address book (in Outlook). Any ideas on how I would go about doing this?


    Thanks,


    Zach

  • Re: Looking Up Names In A Global Address Book


    I supposed that the body of the mail is in cells A1 and B1, and the name of the recipient in A2. I can't test with the global address book because i have no Exchange Server. One drawback is that I loop sequentially through the names of the address book until if found a match. Depending the size of your firm, it may be quite lenghty. Here is the code :


    HTH
    Daniel[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]i just forgot this : you must select "MS Outlook xx Object Library" in the VBA references (Tools, References).
    Daniel

  • Re: Looking Up Names In A Global Address Book


    Thanks, that was exactly what I was looking for. I thought the sequential search would be too slow for ~1,500 entries, but it works fine.


    Now, I will ask a couple follow up questions that will reveal my pitiful knowledge of VB.


    First, how would I get the references to specific cells to instead refer to cells in the active row?


    Second, how would I make the sender of the email an address different from my own (a distribution group of which I am a member)?


    Thanks again,


    Zach

  • Re: Looking Up Names In A Global Address Book


    Quote


    Now, I will ask a couple follow up questions that will reveal my pitiful knowledge of VB.


    First, how would I get the references to specific cells to instead refer to cells in the active row?


    Can't be worse than my pitiful kowledge of english. I can't figure out what you mean; please, may I get an example ?
    As regards the second point, can you do it manually ? and where is defined this distribution group ? Is it a special account ? in my opinion, it is a list of recipients, but I may be wrong. I know how to use another account than the default account.
    Daniel

  • Re: Looking Up Names In A Global Address Book


    For the first question, I just want to create a variable for the active row so instead of sending the email to A2 with text from A1 and B2 (as in your example) it would send text from columns 2 and 3 to the recipient in column 1 of whichever row is highlighted.


    As far as the second question, yes I am just looking to use another account than the default one.


    Thanks,


    Zach

  • Re: Looking Up Names In A Global Address Book


    As for the first point, see following code. For the second point, it will be a little longer than expected : the workbook which serves me as repository for my Outlook code is now stripped of all macros. I need to recreate it from scratch. Of course, you are the priority.


    Daniel[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I'm sorry to disappoint you. The way I do it, using the "SendUsingAccount" property is new in Office 2007.
    It looks like there is no other way to make it, excepted, some add-ins (like redemption)
    Daniel

  • Re: Looking Up Names In A Global Address Book


    Thanks again! Too bad about the SendUsingAccount, although I think my office will be upgrading to '07 soon.


    You example wasn't exactly what I was looking for. Instead of using an InputBox, I just wanted it to use whatever row contained an active cell. That I don't know how to refer to this shows my inexperience with the Excel object model. I don't think Cells(rw.Active, 1) is correct.


    Also, how would I refer to the title of a worksheet? Thanks.

  • Re: Looking Up Names In A Global Address Book


    You are not that far ! Change for :


    What do you mean with sheet title ? If it's the tag name, it's :

    Code
    1. Sheets(1).Name 'for the first sheet or
    2. ActiveSheet.Name 'for the active sheet


    Daniel

  • Re: Looking Up Names In A Global Address Book


    One last thing (I promise!): now that I'm using the same macro for multiple worksheets, I realized that I must specify not just the active cell but active worksheet as well. However, ActiveSheet.ActiveCell.Row isn't right.

  • Re: Looking Up Names In A Global Address Book


    Either the macro uses the active worksheet and you do not need to change anything (by default, the active sheet is assumed or you select the sheet manually before using before starting the macro. If you want to access successively all the sheets, you'll have to use a loop. Let me know if you want to loop through the sheets (this implies that you select the active cells of each sheet before starting the macro).

  • Re: Looking Up Names In A Global Address Book


    I don't want to loop through all the sheets. The problem is that although ActiveSheet.Name does refer to the active sheet (whatever tab is highlighted), all other references refer to whatever worksheet the macro applies to (i.e. the "Name" field in the Properties window), whether or not it is highlighted. I just want to be able to use the one macro for all the sheets.

  • Re: Looking Up Names In A Global Address Book


    I think I have not been clear enough. We use the activecell to determine the row. The active cell is the cell that is selected on the active sheet (the one that is visible on the screen, the one whose tab is highlighted). I can't figure out if you want to select a sheet manually and then execute the macro, or let the macro select the sheets and execute the code automatically. In the first case, you can use the macro "as is" after selecting whatever sheet you want.
    Or do you want to execute the following pseudo code :
    1. Select sheet 1
    2. Check sheet 1 name
    3. Execute or not code according to previous step
    4. Select next sheet and go back to step 2

  • Re: Looking Up Names In A Global Address Book


    With what I have, the active cell does not refer to the cell that is selected on the active sheet, but rather, it goes to the same cell in the sheet that the macro applies to. Right now it is called "SendEmail(Sheet1)." If I select B1 in Sheet 2, it will use B1 in Sheet1, regardless. However, when I use ActiveSheet.Name it will return the name of Sheet 2. I could just copy the macro so there is a different one for every sheet, but considering that the code is the same, I would rather not to make it simpler for the users.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Ok, I solved my problem. The notation I was looking for was:


    Worksheets.(ActiveSheet.Name).Cells(ActiveCell.Row, 1)


    Without this it automatically refers to the cell in the worksheet that the macro is attached to. Thanks for all your help Daniel!