VBA creating file and retrieving file from SharePoint site

[epiodos]

Hello,
I'm in the process of moving files from a local server to SharePoint/Office 365. I have a Macro that creates a folder on the local drive then saves individual files created from a mail merge in Word to that folder and a second Macro that pulls those files into individual emails attachments. I've looked through the web but still don't really understand how to use VBA to call/save to SharePoint. I also think I probably need to change the way I attach the file to the email since I would need to send a copy. Any help is appreciated. Thank you!

Here is the current relevant code:

To create the folder:
Dim StrFolder As String, fileName As String, MainDoc As Document, i As Long, j As Long
Set MainDoc = ActiveDocument
Dim sDate As String

sDate = Format(Now(), "dd-mm-yyyy")
StrFolder = ActiveDocument.Path & "\" & sDate & "\

If Dir(StrFolder, vbDirectory) = "" Then
MkDir StrFolder 'makes dated folder if not created
End If

Saving the file:
With ActiveDocument
.SaveAs FileName:=StrFolder & fileName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
.SaveAs FileName:=StrFolder & fileName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
.Close SaveChanges:=False
End With



Then in the second Macro:

Sub send_Email_With_Attachment()
Dim edress As String, subj As String, message As String, filename As String
Dim outlookapp As Object, mailItem As Object, myAttachments As Object
Dim path As String, lastrow As Integer, attachmentstr As String, x As Integer
Dim sDate As String, StrFolder As String
Dim Salutation As String, Paragraph1 As String, Paragraph2 As String

'setting up location of attachments
sDate = Format(Now(), "dd-mm-yyyy")
StrFolder = ActiveWorkbook.path & "\" & sDate & "\"
x = 2

'goes through the lines of the excel
Do While Sheet1.Cells(x, 1) <> ""
Set outlookapp = CreateObject("Outlook.Application")
Set mailItem = outlookapp.createitem(0)
Set myAttachments = mailItem.attachments
path = StrFolder
edress = Sheet1.Cells(x, 6) 'refers to email address column

'setting up the attachment and subject line
subj = "Tax Receipt from " + Sheet1.Cells(x, 60) 'refers to subject variant
filename = Trim(Sheet1.Cells(x, 61)) + ".pdf"
attachmentstr = StrFolder + filename

'sets up the top part of the email
mailItem.to = edress
mailItem.Subject = subj

..... code here creates body of email message.....

'add attachment and send email
myAttachments.Add (attachmentstr)
mailItem.display
mailItem.send

lastrow = lastrow + 1
edress = ""

x = x + 1

Loop


End Sub

 

[epiodos]

Sorry Just noticed how to do the VBA code thing. Hoping this helps make things more clear

To create the folder:

Dim StrFolder As String, fileName As String, MainDoc As Document, i As Long, j As Long
Set MainDoc = ActiveDocument
Dim sDate As String

          sDate = Format(Now(), "dd-mm-yyyy")
          StrFolder = ActiveDocument.Path & "\" & sDate & "\

If Dir(StrFolder, vbDirectory) = "" Then
           MkDir StrFolder 'makes dated folder if not created
End If

Saving the file:

With ActiveDocument
          .SaveAs FileName:=StrFolder & fileName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
          .SaveAs FileName:=StrFolder & fileName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
          .Close SaveChanges:=False
End With

Then in the second Macro:

Sub send_Email_With_Attachment()
Dim edress As String, subj As String, message As String, filename As String
Dim outlookapp As Object, mailItem As Object, myAttachments As Object
Dim path As String, lastrow As Integer, attachmentstr As String, x As Integer
Dim sDate As String, StrFolder As String
Dim Salutation As String, Paragraph1 As String, Paragraph2 As String

'setting up location of attachments
sDate = Format(Now(), "dd-mm-yyyy")
StrFolder = ActiveWorkbook.path & "\" & sDate & "\"
x = 2

'goes through the lines of the excel
Do While Sheet1.Cells(x, 1) <> ""
        Set outlookapp = CreateObject("Outlook.Application")
        Set mailItem = outlookapp.createitem(0)
        Set myAttachments = mailItem.attachments
        path = StrFolder
        edress = Sheet1.Cells(x, 6) 'refers to email address column

       'setting up the attachment and subject line
       subj = "Tax Receipt from " + Sheet1.Cells(x, 60) 'refers to subject variant
       filename = Trim(Sheet1.Cells(x, 61)) + ".pdf"
       attachmentstr = StrFolder + filename

       'sets up the top part of the email
       mailItem.to = edress
       mailItem.Subject = subj

        '..... code here creates body of email message.....

        'add attachment and send email
        myAttachments.Add (attachmentstr)
        mailItem.display
        mailItem.send

        lastrow = lastrow + 1
        edress = ""

       x = x + 1

Loop


End Sub