Solved: If condition false reply by e-mail with variable hyperlink

[Dipwind]

Hello,

In my spreadsheet, I want that, as soon in one specific cell the value turns false, an automatic e-mail should be sent to a specific e-mail address, the address is in other cell, one of the problems is there are 6 users for the same email, and I want to put in the subject line her or his Name, the name is in other cell all in the same sheet looked up from other sheet.

The message corp on the e-mail should be the same, but with a different hyperlink with the issue N?. all that information are in the same sheet. and finally after the e-mail was sent another cell should be ticked telling that the email was sent. probably after put the mechanical thing working it is easy to deal with the last one.

Hope this make sense, I always have difficulties to post in this forums " specially the titles ", my native language is not English, but any way, any help is welcome and very much appreciated.

Jo

[mdmackillop]

Hi Jo?o
Welcome to VBAX
If you can post a small sample of your workbook with dummy names, cell to be ticked etc., we can give you a better solution.
Use Manage Attachments in the Go Advanced section.
Regards
MD

[Dipwind]

thanks mdmackillop,

sorry the delay in my answer, I've attached a file with what I want.

Hope understandable.

Jo

[mdmackillop]

Not worked out the hyperlink part yet, but is this generally what you're after?

[Dipwind]

mdmackillop, I'm Impressed, exactly what I want.

For the hyperlink I decided to use a excel file and not a word file, is easy for me and I can write a macro with a form template for as soon as any cell on Column C is ticked the Form is automatically created. then the hyperlink take the user until there.

my many thanks mdmackillop
joao

PS: This forum is excellent

[mdmackillop]

Glad to help. If this is solved, you can mark it so using the Thread Tools dropdown.

[Dipwind]

No it is not solved yet, I still need to find a solution for the hyperlink, on the body message.
Each Issue solved when closed the e-mail should have the hyperlink there.
and I don't know how to do it

any way my big thanks for the help given

Jo

[mdmackillop]

Simpler than I thought (when I eventually found it!)

[Dipwind]

I've got an error Variable not defined

[mdmackillop]

Apologies Jo?o, There was a typo in the code.

[Dipwind]

mdmackillop, many thanks again for all your help.

the sample that I attached is not the final one I make a short example, now with your solution "that works in a perfection" I've found some difficulties when copy the code to change it and make it work in my spreadsheet.

My main sheet is exactly the same as the sample.

But the code get the names and emails from "data" there is 3 columns there, abbreviation; name and e-mail, on my original I have also 3 columns, in different order, Name; abbreviation and email, and the sheet is located to the left of the Sheet1, and the first column has merged cells 3-1, I don't know if this make difference.

The hyperlink is there, but I don't know how to specified the way.

My apologies for that, is not nice after all work done by you, put that questions. my mistake.

but can you help me please?

Jo

[mdmackillop]

Can you post another sample with your actual layout. It should be easy enough to adjust.

[Dipwind]

Thank you mdmackillop,

thats the file I'm working

once again thanks
Jo

[mdmackillop]

as soon the G column in "DI" is filled the Issue number is open, and that's the hyperlink that I want on the e-mail, when clicked opend the document later I will figure out the best way to create the document in a automatic way, running after opened, a macro with a form template.

Sorry, I'm not following this explanation.
What is the hyperlink?
What cell completion triggers the hyperlink?
What cell contains the reference to whom it is addressed?

[Dipwind]

The idea is: the issue as soon opened triggered by "DI" Column G, generates a issue N.?, on "SPMF" column G. then a xls file is created (x) on a specific location.

when on "DI" the K column, is hit, closes the Issue and the e-mail is ready to send with all information you already provided, plus the hyperlink with the Issue N.?.

Ideally the hyperlink text on the e-mail should only say the exact information on "SPMF" column G. the user as soon received the e-mail click on the hyperlink and the file with all information regarding the Issue n.? opens.

The cell to who should be addressed is the same on your first solution. in my layout is on "EDB" Column A and respective e-mail address..

(x)-- That bit is not ready yet, at the moment I opened the file manualy and I create the Hyperlink also manualy. my intention is automate that process, but this is matter for other post, is not fair and its not the idea to use the same post to solve everything.

hope my answers help to put what I want a bit more clear.

thanks again mdmackillop

Jo

[mdmackillop]

I've updated the code to create a workbook when DI col G is completed (writes it to G:\NewBook-?.xls). The other code is basically as before, suited to your layout. I'm trying to find code to write DisplayText instead of the bare hyperlink, but no luck as yet.

[Dipwind]

mdmackillop, works excellent, I'm realy happy with your help, fantastic.

I still have a problem I've tried to sort it, but without sucess, the code open the New book "I've changed the code to Issue, but the code gives me the row number and not the content on cell A. that means The Issue number is equal to the information on cell A and not the row number.

any way my big thanks for all your help and commitement in helping me.

Jo

[mdmackillop]

As the Issue numbers are related to the row numbers, you can modify this by subtracting the appropriate value e.g.
[VBA]WB.SaveAs "G:\NewBook-" & Target.Row-7 & ".xls"
[/VBA]

[Dipwind]

mdmackillop, thanks again.

But now something strange are happening. on my computer at home your solution works perfect, but in my computer at work don't.

I have a function written on module 1, (fixeddate ), that was stopped doing the job, and I don't know why. every time I hit cells on column G or K on "DI", the date shows up on columns H or J, but now I've got "#VALUE!".

and after hit G or K, the VBA take me to module 1 showing a compiled error, can't find project or Library.

But it works on my computer at home.

Where I should look to fixed that?

once again thanks
Joao

[mdmackillop]

My fault!
My code ran into prooblems with the merged cells on sheet EDB, so I unmerged them and deleted the surplus columns in my example.
The general rule is "avoid merged cells at all costs".