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]

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?

Yes, this is my actual code. Is there a possibility that Onedrive synchronization might be causing this??

 

[arorsu1]

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?

There is some strange behavior I noticed with the script that it works sometimes

 

[RoryA]

Is there a possibility that Onedrive synchronization might be causing this

Perhaps, although it seems unlikely to me given that you aren't referring to the onedrive document anywhere in the code.