VBA won't get file on D drive

[cLavreys]

Hello,

I have a problem attaching a pdf file from my D drive (with my OneDrive on). The same code works just fine when I move the file to my C drive, but not on D...
What am I doing wrong?
The code is not finished, it will send a specific pdf to a person.

Sub btnMailen()


Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem
Dim source_file, to_emails As String
Dim customer As String
Dim sFolderPathPDF As String
Dim nummer As String
Dim sPath As String



Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)

customer = Range("M7").Value
mailRange = Range("Klantenlijst!A4:G500")



    to_emails = Application.WorksheetFunction.VLookup(customer, mailRange, 7, False)
    nummer = Range("D11").Value & " " & Range("M7").Value
    'sPath = "C:\testje.txt"    'file is present, and exists
    sPath = "D:\1.txt"      'file is present, does not exist ????
   ' sPath = "D:\OneDrive\Firma\000. Boekhouding\03. Facturen\1.txt"        'file is present, does not exist ????
   
   ' vba problem with D drive? Opening the files the common way with explorer is no problem.
    
 If FileOrDirExists(sPath) Then
        MsgBox sPath & " exists!"
    Else
        MsgBox sPath & " does not exist."
    End If
    'source_file = "D:\OneDrive\Firma\000. Boekhouding\03. Facturen\001. pdf\2021\" & nummer

    'myMail.Attachments.Add source_file

'source_file = ThisWorkbook.FullName
'myMail.Attachments.Add source_file


myMail.To = to_emails
myMail.Subject = "Factuurtest"
myMail.Body = "Hi Everyone," & vbNewLine & "Please read these before the meeting." & vbNewLine & "Thanks"
 'myMail.Attachments.Add source_file
 'myMail.Attachments.Add ("D:\OneDrive\Firma\000. Boekhouding\03. Facturen\001. pdf\2021\" & nummer & "\")
myMail.Display


End Sub

 

[GWteB]

What does the code of the used FileOrDirExists function looks like?

 

[cLavreys]

Function FileOrDirExists(PathName As String) As Boolean
     'Macro Purpose: Function returns TRUE if the specified file
     '               or folder exists, false if not.
     'PathName     : Supports Windows mapped drives or UNC
     '             : Supports Macintosh paths
     'File usage   : Provide full file path and extension
     'Folder usage : Provide full folder path
     '               Accepts with/without trailing "\" (Windows)
     '               Accepts with/without trailing ":" (Macintosh)
     
    Dim iTemp As Integer
     
     'Ignore errors to allow for error evaluation
    On Error Resume Next
    iTemp = GetAttr(PathName)
     
     'Check if error exists and set response appropriately
    Select Case Err.Number
    Case Is = 0
        FileOrDirExists = True
    Case Else
        FileOrDirExists = False
    End Select
     
     'Resume error checking
    On Error GoTo 0
End Function

 

[GWteB]

This is a correct way to determine whether a path exists, although it doesn't return whether it is a file or a folder, but that probably doesn't matter for your issue.
I'm assuming you've checked that both your string variables named "nummer" and "source_file" get the correct value, so I've no idea why the Attachments.Add method fails.

 

[GWteB]

Since you're pulling values from worksheet ranges, there could be hidden spaces or line feed characters that you're not aware of.

 

[cLavreys]

Since you're pulling values from worksheet ranges, there could be hidden spaces or line feed characters that you're not aware of.

A part of the code is already used to get the values. This works perfectly. Another bit of code saves the file and exports a pdf in a folder on D:\OneDrive.
So the code I have works perfectly. In another button I want to send the generated PDF to a customer. When I put the PDF on my C drive, there's no problem...but my folder is located on D..

 

[GWteB]

You could try to put your file on OneDrive as well as in a (temporary) folder on your C drive. When composing your email, you pick the file as an attachment from that folder on the C drive, send the email and delete that temporary file afterwards. Just a suggestion. I don't use OneDrive so I'm afraid I can't help you further.

 

[cLavreys]

You could try to put your file on OneDrive as well as in a (temporary) folder on your C drive. When composing your email, you pick the file as an attachment from that folder on the C drive, send the email and delete that temporary file afterwards. Just a suggestion. I don't use OneDrive so I'm afraid I can't help you further.

Not the nicest solution, but it works like a charm. Thanks

for the interested:

Sub btnMailen()

Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem
Dim source_file, to_emails As String
Dim customer As String
Dim sFolderPathPDFtemp As String
Dim NieuwPDF As String
Dim nummer As String

sFolderPathPDFtemp = "C:\tmp\" 'temporary folder for putting the pdf-file

'Check if folder exists and save file
    If Dir(sFolderPathPDFtemp, vbDirectory) <> vbNullString Then 'save file if folder exists
       
            NieuwPDF = sFolderPathPDFtemp & Range("D11").Value & " " & Range("M7").Value
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=NieuwPDF
            'MsgBox "PDF bestand opgeslagen", vbInformation, "opslaan"
           
    Else 'make folder and save file
            MkDir sFolderPathPDFtemp
            NieuwPDF = sFolderPathPDFtemp & Range("D11").Value & " " & Range("M7").Value
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=NieuwPDF
            'MsgBox "map aangemaakt en bestand opgeslagen", vbInformation, "Opslaan"
    End If

'get outlook ready
    Set outlookApp = New Outlook.Application
    Set myMail = outlookApp.CreateItem(olMailItem)

customer = Range("M7").Value 'get client
mailRange = Range("Klantenlijst!A4:G500") 'customer listing
nummer = Range("D11").Value 'search factuurnummer / invoice number

'get mailadres from customer listing
    to_emails = Application.WorksheetFunction.VLookup(customer, mailRange, 7, False)
 
'check if file is on drive
'   If FileOrDirExists(sFolderPathPDFtemp) Then
'       MsgBox sPath & " exists!"
'   Else
'       MsgBox sPath & " does not exist."
'   End If
    
source_file = NieuwPDF & ".pdf"

    
    myMail.To = to_emails
    myMail.Subject = "Factuur " & nummer
    
    myMail.Body = "Beste," & vbNewLine & vbNewLine & "Gelieve in bijlage de voor u bestemde documenten te vinden." _
    & vbNewLine & vbNewLine & "mvg" & vbNewLine & vbNewLine & "Lavreys Chris" & vbNewLine & vbNewLine & "Algemene technieken Lavreys" _
    & vbNewLine & vbNewLine & "Kerkstraat 6/5" & vbNewLine & "3940 Eksel" & vbNewLine & vbNewLine & "GSM: 0491/520313" _
    & vbNewLine & vbNewLine & "BTW: BE0771.195.431"

    myMail.Attachments.Add source_file
    myMail.Attachments.Add "C:\tmp\Verkoopsvoorwaarden ATL.pdf"
    
    myMail.Display

    Kill source_file
    
End Sub

 

[GWteB]

You are welcome and thanks for the follow-up