VBA to save file to SharePoint and attach to outlook template saved in SharePoint

[Chris8630]

Hi all

I'm hoping this is a quick fix. I have a file which I want to attach a copy of a sheet to a separate workbook, save that workbook down and then attach it to an Outlook template. All files are saved/to be saved in SharePoint. The code I have currently is:

Sub Email()
Dim fileextstr As String
Dim a As String
Dim fileformatnum As Long
Dim sourcewb As Workbook
Dim destwb As Workbook
Dim tempfilepath As String
Dim tempfilename As String
Dim outapp As Object
Dim outmail As Object
Dim ws As Worksheet
Dim theactivewindow As Window
Dim tempwindow As Window
a = Sheets("PDN").Range("B5")
Application.ScreenUpdating = False
Application.EnableEvents = False
Set sourcewb = ActiveWorkbook
With sourcewb
Set theactivewindow = ActiveWindow
Set tempwindow = .NewWindow
.Sheets(Array("PDN")).Copy
End With
tempwindow.Close
Set destwb = ActiveWorkbook
If Not IsEmpty(destwb.LinkSources(xlExcelLinks)) Then
For Each link In destwb.LinkSources(xlExcelLinks)
destwb.BreakLink link, xlLinkTypeExcelLinks
Next link
End If
Sheets("PDN").Select
Range("B5").Copy
Range("B5").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=False
'With destwb
'If Val(Application.Version) < 12 Then
'fileextstr = ".xls": fileformatnum = -4143
'Else
'Select Case sourcewb.FileFormat
'Case 51: fileextstr = ".xlsx": fileformatnum = 51
'Case 52:
'If .HasVBProject Then
'fileextstr = ".xlsm": fileformatnum = 52
'Else
'fileextstr = ".xlsx": fileformatnum = 51
'End If
'Case 56: fileextstr = ".xls": fileformatnum = 56
'Case Else: fileextstr = ".xlsb": fileformatnum = 50
'End Select
'End If
'End With
'tempfilepath = "sharepoint address here"
'tempfilename = a
Set outapp = GetObject(, "Outlook.Application")
Set outmail = outapp.createitem(0)
With destwb
ActiveWorkbook.SaveAs "sharepoint address here" & a & ".xlsx"
On Error Resume Next
Set outapp = GetObject(, "outlook.application")
Set outmail = outapp.createitemfromtemplate("sharepoint address here/PDN%20EMAIL.oft")
On Error Resume Next
With outmail
.attachments.Add destwb.FullName
.display
'.send
End With
On Error GoTo 0
Set outmail = Nothing
Set outapp = Nothing
.Close savechanges:=False
End With
'Kill tempfilepath & tempfilename & fileextstr
'End With
Sheets("PDN").Select
Range("L1").Copy
Range("B5").PasteSpecial xlPasteFormulas, operation:=xlNone, skipblanks:=False, Transpose:=False
Set outmail = Nothing
Set outapp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

This works no problem if everything is located on a standard network drive but is failing to save onto sharepoint.

Any suggestions/code re-writes/etc are greatly appreciated

 

[Micron]

There may be another way but the only way I know is to get the SP (SharePoint) server address. Open File Explorer. It has been a long time so I can't recall if you right click on a drive letter (such as F, but not C) or if you right click on the server name that is shown at the top of the drive letter list. I think it is the latter. What you're looking for in the right click menu is the properties option, and after selecting that you're looking for the path property of the server in the resulting dialog. It will start with two back slashes ( \\ ). If you find that, now you know what to look for going forward. I mention this first because I suspect you do not have a mapping to the SP server, and you will need that to discover the SP server path in this manner. You will probably need help with mapping to the SP server just so you can know the path, or perhaps your IT department will tell you what the path is. NOTE: this is not https://something/something... That is the web or intranet address and is not what I'm referring to. I'm referring to the network address of the SP server. Once you have that, you use the complete server path in your code (beginning with \\ ).

That is how I accessed Excel files on a SharePoint server from M$ Access code. If you want to modify these files, you will probably have to check them in and out (as if books from a library) otherwise I doubt you can edit their contents.
HTH

Please post more than a few code lines within code tags (use vba button on posting toolbar) to maintain indentation and readability. I'm too old to even bother to read code like that. If I had $1 for every time I've had to point this out in forums I could retire. Oh, wait a minute...