Email attachment that is listed in text field

[Gina Maylone]

Hello! I need to send an eBlast to many customers with an attachment (the same attachment for everyone). First the user will find the file they want to attach, and that file name is stored in a field on my form called "Attachment". There's a message body and subject field as well for customization. When I go to attach the file, I get an error, because there aren't quotes around the file name, I've attempted to add quotes and get nowhere obviously. It works fine if I spell out the filepath, but I don't want to do that, I want to choose the file. The error message is "object doesn't support this property or method", here's the code:

Private Sub Command13_Click()  'Sub SendMessages(Optional AttachmentPath)
 
  Dim MyDB As dao.Database
  Dim MyRS As dao.Recordset
  Dim objOutlook As Outlook.Application
  Dim objOutlookMsg As Outlook.MailItem
  Dim objOutlookRecip As Outlook.Recipient
  Dim objOutlookAttach As Outlook.attachment
  Dim TheAddress As String


 
  Set MyDB = CurrentDb
  Set MyRS = MyDB.OpenRecordset("customeremails")
  MyRS.MoveFirst
 
  ' Create the Outlook session.
  Set objOutlook = CreateObject("Outlook.Application")
 
  Do Until MyRS.EOF
  ' Create the e-mail message.
  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  TheAddress = MyRS![Email]
 
     With objOutlookMsg
        ' Add the To recipients to the e-mail message.
        Set objOutlookRecip = .Recipients.Add(TheAddress)
        objOutlookRecip.Type = olTo
 
 
        ' Set the Subject, the Body, and the Importance of the e-mail message.
        .Subject = Me.Subject
        .Body = Me.message
        
        'Add the attachment to the e-mail message.
        If Not IsMissing(Me.attachment) Then
          .Attachments.Add(Me.attachment)
        End If
 
        ' Resolve the name of each Recipient.
        For Each objOutlookRecip In .Recipients
           objOutlookRecip.Resolve
           If Not objOutlookRecip.Resolve Then
             objOutlookMsg.Display
           End If
        Next
        .Send
      End With
      MyRS.MoveNext
   Loop
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

Any thoughts for how I can refer to the text field on my form? Thanks in advance you guys! Happy weekend!

[ranman256]

I send stuff without quote all the time.
you MUST have the full filepath,otherwise its not a file.

I hope your me.attachment is NOT an access attachment, but rather a textbox named attachment and in it is the full filepath.

[Gicu]

If Me.Attachment is a textbox holding the full path to the file including the extension (C:\MyDbFolder.CustomerSurvey.pdf) then this code should look like this:

 'Add the attachment to the e-mail message.
        If Not IsNull(Me.attachment) Then
          .Attachments.Add(Me.attachment)
        End If

[Gina Maylone]

It is a textbox and the full filepath. Thanks ranman!

[Gina Maylone]

I pasted this and it's now working! Thank you Gicu!

[Gicu]

You're very welcome Gina!
Cheers,

[Micron]

Maybe research IsMissing function so you know where it can be used? AFAIK, it doesn't apply in that code.