+ Reply to Thread
Results 1 to 33 of 33

Outlook meeting invite using cell values

  1. #1
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Outlook meeting invite using cell values

    Column U is a Yes/No column using Data Validation.
    If No is selected, do nothing. If Yes is selected, can vba send off a calendar invite using info in other columns?
    This info would be:
    Column Q = e-mail address to use
    Column J = date to use
    Column K = time to use

    The e-mail address, date, time etc will be different depending on the row.

    Many thanks in advance and please ask if any further info is required.

    Note: Also referenced here on other forum

  2. #2
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    Try this macro
    you must have tools, references, microsoft outlook object library (whatever number) selected for this to run.
    you might want to change .send to .display at first when you test it so you don't spam yours contacts
    you should also change or delete the location and subject to what you need

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Thanks. I ticked the Microsoft Outlook Object Library.
    Do I just paste this code into a standard module, or in the code area for the individual sheet?

    I am getting a 'Type mismatch' error on:
    Please Login or Register  to view this content.
    I can upload a demo of the workbook if it might help or may just be an easy fix??

    Thanks
    Last edited by JRC1; 03-15-2017 at 09:18 AM.

  4. #4
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    Are your date and time inputs in date and time numeric format? That might be the problem, if not.

  5. #5
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Date is written like 15/03/2017 and the cell is defined as "Date"
    Time is written like 12.30pm and the cell is defined as "General"

    I will try changing the latter to "Time" and re-running the code. Will post my results here - thanks

  6. #6
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Unfortunately changing the cell type to time didn't seem to solve the issue.

    I've attached a sample workbook just with the Sheet in question included.
    Could you please have a look for me and see if you can get it working your end?

    Many thanks in advance.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    Hi
    it worked for me once outlook was checked in references
    try deleting it from the code for the individual sheet and running it instead from a module

  8. #8
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Great, managed to get it to work... kind of. Thanks for all your help so far, I have some questions:
    1) Only seems to be running if I change column U to "Yes" and then click run in the module window - doesn't seem to automatically run when Yes is selected?
    2) How have you set the time column up? As despite setting the cell to "Time" and trying various time formats, the appointment is coming through at 00:00, i.e. midnight, rather than the time in the cell.
    3) Will a user have to tick the excel box within references when they open this file, or is it specific to the saved file?
    4) E-mail is going to my deleted items - any way around this?

    Thank you

  9. #9
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    ok

    I had assumed you wanted to run the macro from the raw data sheet. To run it from lookup, copy this code into the code area for the lookup sheet (not the module).

    Please Login or Register  to view this content.
    The other macro will need a bit of modification if you want it to run when you click "yes" in the lookup sheet. For simplicity I have set the macro to select the raw data sheet to run the macro, as the addresses it refers to are all in the active sheet. this could be changed if necessary.


    Please Login or Register  to view this content.
    2) the time problem was an error - the macro was reading the wrong column. This should be fixed.
    3) The user will not have to tick references again in this file once they are checked and the file is save, but they will if you copy the code to a different file.
    4) I'm not sure why its going to deleted items - it doesn't when I run it. Could it be because you are not using a real email address?

  10. #10
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Thanks Nicky for your work on this.

    1) When running, the new code appears to bring up the outlook meeting request window without automatically sending it off? On second thoughts though, this is probably better, as it allows the sender to adjust the end time of the meeting, which thinking about it wasn't possible before and it was just assuming 1hr was required... It also allows the body to be written which again would prove beneficial. So I think this can be left as is...?

    2) The code only seems to run when I physically switch from my Lookup tab to Raw Data (providing column U in Raw Data is set to Yes). Can it be amended to run just when Cell U in Raw Data is either changed from Blank to Yes or from No to Yes? As ideally the Lookup tab will be hidden to the user, and I wouldn't want them to have to switch between tabs in order to get this macro to run. Also important that only the above happens, i.e. blank to yes or no to yes. I wouldn't want the file to keep pinging out outlook windows for stuff that is already set to yes each time the file is opened.

    Apologies, if I've misunderstood your post above or if I haven't made it fully clear what I'm after - I think we are nearly there!

    Cheers

  11. #11
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    I changed the final action from '.send' and '.save' to '.display' in order to review the output and not sent undeliverable messages. You could replace '.display' with these if you like, but allowing the sender to review seems sensible.

    can I just check to make sure I understand you

    the user will be in sheet Raw Data and will change the contents of a cell in column U to 'yes'. At that point, you want the macro to run.

    Do you want it to run on all cells in column U with the value 'yes', or only the row where the user changed the value to 'yes'?

  12. #12
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    assuming it is only on the one row that is changed, this code should work. It sits in the code page for sheet Raw Data, not the workbook module

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    I'd like it to run each time the user changes a row in Column U to Yes.

    For example Q2 could be example1@hotmail.com, Q3 could be example2@hotmail.com
    When U2 is changed from blank or no to Yes then an e-mail window opens with example1@hotmail.com added. If U3 is then changed to Yes a separate e-mail window opens with example2@hotmail.com added... so answer to your question would be to run only when the specific row is changed... hope that makes sense.


    I am facing a problem entering the above code in Raw Data sheet as I already have a Worksheet_Change in there and they are conflicting... is there any way you can join them to prevent this?

    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    it doesn't look like there are any conflicts, so you could probably just copy the code from one macro and paste it into the other, and both sets will run on a change event.

    or, probably a cleaner method is to change my code from a change to a selectionchange event. If your default is that pressing enter activates the cell below the one you changed, this should be triggered whenever someone types "yes" in column U

  15. #15
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    So combined like this is OK?

    Please Login or Register  to view this content.
    I'm new to VBA and am unsure how to combine stuff, seems to work ok combined like the above but I'm sure it isn't done right? I do also have a selectionchange event:

    Please Login or Register  to view this content.
    Would it be better to combine with this one?

    Thanks for your help!

  16. #16
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    I'm getting a type mismatch error on this part?:

    Please Login or Register  to view this content.

  17. #17
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    The method you have used looks fine
    If I were you though I'd combine my macro and your selection_change macro
    the selection_change you currently have is simple so if you start to get errors (and they often come up unexpectedly with worksheet code) then they will be easier to identify. There's quite a bit going on in the worksheet_change macro

  18. #18
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    The issue I'm finding with the selection change one though is that where U = Yes, each time the cell is even clicked on or scrolled over it pings up the e-mail window. If a user accidentally clicks in this cell it is going to bring up the window which seems excessive... this wasn't happening with worksheet_change.

    Regardless of which one I use, I appear to be getting a "Type Mismatch" error on:
    Please Login or Register  to view this content.
    Any ideas? I can attach the workbook if it would help, thank you.

  19. #19
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Here is an example workbook attached, hopefully it will help highlight/explain the error.

    Many thanks
    Attached Files Attached Files

  20. #20
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    Hi
    there is no email address in the Q column. When I added one, the macro worked for me.

  21. #21
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    If an e-mail address is added, but is then deleted, or even if any info from that row is deleted, do you not get the error I have mentioned?

    It seems it will error out if any information is removed or isn't present after the macro has run?

  22. #22
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    It works fine for me as long as there is a "yes" in column U, a recipient address in column Q, and numeric time value in column K (some of the values in there are not being recognised as times).

  23. #23
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Yes, same. But my question is that if there are no values, or some are missing, or some are removed (say for example in Row 2 a contact name is deleted after some time), the type mismatch error seems to appear. Is there anyway of error proofing this so it doesn't appear? As this will be used by many users and I wouldn't want the code to keep breaking if some info is missing/removed.

    I just wondered if that section of the code could be modified so that the cells aren't mandatory to have values?

    Thanks

  24. #24
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    It can perhaps be made a bit more stable but you would get odd results. For example, it could show the dialog box without an addressee, but the user wouldn't be able to send it. I'll have a look at it tomorrow, if you don't mind - I have a late night at work this evening.

  25. #25
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    It may be that we are experiencing a slightly different issue but for me if I delete a group of info from various columns across a few rows of data I keep getting type mismatch error and when clicking Debug it keeps coming back to the following:

    Please Login or Register  to view this content.
    Columns U, Q, J, K won't always have info in them, depending on the stage of the request, some or all could be blank. I don't want the code to error if these are blank or if they are altered to be blank by the user.

    If you could, that would be much appreciated & thanks for all of the help so far.

  26. #26
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    ok, try this variation in the sheet code - it should be more stable

    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Many thanks. E-mail code works perfectly, but I do still appear to be getting:

    Run-time error '13':
    Type mismatch
    ---> If Not isect Is Nothing And LCase(Target.Value) = "yes" Then

    I can't understand why? For example, say I delete some dummy data in range N3:P6, the error appears.
    Does it appear for you?

  28. #28
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Do I perhaps require another
    Please Login or Register  to view this content.
    later on in the code? I'm not sure.

  29. #29
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    After some further testing, I also get the error when deleting a row.

  30. #30
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    try putting:
    Please Login or Register  to view this content.
    above
    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    This appears to have solved the issue - many thanks! please check your pm's.

  32. #32
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Outlook meeting invite using cell values

    Hi NickyC/anyone new reading the thread

    What additions/amendments would be required to the vba in order to amend this to have the e-mail send from a central mailbox, rather than the mailbox of the user who is in the file?

    I appreciate this may not even be possible, and it isn't essential, but some feedback is that it may be better to fill up a central calendar rather than individual calendars of users sending out the e-mail.

    Here's the latest vba I'm using for this, for reference:

    Please Login or Register  to view this content.
    Last edited by JRC1; 04-05-2017 at 06:11 AM.

  33. #33
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,684

    Re: Outlook meeting invite using cell values

    Hi
    try adding under With OutAppointment

    Please Login or Register  to view this content.
    note this is case sensitive and will cause an error if the user does not have permission to send mail from that account.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Send outlook invite through excel macro
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-14-2016, 01:47 PM
  2. Accepting a meeting invite without responding
    By scott.xlsx in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2015, 05:31 PM
  3. [SOLVED] Sending invite through Outlook 2007 via excel macro or VBA
    By kartikeyadawar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-10-2015, 06:03 AM
  4. How To Take Attendance For A Meeting Using Outlook
    By Charmymay in forum Office 365
    Replies: 1
    Last Post: 12-29-2014, 07:57 PM
  5. Meeting invite via Excel
    By JimmyQ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-05-2014, 08:20 AM
  6. [SOLVED] Create meeting in outlook from Excel vba
    By eran.chason@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-24-2005, 06:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1