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

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.

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

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

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.

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

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

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?

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

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'?

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.

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

For example Q2 could be [email protected], Q3 could be [email protected]
When U2 is changed from blank or no to Yes then an e-mail window opens with [email protected] added. If U3 is then changed to Yes a separate e-mail window opens with [email protected] 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.

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

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!

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

Please Login or Register  to view this content.

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

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.

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

Many thanks

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

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?

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).

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

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.

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.

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

Please Login or Register  to view this content.

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?

Do I perhaps require another

Please Login or Register  to view this content.

later on in the code? I'm not sure.

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

try putting:

Please Login or Register  to view this content.

above

Please Login or Register  to view this content.

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

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.