Outlook File Attachment using VBA

[arorsu1]

I'm encountering an issue with my VBA code for sending Outlook emails with attachments. The code is intended to generate a file path and attach the file to an Outlook email. However, I'm consistently getting an error stating that the file doesn't exist or has a bad file or path name. I can confirm that the file path is correctly generated and the file exists at that location. What could be causing this issue, and how can I resolve it? Here is a snippet of the relevant code:

View attachment 102606

Private Sub CommandButton1_Click()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim OutlookMail2 As Object
    Dim WordDocument As Document
    Dim FilePath1 As String
    Dim FilePath2 As String
    Dim emailBody As String
    Dim userInput As String
    Dim userResponse As VbMsgBoxResult
    
    ' Create an instance of Outlook
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    Set OutlookMail2 = OutlookApp.CreateItem(0)
    
    Randomize
    
    Dim random4DigitNumber As String
    random4DigitNumber = Format(Int((9999 * Rnd) + 100), "0000")
    
    Dim ticketNumber As String
    Dim parts() As String
    parts = Split(TextBox1.Value, " ")
        ' Check if there's at least one part
    If UBound(parts) >= 0 Then
        ' Get the first value
        
        ticketNumber = Trim(parts(0))
        
        ' Display the first value
        MsgBox "First Value: " & ticketNumber
    Else
        MsgBox "No parts found in the string."
    End If
    
    ' Extract numeric part from ticketNumber
Dim numericPart As String
numericPart = VBA.Strings.Mid(ticketNumber, InStr(ticketNumber, "_") + 1)

' Use only the numeric part
ticketNumber = Trim(numericPart)
    'ticketNumber = Trim(TextBox1.Value.Split("(")) ' Trim leading and trailing spaces
 
   ' Set the file path for the exported document
FilePath1 = Environ("USERPROFILE") & "\Documents\PAP_" & ticketNumber & "_" & random4DigitNumber & ".docm"
FilePath2 = Environ("USERPROFILE") & "\Documents\PAP_" & ticketNumber & "_" & random4DigitNumber & ".pdf"

    
    If ticketNumber = "" Then
        MsgBox "No value provided. Execution halted.", vbExclamation
        Exit Sub ' Exit the code
    End If
    
    userInput = InputBox("If you want to add something in the email body", "Email Body")
    
    emailBody = "Hello Team,<br><br>" & _
                 "Please find attached PAP for the A+ ticket (<b>" & ticketNumber & "</b>).<br>" & _
                 userInput & "<br><br>Regards,<br>" & Application.UserName
    
    userResponse = MsgBox("Are you sure you want to send the file?", vbOKCancel)
    
    If userResponse <> vbOK Then
        Exit Sub
    End If
    
        ' Save the active document with a new name
ActiveDocument.SaveAs2 FilePath1, FileFormat:=wdFormatXMLDocumentMacroEnabled

MsgBox Dir(FilePath1)
    
    ' Configure the email
    With OutlookMail
        .To = "xyz.abc.com"
        .Cc = "xyz.gef.com"
        .Subject = "Preventive Action Plan - " & ticketNumber
        .HTMLBody = emailBody
        .Attachments.Add FilePath1
        ' Display the email for the user to review (optional)
        .Display
        
        ' Send the email
        '.Send
    End With

End Sub

 

[arorsu1]

The code works without error here, so I don't think there's anything inherently wrong with it. I assume it's definitely the .Attachments.Add line causing the error?

Yes, you are getting an error at this line when you reference the FilePath

 

[RoryA]

I'm not actually. What is the error number?

 

[arorsu1]

I'm not actually. What is the error number?

 

[Skybluekid]

I agree @RoryA .

@arorsu1 are you able to step through the code and post a screenshot of where the code fails

 

[arorsu1]

I agree @RoryA.

@arorsu1 are you able to step through the code and post a screenshot of where the code fails

When I try to attach the file using FilePath1 ".Attachments.Add FilePath1"

 

[RoryA]

What did this part show:

MsgBox Dir(FilePath1)

The name of the file?

 

[arorsu1]

What did this part show:

MsgBox Dir(FilePath1)

The name of the file?

Correct

 

[Skybluekid]

You said that the document is stored in Documents folder on One Drive,

the path that you post does not reference One Drive in the path:
"C: \Users\<user-id>\Documents\PAP_2020202122_2354.docm"

I have done a test and I got the path of a document, which is stored on my One Drive and it does mention it in the path
"C:\Users\<user-id>\OneDrive\Excel Files\XXXXX.xlsx". I got this path from File Explorer. Navigate to the file on File Explorer, copy the path and compare it to the path that you have in the FilePath1

 

[RoryA]

Correct

Then I am extremely confused. If Dir can find it, there is no reason it should fail later, unless something is moving/deleting it in between (but you said you could see it). This is your actual code, yes?

 

[arorsu1]

You said that the document is stored in the Documents folder on One Drive,
View attachment 102677

the path that you post does not reference One Drive in the path:
"C: \Users\<user-id>\Documents\PAP_2020202122_2354.docm"

I have done a test and I got the path of a document, which is stored on my One Drive and it does mention it in the path
"C:\Users\<user-id>\OneDrive\Excel Files\XXXXX.xlsx". I got this path from File Explorer. Navigate to the file on File Explorer, copy the path, and compare it to the path that you have in the FilePath1

The active document is stored in OneDrive, but the path where I am saving the file is in the local directory