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

 

[Skybluekid]

Does it save correctly to the desired location?

 

[arorsu1]

Does it save correctly to the desired location?

Yes, I can see the file there.

 

[Skybluekid]

Go to the folder using File Explorer and copy the path as text. Compare the two paths are the same.
Have you tried adding the PDF, File Path3, to see it that attaches?

 

[arorsu1]

Go to the folder using File Explorer and copy the path as text. Compare the two paths are the same.
Have you tried adding the PDF, File Path3, to see it that attaches?

Allow me to clarify my objective. I aim to append the active document as an attachment to an Outlook email generated when users click a button. To modify the file's name, I save it in a distinct location and utilize that path for attaching the file.

The active document is stored in Onedrive.

 

[Skybluekid]

I understand what your objctive is. What I was establising was that the file paths for saving and attaching are the same. Looking at the VBA it seems that the code should have no issues with getting the document, but some reason the file path for the attachment can not find the file that has been saved.

I would put the full path name, copied from File Explorer, into the path of the attachment, to see if that finds the file.

 

[arorsu1]

I understand what your objctive is. What I was establising was that the file paths for saving and attaching are the same. Looking at the VBA it seems that the code should have no issues with getting the document, but some reason the file path for the attachment can not find the file that has been saved.

I would put the full path name, copied from File Explorer, into the path of the attachment, to see if that finds the file.

You'll encounter the same error if you attempt to attach the active document without saving it elsewhere.

 

[Skybluekid]

Are you able to post the file path that FilePath1 var is? (obviously blot out any sensitive information)

 

[arorsu1]

Are you able to post the file path that FilePath1 var is? (obviously, blot out any sensitive information)

Sure. It is "C: \Users\<user-id>\Documents\PAP_2020202122_2354.docm"

 

[RoryA]

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?