Excel VBA to Outlook Email Draft - How to save email draft copy to sharepoint / How to use template from sharepoint?

[elitef]

Hi Everyone,

Hoping for some assistance from the smart folks on here.

I have a VBA in excel which generates an Outlook message draft from a template.
I'd like to know, if at all possible, two things:
- How can I make it so that the draft, once opened, is saved as a file to a sharepoint location?
-- If not possible to do that, then is there a way to have the VBA convert the email draft to a .doc/.docx document and upload that instead?
- How can I have the script use a template which is located on a sharepoint as opposed to locally on a desktop?

If neither is possible, does anyone have any workarounds to get this to somewhat work?

Any help would be greatly appreciated.

Dim OO As Object
On Error Resume Next
Set OO = GetObject(, "Outlook.Application")
On Error GoTo 0
If OO Is Nothing Then
ans = MsgBox("Outlook is not currently open. Would you like to open Outlook now?     If yes, Outlook will open and you will have to click on the Send Comms button again.", vbYesNo)
If ans = vbYes Then Shell ("Outlook")
If ans = vbNo Then Exit Sub
Else

    ThisWorkbook.Sheets("Email").Range("A" & ThisWorkbook.Sheets("Email").Rows.Count).End(xlUp).Offset(0).Range("K1").Value = Format(Now(), "MM/DD/YYYY hh:mm:ss")
 
    Dim NewEmail As MailItem
    Dim PathFileName As String
 

    Dim Recp As Variant
    Dim I As Long
    For I = 0 To Worksheets("List").Range("L" & Rows.Count).End(xlUp).Row Step 400
    Recp = Worksheets("List").Range("L2").Offset(I).Resize(400)
 
  PathFileName = Application.ActiveWorkbook.Path & "\Templates\EMAIL.oft"    'This is the section I'd love to see if at all possible to use a template off of a sharepoint site instead of locally
  Set NewEmail = CreateItemFromTemplate(PathFileName)
 

  With NewEmail

    .Subject = Worksheets("Email").Range("H1").Value
 
    .HTMLBody = Replace(.HTMLBody, "#BODY", Worksheets("Email").Range("H6").Value)
 
 
    BodyWithoutSignature = .HTMLBody
    .SentOnBehalfOfName = "Secondary@mailbox.com"
    .DeferredDeliveryTime = DateAdd("n", 3, Now)
    .ReplyRecipients.Add "AdditionalDL@mailbox.com"

    .To = "AdditionalDL@mailbox.com"
    .BCC = Join(Application.Transpose(Recp), ";")
    .Display
    .HTMLBody = BodyWithoutSignature

 
  End With
  Next
   
  End If