Run Time Error 70 Permission Denied Excel VBA

L

Legacy 286866

Guest
I have created some code that will move emails to a folder, add a unique ID, put into a spreadsheet and not overwrite duplicates.
This worked when I made it and now it comes up with Run Time Error 70 Permission Denied. Been looking through the code and cant figure out where or why this is happening.
Can you guys see anything I am missing?

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">
Rich (BB code):
Rich (BB code):
Rich (BB code):

Option Explicit
Const fPath AsString="C:\Users\Emails"'The path to save the messages

Sub Download_Outlook_Mail_To_Excel()
Dim olApp AsObject
Dim olFolder AsObject
Dim olNS AsObject
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim NextRow AsLong
Dim i AsLong
Dim olItem AsObject
    Set xlBook = Workbooks.Add
    Set xlSheet = xlBook.Sheets(1)
    OnErrorResumeNext
    Set olApp = GetObject(,"Outlook.Application")
    If Err <>0Then
        Set olApp = CreateObject("Outlook.Application")
    EndIf
    OnErrorGoTo0
    With xlSheet
        .Cells(1,1)="Sender"
        .Cells(1,2)="Subject"
        .Cells(1,3)="Date"
        '.Cells(1, 4) = "Size"
        .Cells(1,5)="EmailID"
        .Cells(1,6)="Body"
        CreateFolders fPath
        Set olNS = olApp.GetNamespace("MAPI")
        Set olFolder = olNS.PickFolder
        ForEach olItem In olFolder.Items
            NextRow =.Cells(.Rows.Count,"A").End(xlUp).Row +1
            If olItem.Class=43Then
                .Cells(NextRow,1)= olItem.Sender
                .Cells(NextRow,2)= olItem.Subject
                .Cells(NextRow,3)= olItem.SentOn
                '.Cells(NextRow, 4) =
                .Cells(NextRow,5)= SaveMessage(olItem)
                .Cells(NextRow,6)= olItem.Body
            EndIf
        Next olItem
    EndWith
     MsgBox "Outlook Mails Extracted to Excel"
lbl_Exit:
    Set olApp =Nothing
    Set olFolder =Nothing
    Set olItem =Nothing
    Set xlBook =Nothing
    Set xlSheet =Nothing
    ExitSub
EndSub

Function SaveMessage(olItem AsObject)AsString
Dim Fname AsString
    Fname = Format(olItem.ReceivedTime,"yyyymmdd")& Chr(32)& _
            Format(olItem.ReceivedTime,"HH.MM")& Chr(32)& olItem.SenderName &" - "& olItem.Subject
    Fname = Replace(Fname, Chr(58)& Chr(41),"")
    Fname = Replace(Fname, Chr(58)& Chr(40),"")
    Fname = Replace(Fname, Chr(34),"-")
    Fname = Replace(Fname, Chr(42),"-")
    Fname = Replace(Fname, Chr(47),"-")
    Fname = Replace(Fname, Chr(58),"-")
    Fname = Replace(Fname, Chr(60),"-")
    Fname = Replace(Fname, Chr(62),"-")
    Fname = Replace(Fname, Chr(63),"-")
    Fname = Replace(Fname, Chr(124),"-")
    SaveMessage = SaveUnique(olItem, fPath, Fname)
lbl_Exit:
    ExitFunction
EndFunction

PrivateFunction SaveUnique(oItem AsObject, _
                            strPath AsString, _
                            strFileName AsString)AsString
Dim lngF AsLong
Dim lngName AsLong
    lngF =1
    lngName = Len(strFileName)
    DoWhile FileExists(strPath & strFileName &".msg")=True
        strFileName = Left(strFileName, lngName)&"("& lngF &")"
        lngF = lngF +1
    Loop
    oItem.SaveAs strPath & strFileName &".msg"
    SaveUnique = strPath & strFileName &".msg"
lbl_Exit:
    ExitFunction
EndFunction

PrivateSub CreateFolders(strPath AsString)
Dim strTempPath AsString
Dim iPath AsLong
Dim vPath AsVariant
    vPath = Split(strPath,"\")
    strPath = vPath(0)&"\"
    For iPath =1To UBound(vPath)
        strPath = strPath & vPath(iPath)&"\"
        IfNot FolderExists(strPath)Then MkDir strPath
    Next iPath
EndSub

PrivateFunction FolderExists(ByVal PathName AsString)AsBoolean
   Dim nAttr AsLong
   OnErrorGoTo NoFolder
   nAttr = GetAttr(PathName)
   If(nAttr And vbDirectory)= vbDirectory Then
      FolderExists =True
   EndIf
NoFolder:
EndFunction

PrivateFunction FileExists(filespec)AsBoolean
Dim fso AsObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(filespec)Then
        FileExists =True
    Else
        FileExists =False
    EndIf
lbl_Exit:
    ExitFunction
EndFunction
</code>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
My god.... I found the issue. The folder where I was trying to save the emails had special permissions..... (sorry). Now resolved.
 
Upvote 0

Similar threads

L
Replies
0
Views
585
Legacy 286866
L

Forum statistics

Threads
1,215,045
Messages
6,122,830
Members
449,096
Latest member
Erald

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top