Upload a file to Sharepoint 2007 from outlook 2007 and add metadata using VBA RRS feed

  • Question

  • Hi

    I need to upload a email from outlook 2007 to SharePoint a SharePoint library. The process is I Manually select the email and the macro selects the excel attachment and opens it, then I read the info in excel. My code does it fine, but I need to add information to a SharePoint library Colum, and I don’t know how to do it?

    Finally what I need is how to fill a colum when I upload the file to the SharePoint?

    I explained it in the code. :)

    This is my code:

    Public Sub SaveEmail11111111()
        Dim Msg As MailItem
        Dim sName As String
        Dim FileName As String
        Dim saveFolder As String
        Dim Attach_list(1 To 100) As String
        Dim i As Integer
        Dim Complain As Integer
        Dim Nature(1 To 100) As String
        Complain = 0
        Dim xlApp As Object
        Dim xlBook As Object
        Dim sourceWB As Object
        Dim NatureComp As String
    'Set xlBook = xlApp.Workbooks.Add
        saveFolder = "htp/YourSharePointSite/sites/outlook/" **Your site

        For Each Msg In Application.ActiveExplorer.Selection
            i = 1
            'Attach_list = Split(Msg.FileName)
            For Each atmT In Msg.Attachments
                Form1.CheckBox1 = atmT.FileName

                If Right(atmT.FileName, 3) = "xls" Or Right(atmT.FileName, 4) = "xlsx" Then
                    Attach_list(i) = atmT.FileName
        'File path and save the document
                    FileName = "C:\Excel\Example - " & Left(atmT.FileName, Len(atmT.FileName) - 15) & " " & Format(Now, "d mmm yyyy hhmmss") & ".xlsx" ' "C:\" & Atmt.FileName
                    Msg.Attachments.Item(i).SaveAsFile Path:=FileName
                    atmT.SaveAsFile FileName

    '****So the story begins here where I open the Excel workbook
                    Set xlApp = CreateObject("Excel.Application")
        ' Reading excel information
                    With xlApp
                        .Visible = True
                        .EnableEvents = True
                        .Workbooks.Open FileName:=FileName
                        For j = 0 To 30

    'Then I read a colunm and I have to import that many timees the file to SharePoint

    ' up to her ist fine

                            If xlApp.Application.workSheets(1).cells(13 + j, 6) <> "" Then
                              Complain = Complain + 1
                              Nature(Complain) = xlApp.Application.workSheets(1).cells(13 + j, 6)

    ' The thing is that in this fallowing line I save  the email to SharePoint, but I dont know how to fill the colimns, since I have to put in there the information on the Spreas sheet.

                              Msg.SaveAs saveFolder & Format(Now, "ddmmyyyy_hhmm") & "EAR" & ".msg",  olMSG

                                j = 100
                        End If
                        Next j
                    End With

                    With Form1.ComboBox1
                        .AddItem Attach_list(i)
                    End With
                    i = i + 1
                End If

            Next atmT

            NatureComp = "123"
    '**** Here is where I save the file but this saveas method doesn't acept more than the file path and format?
            Msg.SaveAs saveFolder & Format(Now, "ddmmyyyy_hhmm") & "EAR" & ".msg",  olMSG
    End Sub   '  ****Thanks for the help I really apreciate any sugestions

    • Edited by BryanLake Thursday, June 27, 2013 7:55 PM
    Tuesday, June 25, 2013 3:39 PM