Path not found due to different folder structure for each user

Question

I have VBA code which finds an email template through a file path and pre-fills the email based on cells within the spreadsheet. It also finds the email attachment based on the name of the attachment in the cell and finds that within the file path.

This worked locally, however it is now being used by colleagues and all have different file path accessed as below:

Me: G:\Group\Data\Business\Call\Excel\Email.oft
Colleague 1: G:\Data\Business\Call\Excel\Email.oft
Colleague 2: G:\Business\Call\Excel\Email.oft

We can all view and click on the email template.

When inserting the file path into the VBA code, if I put my file path, they cannot access it and if I put their file path, I cannot access it with the error

Path not found

I assume as there is a “step” missing in the path.

How can I bypass the file path restrictions?

I tried \..\ to replace the missing paths but does not work/fill the gap.

The primary Excel spreadsheet is opened via a SharePoint site so cannot use ThisWorkbook.Path.

Sub Send_email_fromtemplate()
Dim edress As String
Dim subj, name As String
Dim filename As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim path As String
Dim attachment As String
Dim r As Long
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim customername As String

r = 2

Do While Sheet1.Cells(r, 1) not equal to  ""
    Set outlookapp = CreateObject("Outlook.Application")
    'call your template
    Set outlookmailitem = outlookapp.CreateItemFromTemplate("G:\Group\Data\Business\Call\Excel\Email.oft")
    outlookmailitem.Display
    Set myAttachments = outlookmailitem.Attachments
    'deifine your path for the attachment
    path = "G:\Group\Data\Business\Call\Excel\"
    edress = Sheet1.Cells(r, 1)
    name = Sheet1.Cells(r, 2).Value
    subj = Sheet1.Cells(r, 3)
    filename = Sheet1.Cells(r, 4)
    attachment = path + filename
    With outlookmailitem
        .To = edress
        .cc = ""
        .bcc = ""
        .Subject = subj
        myAttachments.Add (attachment)
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="{{Placeholder for Name}}")
                oRng.Text = name
                Exit Do
            Loop
        End With
        Set xInspect = outlookmailitem.GetInspector
  
       .Display
        '.send
    End With
    'clear your email address
    edress = ""
    r = r + 1
Loop
'clear your fields
Set outlookapp = Nothing
Set outlookmailitem = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
End Sub

There are around 20 users but have no way of determining all their file paths. Also I want to make it accessible for one-off users.

Answer 1

You could use the server path rather than the local path. This shouldn't be different no matter what drive letter you're mapped to.

.CreateItemFromTemplate("\\ServerName\Darren Bartrup-Cook\Test\OL_Template.oft")

One way to find the path (other options are no doubt available):

• Press Windows Key + R to open the Run dialog box.
• Type "cmd" and click OK.
• In the Command Prompt window enter "net use" and press enter.