Thread: Email Addresses

Hi Guys

I have a question. I have saved an outlook template for use when evaluating suppliers and an Excel 2007 spreadsheet with supplier account details including email addresses. Ideally for this particular spreadsheet i would like to be able to click on anyone of the email addresses and have it open the template automatically. i have set up the email addresses using the hyperlink method which may or may not be correct.

any help would be very much appreaciated.

Phil

This works when double clicking the cell with the email address in. You'll need to change the path below to where your template is saved:

The Red is the path for the template
The Blue is the range where your email addresses are

You'll want to remove the hyperlinks from the sheet

This code goes into the worksheet module of the sheet with your email addresses in.

Right click on the sheet name at the bottom, right click > view code

Paste the below into the blank white screen that comes up

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Range("A1:A60"), Target) Is Nothing Then

    Dim myOlApp As Object
    Dim MyItem As Object
    Set myOlApp = CreateObject("Outlook.Application")
    Set MyItem = myOlApp.CreateItemFromTemplate("D:\My documents\untitled.oft")
    MyItem.to = Target.Value
    MyItem.Display
End If


End Sub

Hi Phil

i open a new email message with the correct address present

Isn't that what you wanted?

Have you done this?

Right click on the sheet name at the bottom, right click > view code

Paste the below into the blank white screen that comes up

Have you changed this to the path of your template?

.CreateItemFromTemplate("D:\My documents\untitled.oft")

You may be using excel 2007 onwards which I believe has restrictions on which types of workbooks you can run macros in. Unfortunately I only have '03 so can't help, but I'm sure someone will

I asked here http://www.excelforum.com/the-water-...thread-23.html how to resolve this. I don't have 2007 but at a guess you need to Save as a macro workbook so the extension needs to be xlsm or xls (for backward compatibility). In 2003 I would go to file > save as > then change the bottom dropdown box to change the file type.

I know 2007 is different though, but I hope this helps