Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to Extract Data & put into Outlook

    I have email addresses in Col N

    I would like VBA code that If I double click on an email address in Col N, the following information will be extracted to outlook:

    1) The value (unhidden column only) that is in line with the email address for eg in this case February 2012 = Col F , therefore value to be extracted is 10938
    2) The Branch nane that is 5 rows above the email address and in Col A-This must appear three spaces next to the value

    I have attached sample data

    Your assistance regarding the above will be most appreciated
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    I was hoping you would have received an answer by now!

    Here's my solution:
    See attached file.

    I just added my code to the file you submitted.
    (you have a few 'empty' VBA modules in your file)

    To simplify the process, it would be better to double-click the Commission amount cell, rather than the email cell.
    The reason is that we always know that the email address should be in column [N].
    It is more tricky to rely on there being only one visible month column.
    (e.g. what happens if there are two columns showing? none showing? all showing?)

    I have tested this with Outlook2010.
    Currently, although you can have multiple versions of Office on the same PC (I have Excel2003, 2007 and 2010 on one of my laptops), you can ONLY have ONE version of Outlook.
    The VBA code requires that you must include a Reference to your Outlook Object Library.
    See my reminder in the VBA code in the attached file.

    I put my code in module named [modOutlook]
    The code for the "double-click" event is on the sheet [Parts]
    (right-click on sheet tab [Parts] and click View Code)

    So, when you double-click on a commission amount in one of your month columns, an email is created with the email address specified in column [N], for the Branch that is 5 rows up in column [A].
    The month details and Branch name are put in the header of the email.
    The body text of the email has the commission amount.
    The email is created for you, waiting for you to hit the Send button.
    You could adapt the code to do this if you really wanted to, but I prefer not to send automatically.

    zeddy
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for all your effort & assistance. I am using Office 2007 at home & Office 2010 at work.

    When I double click on commision, I get an error "compile error: can't find project or library" and this is in line with zrow. When I click on ok the following is highlighted

    "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)"

    It would be appreciatedif you would test & amend

    Regards

    Howard
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    In my earlier post:
    The VBA code requires that you must include a Reference to your Outlook Object Library.
    See my reminder in the VBA code in the attached file.
    I put my code in module named [modOutlook]

    So, your "compile error: can't find project or library" is telling you that it can't find the Outlook Object library.
    This is because YOU haven't set a Reference to it yet in the file I attached. It will 'find' the library once you have checked the box for it on your PC. I will tell you how to do this shortly.

    When a file needs an object library, e.g. to manipulate Outlook, you must have this set on the particular PC you are running the code on. The library cannot be 'included' with the excel file itself!

    So, to 'fix' this:
    Open the file I sent previously.
    Go to the VBA editor (press Alt-F11)
    Open the module [modOutlook].
    Read my note at the top of this module.
    Look in the top panel toolbar of the VBA editor.
    Select Tools
    Select References
    In the list of Available References, scroll down the alphabetic list until you find Microsoft Outlook xx.x Library.
    Check the box.
    Save the file.
    Close the VBA editor window.
    Go to sheet [Parts]
    Double-click on a commission amount.

    Note: If Outlook is NOT already open in the background, it will take a few seconds to load and then you will see the email that is created.
    If Outlook is already open in the background, the creation of the email will be 'faster'.

    Please let us know how you get on.

    zeddy

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the time taken to explain how sort out the problem

    When I now run the macro , I get rum time error '13' type mismatch & the following code is highlighted "zMonth = CDate(temp)"

    It would be appreciated if you would test & advise

    Regards

    Howard

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    Check what you have in row 1 for the month date i.e. in cell [F1]
    In the file I attached, it was a formula =+E1+31 displaying a result of February-2012.

    The code line..
    temp = Cells(1, zCol) '> date is in row 1 (or row 5??)
    ..should by default retrieve the 'value' of this cell.
    The next code line
    zMonth = CDate(temp)
    ..uses the 'Convert to Date' function to ensure the 'temp' value is treated (where possible) as a 'date'.
    I included this line because date formats are dependant on where in the world you are etc.
    But if the cell contains text only (e.g. the word Fred for example) this will trigger the type mismatch error.
    So check your cell entry!
    There are lots of tests that could be done in the code (I put suggestions like 'test for missing month here if required' etc) but the object here is to show you the way rather than do it all.
    Have another look, read the code carefully, try again, and let us know.

    I tested the file before I attached it. Have you changed anything in row1?????

    zeddy

    zeddy
    zMonth = CDate(temp)

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply. I have a date in F1 (format "dd/mm/yyyy") I still get type mismatch

    Please check sample file attached as I can't determine what the problem is

    Thanks

    Howard
    Attached Files Attached Files

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    Are you using iTunes on your PC???
    Just asking.
    On my system, everything worked in Excel 2003 annd Excel 2010.
    I loaded your attached file in Excel 2007 and replicated your problem.
    It was a missing library problem again for me.
    When you get any missing library problem, VBA code can stop on ANY code line, and the issue is NOT with the particular line of code.
    When I went to the VBA editor, and checked Tools>References my system tells me it can't find
    OutlookChangeNotifierAddIn 1.0 Type Library.
    It was 'looking' for this in a folder
    C:\ProgramFiles\Common Files\Apple\Mobile Device Support...

    Anyway, I just 'unchecked' this 'missing reference', and then everything worked as expected in Excel 2007.
    Can you try to do the same at your end?

    zeddy




    I went to th

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply

    I went to VBA editor, and checked Tools>References and tried to find OutlookChangeNotifierAddIn 1.0 Type Library in Outlook2007, but cannot locate this to uncheck.

    It would be appreciated if you could guide me

    Regards

    Howard

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    I have checked my original file with Excel2007, and everything worked as expected.
    When I load your latest attached file into Excel2007, I get the problem you reported.
    The VBA debugs to the zRow line as you reported.
    I look in the VBA editor top-panel toolbar and select Tools>References.
    In my list of Available References, I have six checkboxes ticked.
    The last checked box in my list says:
    MISSING: OutlookChangeNotifierAddin 1.0 Type Library
    So I uncheck this box.
    Then everything works as expected.

    zeddy

  11. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply. I cannot find "MISSING: OutlookChangeNotifierAddin 1.0 Type Library" when I select tools>references under VB Editor

    I think that the only way to resolve this is via remote access using a one time user name & password



    Regards

    Howard

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    I am unable to help you with a remote access.

    Perhaps another person with Outlook can go through these posts and try the sample files on their systems.
    My original attachment works OK on my system under Excel2003, Excel2007 and Excel2010 with Outlook 2010 as the mail system.

    The only other option is try it on your Excel2010 system at work.

    It seems that something is interfering with this, possibly an add-in.

    I will see if I can test it with other Outlook versions on some of my other PCs.

    zeddy

  13. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply and your effort in trying to resolve the issue

    I have attached a screen print of my VBA editor showing you which items are ticked. I will test the workbook on my PC at the office this morning & let you know. It is is working on your PC, then obviously something is causing a conflict, but to find wht is causing the problem is the difficult part

    Regards

    Howard
    Attached Files Attached Files
    Last edited by HowardC; 2012-03-12 at 01:58.

  14. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Have tested the workbook on my office PC, where I have Office 2010 installed. When double clicking on commision, I get run time error 13 "type mismatch" and the following code is highlighted

    zMonth = CDate(temp)

    Regards

    Howard

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    From your screenprint I see that you now have the mysterious "OutlookChangeNotifierAddin 1.0 Type Library" checked as an Available Reference (in the ..\Apple\ folder I mentioned previously).
    Can you Uncheck this Reference, and then try again please.

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •