Word file from SharePoint corrupted when sent via Outlook

Question

I have a macro that will populate a Word template with fields from an Excel sheet and save it as a standalone document.

The file is saved on SharePoint. I can open, download and edit it.

Sub inpWord()
    Dim bStarted As Boolean, wdApp As Word.Application, wdDoc As Word.Document, wdTemp As String
    
    wdTemp = "XXXXXX SHAREPOINT LINK XXXXXXX"
    Set inpBook = xlApp.Workbooks("Input Tool")
    
    On Error Resume Next 
        'Open word 
       
    Set wdDoc = Documents.Add(Template:=wdTemp, NewTemplate:=False, DocumentType:=wdNewBlankDocument, Visible:=True)
    Filename = "SHAREPOINT FOLDER LINK" & CStr(RefNum) & ".docx?web=1"
    
    With wdDoc
        Set CCDate = .SelectContentControlsByTitle(1)
        'For loop to populate word template
        .SaveAs Filename
    End With

End Sub

I send the document after recalling it from SharePoint using another macro. There is no error during the process but the attachment is corrupted in the email.

Private Sub CommandButton1_Click()
Dim bStarted As Boolean, oOutlookApp As Outlook.Application, oItem As Outlook.MailItem, xlApp As Object, xlBook As Object

Set xlApp = GetObject(, "Excel.Application")
Set xlBook = xlApp.Workbooks("Purchasing Tracker")

POAttach = "SHAREPOINT LINK" & CStr(PONum) & ".docx?web=1"

On Error Resume Next 'Open Outlook unless already open
    Set oOutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
        Set oOutlookApp = CreateObject("Outlook.Application")
        bStarted = True
        Err.Clear
    End If
    oOutlookApp.Visible = True

Set oItem = oOutlookApp.CreateItem(olMailItem)

    With oItem
        .To = "RECIEVER"
        .Subject = "XX"
        .HTMLBody = "XX"
        .Attachments.Add Source:=POAttach, Type:=1, _
        DisplayName:="Formula Student Purchase Order: " & CStr(UserForm1.TextBox1.Value)
        .Send
    End With
End Sub

I tried to only include the necessary portions. All macros are written in the Excel documents, nothing in Outlook or Word.

Answer 1

The Attachments.Add method is not aware about hyperlinks and can't deal with files stored on the remote server. You need to download the file locally and then pass a local file path instead.

The source of the attachment can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment.