Start Free Trial

asked on

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

I'll take a look. Have you seen my reply to the previous question?

Hi Mike I'm looking at this one now.

I think I have covered everything for this.
Supplier-Meetings-status--4-.xlsm

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

ASKER

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

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.

ASKER

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

Do you get any error messages?

ASKER

No error messages at all.

Mike

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

ASKER

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

Hi Mike

I'll take alook, sorry about the delay.

ASKER

Thanks Roy

ASKER

Hi Roy,

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

Mike

Hi Mike

I've been snowed under at work, but I'll be reviewing the code this afternoon

ASKER

No problem, thanks!

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

ASKER

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

Hi Mike

can you just confirm the full file path and I'll check it this evening

ASKER

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

I'll check it out when I get home.

Hi Mike

The path seems to be truncating. I've got some dummy code working but I just need to tidy it up

ASKER

great thanks for the update.

Mike

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

ASKER

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

ASKER

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

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.

ASKER

That's fine, thanks Roy

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

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!

ASKER

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

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

ASKER

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

No problem Mike

ASKER

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

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.

ASKER

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

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.

ASKER

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

I would add instructions to use the navigate button that I added to the ribbon