I am trying to redo some code that works flawlessly on a local or shared drive, but I need to use SharePoint as the location for my files as it is the only place we can guarantee everyone has access too.
My code is simple, all it does is look to see if there was a list of external email addresses and if so it opens a template email and attaches all files in a given location to that email, then blind carbon copies everyone. a
It looks like this.
I have tried to just add a path to the SharePoint Library which works fine for opening and saving files, or even directly referencing them to attach it to an email (Example: .Attachments.Add "http://SharePoint/Library/MyFile.pdf works just fine). Unfortunately SharePoint Libraries apparently can't be used as a directory I can loop through. Is there a method I can use to do this?
My code is simple, all it does is look to see if there was a list of external email addresses and if so it opens a template email and attaches all files in a given location to that email, then blind carbon copies everyone. a
It looks like this.
Code:
Sub SendExtEmail()
Dim OutApp
Dim OutMail As Outlook.MailItem
Dim ExtAttach, EVMmail As String
Sheets("Macro Run Controls").Select
ExtAttach = C:\Path to files
EVMmail = C:\Location of saved email template
'Create the external manager email
Set OutApp = CreateObject("Outlook.Application")
On Error Resume Next
Set OutMail = OutApp.CreateItemFromTemplate(EVMmail)
With OutMail
.SentOnBehalfOfName = "YourEmail@mail.com"
.To = ""
.CC = ""
.BCC = "YourEmail@mail.com; " & ExtMgrStr
StrFile = Dir(ExtAttach & "*.*")
Do While Len(StrFile) > 0
.Attachments.Add ExtAttach & StrFile
StrFile = Dir
Loop
.Display 'or use .Send
End With
On Error GoTo 0
End Sub
I have tried to just add a path to the SharePoint Library which works fine for opening and saving files, or even directly referencing them to attach it to an email (Example: .Attachments.Add "http://SharePoint/Library/MyFile.pdf works just fine). Unfortunately SharePoint Libraries apparently can't be used as a directory I can loop through. Is there a method I can use to do this?