Hyperlink to Documents with same name as cell contents

sharks17 · Apr 17, 2012

Hello MrExcel Forums!

A little bit of help would be great. Using Excel 2007 (Also have 2010 on Terminal Server). I know how to create a hyperlink but need some advanced help to become more efficient.

Each day I send an email with a list of customer orders received. I use excel because the list comes from our system and pastes into excel in a format which is easy to work with - also because I just like Excel...

I then copy from Excel and paste into Outlook in HTML format. I am currently hyperlinking the order number field (See below Example cell B2) with a .pdf of the customer order which is located on our file server. The filename of the .pdf contains the order number (last digits). So basically from the daily report I send via outlook the reader can click on the order number and it will launch a pdf of the customer order. My question is, how can I automate the process of hyperlinking? To do this one by one is ridiculous. If the path is \\fileserver\orders\order 12345.pdf and this hyperlink is established in cell B2 (In my illustration below) is there a way to simply have excel copy the hyperlink to the cells below but only change the hyperlink last 5 digits to be whatever is the contents of the cell? So the hyperlink in cell B3 would be \\fileserver\orders\order 12346.pdf

Many thanks for the help and support I have received from you experts.

 <table class="MsoNormalTable" style="width:392.25pt;margin-left:4.65pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0in 5.4pt 0in 5.4pt" border="0" cellpadding="0" cellspacing="0" width="523"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:15.0pt"> <td style="width:134.0pt;background:white; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="179">
</td> <td style="width:158.0pt;background:white; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="211"> [FONT=&quot]A[/FONT]
</td> <td style="width:50.75pt;background:white; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="68"> [FONT=&quot]B[/FONT]
</td> <td style="width:49.5pt;background:white; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="66"> [FONT=&quot]C[/FONT]
</td> </tr> <tr style="mso-yfti-irow:1;height:15.0pt"> <td style="width:134.0pt;background:white; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="179">

[FONT=&quot]1[/FONT]

</td> <td style="width:158.0pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;background:white;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" nowrap="nowrap" valign="bottom" width="211"> [FONT=&quot]Company Name[/FONT]
</td> <td style="width:50.75pt;border:solid windowtext 1.0pt; border-left:none;mso-border-top-alt:solid windowtext .5pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: white;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="68"> [FONT=&quot]Order #[/FONT]
</td> <td style="width:49.5pt;border:solid windowtext 1.0pt; border-left:none;mso-border-top-alt:solid windowtext .5pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: white;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="66"> [FONT=&quot]Amount[/FONT]
</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:134.0pt;background:white; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="179">

[FONT=&quot]2[/FONT]

</td> <td style="width:158.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext .5pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: white;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="211"> [FONT=&quot]Bobs Apples[/FONT]
</td> <td style="width:50.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="68"> [FONT=&quot]12345[/FONT]
</td> <td style="width:49.5pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="66">

[FONT=&quot]100,000[/FONT]

</td> </tr> <tr style="mso-yfti-irow:3;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:134.0pt;background:white; padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="179">

[FONT=&quot]3[/FONT]

</td> <td style="width:158.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext .5pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: white;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="211"> [FONT=&quot]Janes Apples[/FONT]
</td> <td style="width:50.75pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="68"> [FONT=&quot]12346[/FONT]
</td> <td style="width:49.5pt;border-top:none; border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:white;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" valign="bottom" width="66">

[FONT=&quot]50,000[/FONT]

</td> </tr> </tbody></table>

Jerry Sullivan · Apr 18, 2012

Hi sharks17,

One approach would be to use formulas with the Hyperlink() function referencing the Order Numbers. You could hide the Column shown prior to copying your image into Outlook or have that Column outside the copied range.

Excel Workbook

A

B

C

D

1

Company Name

Hide Column

Order #

Amount

2

Bobs Apples

12345

100,000

3

Janes Apples

12346

50,000

Sheet

sharks17 · Apr 18, 2012

Thank you so much!

This worked wonderfully! The time savings from this is beyond what I even imagined.

Jerry Sullivan · Apr 18, 2012

You're very welcome!

Hyperlink to Documents with same name as cell contents

sharks17

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

sharks17

New Member

Jerry Sullivan

MrExcel MVP

Similar threads

Forum statistics

Share this page

Hyperlink to Documents with same name as cell contents

sharks17

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

sharks17

New Member

Jerry Sullivan

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock