VBA won't get file on D drive

cLavreys

New Member
Joined
Oct 11, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,473
Office Version
  1. 2013
Platform
  1. Windows
What does the code of the used FileOrDirExists function looks like?
 

cLavreys

New Member
Joined
Oct 11, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,473
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,473
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

cLavreys

New Member
Joined
Oct 11, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,473
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 
Solution

cLavreys

New Member
Joined
Oct 11, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,473
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the follow-up (y)
 

Forum statistics

Threads
1,148,369
Messages
5,746,295
Members
424,006
Latest member
Metal_warrior

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
Top