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.
WB.SaveAs "G:\NewBook-" & Target.Row-7 & ".xls"

[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".

[mvidas]

"avoid merged cells at all costs" - great rule of thumb.

Anyways.. Hi Malcolm, Hi Joao. Malcolm asked me about the display of text in the email (instead of the hyperlink itself). Assuming you don't mind sending HTML formatted emails (the only way to accomplish this).. in your Email sub make the following change:' HLink = "file://" & Path & "NewBook-" & Target.Row & ".xls"
HLink = "<a href=""file://" & Path & "NewBook-" & Target.Row & ".xls" & _
""">Display Text</a>"Changing your 'display text' as needed.

Then in the With objMail block, change the .body line:' .Body = Target.Offset(, -5) & vbCr & "This issue has been closed by " & _
d.Offset(, -1) & vbCr & vbCr & HLink
'changed .body to .htmlbody and replaced vbcr with <br>
.HTMLBody = Target.Offset(, -5) & "<br>This issue has been closed by " & _
d.Offset(, -1) & "<br><br>" & HLink
Should take care of it!
Matt

[Dipwind]

Oppssss, I didn't saw that, probably because the enthusiasm of seeing the code working :)

many thanks mdmackillop
joao

[mdmackillop]

Thanks Matt. I'll have a go with that tomorrow.

[Dipwind]

Hello Matt,

and thanks for your help, I also tomorrow will try to change the code. probably some questions I would raise :)

many thanks
Jo

[Dipwind]

I'm getting crazy with this, :)

At home every thing runs smoothly, here at work is a problem. now, I'm getting Compiled error on module2, with this line highlighted in blue "objol As New Outlook.Application"

the only difference in the code is the localization that I've changed, and in my work I have a header with seven more rows (logo and sheet title) but they don't interfere with the ones that contain the information.

I've also unmerged the cells and deleted the extra columns on "EDB", but even more strange for me is your sample works fine in my computer but in my work no. I'm feeling in a dead end.

Jo

[mvidas]

Did you set a reference to "Microsoft Outlook x.x Object Library"? I use outlook 2000 (9.0), and the sample file above looks like it has a reference to ol2003 (11.0) set. If you to go Tools / References in VBA, you may see the word "MISSING: " added before the outlook reference.. uncheck that then check whatever outlook version you have.

[Dipwind]

mvidas,

Well what can I say :rotlaugh: I've returned to life again :) thanks for the tip.

I also change the code to show on the e-mail the text as I want, as far I understand, I can't put a variable there, is that true? that means on the display text, I cannot put the issue number, because each one are different...

after your answer I can close this thread.

mdmackillop, my many thanks for your support during this thread, it was fantastic the way that you guys, shared your knowlege.

a small step for you but a big step for me.

my many thanks again.

Jo

[mdmackillop]

Glad you've got it working.
You should be able to use a variable in DisplayText, it just has to be incorporated into the string.

[mvidas]

Malcolm is correct, just takes putting a variable into the string. For example:HLink = "<a href=""file://" & Path & "NewBook-" & Target.Row & ".xls" & _
""">" & IssueNumber & "</a>"Matt

[mvidas]

Also, regarding the reference to Outlook, you can remove that reference altogether by making the following changes to your code:
' Dim objOL As New Outlook.Application, objMail As MailItem
Dim objOL As Object, objMail As Object
' Set objOL = New Outlook.Application
Set objOL = CreateObject("outlook.application")
' Set objMail = objOL.CreateItem(olMailItem)
Set objMail = objOL.CreateItem(0) '0=olMailItem
That uses a technique called "late binding" which can use a class of objects without a reference to that class. That should allow your code to work on multiple computers, regardless of the version of Outlook on the PC.
Matt

[Dipwind]

Thanks again guys, I probably have another setting wrong on my outlook in my work, because with the HTML format, I can't click on the link.

I've changed the code, and I've used the display text to write some information there, but when trying to click on the link nothing happen, even typing Ctrl.

any clue?

thanks in advance
jo

[mvidas]

Hmm.. it works fine with my testing..
Thinking a little more, the link won't work when the message is just being .Display'ed, but it will work when it is sent. Try sending it to yourself, then checking on the received email to see if it works for you.

If still no joy, try running just this sub, and send the email to yourself:Sub HTMLBodyTest()
Dim olApp As Object
Set olApp = CreateObject("outlook.application")
With olApp.CreateItem(0)
.HTMLBody = "Hi<br><br><a href=""http://www.google.com"">This link should " & _
"take you to google</a>"
.Display
End With
Set olApp = Nothing
End SubSee if that works at least for you

[Dipwind]

All sorted :rotlaugh: , Now is working in a perfection.

As soon I received the e-mal I can now following the link.

my many many many thanks for your help.

I have more questions related to this project but I will start another thread.

once again my many thanks to mdmackillop and mvidas

Jo

[mvidas]

Happy to help!