All of my documents are stored on the OneDrive, so when I run a macro in Excel, it's not recognizing the path.
I have an Excel file with the path in one column to some pdf documents I'm attaching to an email. It's all working on my computer that is not connected to the OneDrive, but of course as soon as I try it on the computer where all files are saved to the OneDrive, path is not found. How can I pick-up my hard drive part versus the OneDrive path? I've looked for macros on-line already composed, but they are just not helping me out here.
Find hard drive path versus OneDrive
-
- Administrator
- Posts: 78232
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Find hard drive path versus OneDrive
Hi Hans,
The path is typed into one of the cells in the workbook. So, the path looks like
C:\Users\gailb\OneDrive\Desktop\Email Attachments
where the hard drive path is
C:\Users\gailb\Desktop\Email Attachments
Since the code does not see the right path, the .Attachments does not pickup anything.
The path is typed into one of the cells in the workbook. So, the path looks like
C:\Users\gailb\OneDrive\Desktop\Email Attachments
where the hard drive path is
C:\Users\gailb\Desktop\Email Attachments
Code: Select all
Sub Mail_Attachment_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim LastRow As Long: LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Dim i As Long
On Error Resume Next
For i = 2 To LastRow
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Sheet1.Range("B" & i).Value2
.CC = ""
.BCC = ""
.Subject = "Course Certificate"
.Body = "Hello " & Sheet1.Range("A" & i).Value2 & ","
.Attachments.Add Sheet1.Range("C" & i).Value2
.Send
End With
Next i
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
-
- Administrator
- Posts: 78232
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Find hard drive path versus OneDrive
It contains the path string
-
- Administrator
- Posts: 78232
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Find hard drive path versus OneDrive
I don't understand. If column C just contains a path such as C:\Users\gailb\OneDrive\Desktop\Email Attachments, the line
.Attachments.Add Sheet1.Range("C" & i).Value2
will fail since you specify a folder, not a file.
.Attachments.Add Sheet1.Range("C" & i).Value2
will fail since you specify a folder, not a file.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Find hard drive path versus OneDrive
Sorry about that. It actually has the file name in that path. I failed to post it.
So, it would be, C:\Users\gailb\OneDrive\Desktop\Email Attachments\Student1.pdf
So, it would be, C:\Users\gailb\OneDrive\Desktop\Email Attachments\Student1.pdf
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Find hard drive path versus OneDrive
Hi Hans,
I was really messing this up. After your comment about missing the file name, I've included all of the files names in the path and all is working well. Thanks for getting me on the right track
I was really messing this up. After your comment about missing the file name, I've included all of the files names in the path and all is working well. Thanks for getting me on the right track
-
- Administrator
- Posts: 78232
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands