send email with hyper-link macro

I would like one macro to do the following;

phase 1
Send the following email to the email addresses found in the range B86:B93 on the active sheet (saxon 03.07.2015) in the attached workbook.

Email
Hi,

Please could you complete the action points found on the supplier meeting report (link below).
Once you have completed your action points please change the status from the drop down list found in column C/D and leave any relevant comments in the STAKEHOLDER COMMENTS column.

Once completed please click the save & close button at the top of the page.

If you have any questions regarding your action points please contact the appropriate category manager found in cell "E6"

(insert meeting report hyper-link *see PHASE 2 below)

Thanks
Supplier Relationship Team

Email subject = Supplier Meeting Action Points

phase 2

Insert a hyper-link at the bottom of the email body sent to the all of the email addresses in the range B86:B93.

The hyper-link should send the recipient of the email to cell A1 of the active sheet where this macro will be run from.

The file path for the active sheet is P:\Supplier Relations\Supplier Meetings status.xlsm
I would like the link in the email to be displayed as the value found in cell B2 on the active sheet. (note: this value will change as values on the active sheet will vary).

PHASE 3

Once the emails have been sent to the email addresses, cell B3 should be populated with report sent on: (TODAY'S DATE)

TODAY'S DATE = date the macro is run and the emails are sent.

PHASE 4

The workbook should then be saved (not save as). The workbook should remain open.

Rules

1.If the same email appears twice in the range B86:B93 then only one email should be sent to that address. i.e. the recipient should only receive one email.

2. The active sheet will vary depending on the choice of the user. However the layout of the report will be exactly the same. The values will change depending on the users entries.

3. Note:all recipients will have access to the file path as they will be using the same server

Thanks Mike
Supplier-Meetings-status.xlsm
mikes6058Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
I'll take a look. Have you seen my reply to the previous question?
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike I'm looking at this one now.
0
Roy CoxGroup Finance ManagerCommented:
I think I have covered everything for this.
Supplier-Meetings-status--4-.xlsm
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Roy CoxGroup Finance ManagerCommented:
Mike, I forgot to add the text file that contains the message for the email. Place this file in the same folder as the workbook
MsgBody.txt
0
mikes6058Author Commented:
Hi Roy,

Thank-you for moving on to this question. I've just tried running this code but I am getting the following error:



Compile error
Invalid use of property

on line: ThisWorkbook.Saved True

Also do I have to input any paths/file locations into the code?

Also, I am going to do some testing on the buttons you've created no the other question. I'll get back to you soon.

Mike
0
Roy CoxGroup Finance ManagerCommented:
Sorry I added that line in hurry because I missed it. It should be

ThisWorkBook.Save

Open in new window


I'll tidy it up later.

All paths are coded.
0
mikes6058Author Commented:
Thanks Roy,

Just to let you know.

I've saved the msgbody file into the same folder as the workbook and have changed the line mentioned. When I run the code nothing appears to happen?

Mike
0
Roy CoxGroup Finance ManagerCommented:
Do you get any error messages?
0
mikes6058Author Commented:
No error messages at all.

Mike
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I emailed to my email address with no problem at all except the hyperlink. If you message me your email address I'll forward it to you
0
mikes6058Author Commented:
try mike6058@gmail.com

Can you check the attached sheet to ensure I have everything right.

Based on the current selection one email should be sent to rob.marr@thstools.co.uk and one email to robmarr789@gmail.com

Note: I have changed the range in the vlookups for the email addresses as it was looking in the wrong range.

Mike
Supplier-Meetings-status--4---5-.xlsm
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I'll take alook, sorry about the delay.
0
mikes6058Author Commented:
Thanks Roy
0
mikes6058Author Commented:
Hi Roy,

Sorry did you manage to test the code on the attached sheet?

Mike
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I've been snowed under at work, but I'll be reviewing the code this afternoon
0
mikes6058Author Commented:
No problem, thanks!
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

Can you test his on your system and let me know if you have any problems.

Msgbody.txt needs to be in the same folder as the workbook
Supplier-Meetings-status--4---5---1-.xls
MsgBody.txt
0
mikes6058Author Commented:
Hi Roy,

The email is now sending correctly.

However when I click on the hyper-link in the email to send the user back to the worksheet I am getting the following error.

Cannot find file '\\SERVER2k8\data/supplier'.please verify the path or internet address is correct

Mike
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

can you just confirm the full file path and I'll check it this evening
0
mikes6058Author Commented:
Hi Roy,

The path to the file is P:\Supplier Relations\Supplier Meetings status.xlsm

They should then automatically be sent to the worksheet active when the email macro was run

The path to the file is therefore the same as the active sheet that the email macro is run from

Mike
0
Roy CoxGroup Finance ManagerCommented:
I'll check it out when I get home.
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

The path seems to be truncating. I've got some dummy code working but I just need to tidy it up
0
mikes6058Author Commented:
great thanks for the update.

Mike
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

This seems to work. Give it a test, I'm having problems with my OutLookl but it displays & the link works.

Supplier-Meetings-status--4---5---4-.xls
0
mikes6058Author Commented:
Hi Roy,

Thanks the link is now working. The only thing I've noticed is that the file does not open on the worksheet that  was open when the user ran the email macro.

For example, if a user runs the macro from the worksheet Ledco 04.07.2015 I would then want the link from the email to automatically send the user straight to the Ledco 04.07.2015 worksheet. At the moment the hyper-link will send the user to the last active work sheet open when the file was saved.

There will be a number of different supplier meeting worksheets in the file.

If this isn't possible would it be possible to instruct the user in the email to "find the meeting report *name* from the meeting.task.status.log"

*name* = the value of cell B2

If this method is used the original hyper-link will need to remain in the email to send the user to the file.

let me know your thoughts
Mike
0
mikes6058Author Commented:
Also just a small thing...

I'm not sure why but the file path has been added to the body of the email. Is it possible to prevent this line from being added? Line underlined and bold

Hi Please could you complete the action points found on the supplier meeting report (link below). Once you have completed your action points please change the status from the drop down list found in column C/D and leave any relevant comments in the STAKEHOLDER COMMENTS column. Once completed please click the save & close button at the top of the page. If you have any questions regarding your action points please contact the appropriate category manager found in cell "E6" of the sheet. Supplier-Meetings-status--4---5---4-.xls is created.
Click on this link to open the file : Supplier Meeting Status Report Thanks Supplier Relationship Team
0
Roy CoxGroup Finance ManagerCommented:
I'm going to edit the message if the actual link is OK. I haven't used HTMLBody before but it seems I'll have to enter the whole nessage there not import it from the text file.
0
mikes6058Author Commented:
That's fine, thanks Roy
0
Roy CoxGroup Finance ManagerCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikes6058Author Commented:
Hi Roy,

Thanks the Supplier-Meetings-status--4---5---4-.xls is created. line has now been removed and the layout of the email is a lot better. Did you manage to see my second to last comment regarding the automation of the destination worksheet when the link is opened? If its another sizeable task, I'm happy to open a new question for it

Mike
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I'll need to play with the hyperlink for activating the sheet. Adding instructions is not difficult.

I can play around tomorrow now we have the message working.

Your projects certainly send me down different coding techniques!
0
mikes6058Author Commented:
Great, I didn't think it would be straight forward.

I'm glad I've helped broaden your skill set! :/

Thanks for the hard work again Roy

Mike
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

Can you try this. It won't open for me but it could be due to the workbook trying to update links.
Supplier-Meetings-status--4---5---6-.xls
0
mikes6058Author Commented:
Hi Roy,

Just to let you know I am currently away from the office and don't have access to my work server.  I will be back in the office on Wednesday so I will test and report back to you then.

Mike
0
Roy CoxGroup Finance ManagerCommented:
No problem Mike
0
mikes6058Author Commented:
Hi Roy,

I've tested the link but it doesn't appear to work.

The link itself appears to be right but the added #sheetname!a1 doesn't direct the user directly to desired worksheet when the file is opened.

I've tested this method manually on a basic worksheet with two worksheets but it doesn't seem to work.

Perhaps we could resort to my other suggestion - see below

If this isn't possible would it be possible to instruct the user in the email to "find the meeting report *name* from the meeting.task.status.log"

*name* = the value of cell B2

If this method is used the original hyper-link will need to remain in the email to send the user to the file.

Using the ledco meeting report as an example the body email would display as below

Hi,

Please could you complete the action points found on the supplier meeting report (link below)

Once you have completed your action points please change the status from the drop down list found in column C/D and leave any relevant comments in the STAKEHOLDER COMMENTS column.

Once completed please click the save & close button at the top of the page.:

If you have any questions regarding your action points please contact the appropriate category manager found in cell E6 of the sheet.:

Please open the meeting report using the link below and select the meeting report link called Ledco 04.07.2015 found in column E

*Insert file link here*

Regards,

Supplier Relationship Team
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I'll have to amend the message and include instructions on how to locate the file using the button in the Ribbon.

I'll do this Friday afternoon, before if I get chance.
0
mikes6058Author Commented:
Hi Roy,

Just to let you know I've managed to sort this. Thanks again for your work on this project. I should have some one off problems coming up soon.

Mike
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I've been busy the last couple of days, a new grandson arrived!

What was the fix?

Just message me if you need specific help.
0
mikes6058Author Commented:
Hi Roy,

Congratulations to you and your family!

At this stage I have settled for referencing the value of Cell B2 in the body of the email which I was able to work out.

I still haven't found a way of automatically directing the user to a desired worksheet in the work book. If you come across anything let me know.

Thanks
Mike
0
Roy CoxGroup Finance ManagerCommented:
I would add instructions to use the navigate button that I added to the ribbon
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.