Changing VBA code from Outlook to Lotus Notes

Good morning tommytee

You're better off starting from scratch to be honest. This code should get you started to send a straight forward e-mail - just amend it to suit. I don't have access to Notes 7, but have tested it on Notes 6.5. I would be interested in confirmation that it works OK on the latest version.

Please Login or Register  to view this content.

HTH

DominicB

Hi tommytee

Firstly, will you check that the code above works - do this by finding the cells where comments are added and add a body, a header, and an addressee and just run the code to make sure that this code works OK on Notes 7.

Then let me know where the body of the e-mail will be, and where the header will be, perhaps providing a cut down version of what you have at the moment. Also, I'm guessing that the "contact" in column A is to be the salutation (eg Dear tommytee etc)?

HTH

DominicB

You will probably find it easier to just put a x in column C & use the code to check for that value instead of checkboxes. Alternatively, you can format C to Marlett Font & use "a" to get a check mark.

Originally Posted by tommytee

Thanks Roy,

My intention was to link the check box to each cell to return a TRUE/FALSE and reference that in the code, but I'm not sure if thats the best way.

Tom

I think you will find it simpler to do what I suggested. Check the example, double click any cell in column C to add the check (tick), remove it if not required. Code is in the Worksheet module.

Hi tommytee

Roy's suggestion would be easier than to create 500 tick boxes : that said the example I have used utilises a tick box, as I have only one row to cater for. If you want to go for Roy's example, no great changes will be required.

To kick the thing off in your example you will need to run the macro "BuildMessage" which will loop through all rows and send an e-mail based on the information in the sheet where column N says True.

I am looking into saving the resultiung e-mail into drafts, but this is not quite as simple as it would be in Outlook. Unsurprisingly, Notes doesn't integrate into Office quite as well as Microsoft products do - in fact it is the command

Please Login or Register  to view this content.

which places the mail into the sent folder, so it's not quite as straightforward as one might think.

HTH

DominicB

Hi Dominic, does Notes not have a .Show command or similar?

Originally Posted by royUK

Hi Dominic, does Notes not have a .Show command or similar?

Thanks for the thought Roy, but unfortunately, but the .Show command (and is there also a .Display command?) are unsupported, however I've found a .Save instruction that seems to work with a slight syntax tweak, so TommyTee in the macro replace this line :

Please Login or Register  to view this content.

with this :

Please Login or Register  to view this content.

to get the unsent mail to go to your Drafts folder for review.

HTH

DominicB

Hi tommytee

Originally Posted by tommytee

I was just trying to use Roy's code for double clicking but can't seem to get it to work in the test sheet. I wanted to change it to column A aswell but couldn't see how to do it.

Not quite sure what you mean, but if you're really struggling feel free to upload a small sample of where you're up to and someone will try and help you out.

DominicB

Hi tommytee

The modules I supplied are standard modules - they don't run until you ask them to. The module that Roy supplied is an "event procedure" - it runs whenever a predefined event takes place, in this case before a double click happens (look at the title of the macro ...) As such it needs to be placed in a special module, behind sheet 1. Have another look at the book Roy uploaded and you'll see what I mean.

Please Login or Register  to view this content.

The line preceded with a ' is indeed a comment, however the next line does the gritty work : If Target (the cell you're in) .Column (grab the column number) <> (doesn't equal) 3 (column C) Then Exit Sub(bail out). If you want this to be column A change the 3 to a 1.

Once you've put these change into place have a look at my code, as this checks to see if column 14 equals True, so this line would need changing :

Please Login or Register  to view this content.

to :

Please Login or Register  to view this content.

This is if you're using column A (we like to call it A but remember that Excel likes to refer to it as a number)

Hopefully with those changes in place it should start to come together.
As always, shout out if you come unstuck, but try and get the ticks working first, then concentrate on the other bits.

HTH

DominicB

Hi tommytee

Originally Posted by tommytee

Whats standard etiquette if I need further assistance - post on this thread again or open a new one? Sorry, bit of a newbie to all this.

Your original problem is solved now, so feel free to start another thread.
If you think that the contents of this thread are relevant in your next posting, then feel free to post a link.

Glad you're sorted.

DominicB