CDO Mail sending attachment via location field

[mousemat]

I have code which work when the attachment is sets C:\AccessFiles\some.pdf

However. I want to send a file that has been referenced in a text field within the table. The code gives a runtime error 438 but hovering over the line it stops at, shows the location and the file that I want to send out via the email. It stops here: .AddAttachment = rs.Fields("Formlocation")

What am I missing?

Public Sub SendEmail()

    Dim rs As DAO.Recordset

    Dim imsg As Object
    Dim iconf As Object
    Dim flds As Object
    Dim schema As String

    Set imsg = CreateObject("CDO.Message")
    Set iconf = CreateObject("CDO.Configuration")
    Set flds = iconf.Fields

    ' send one copy with SMTP server (with autentication)
    schema = "http://schemas.microsoft.com/cdo/configuration/"
    flds.Item(schema & "sendusing") = cdoSendUsingPort
    flds.Item(schema & "smtpserver") = "servername"
    flds.Item(schema & "smtpserverport") = 25
    flds.Item(schema & "smtpauthenticate") = cdoBasic
    flds.Item(schema & "sendusername") = "email address"
    flds.Item(schema & "sendpassword") = "password"
    flds.Item(schema & "smtpusessl") = False
    flds.Update
    
    
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
'Open the file for reading
Set f = fso.OpenTextFile("c:\AccessTextFiles\CraftFayreBookingConfirmation.txt", ForReading)
'The ReadAll method reads the entire file into the variable BodyText
BodyText = f.ReadAll
'Close the file
f.Close
Set f = Nothing
Set fso = Nothing

'this is ithe original line'Set rs = CurrentDb.OpenRecordset("SELECT * FROM TestTable") 'This is where the records come from, most likely a query run on demand


Set db = CurrentDb
    Dim strSQL As String
    Dim filename As String
  
    
    
'This line produces the recordset, People who have completed the booking process, sent their form and paymnt.

strSQL = "SELECT tblBookings.BookingID, tblParticipants.PName, tblParticipants.PEmail, tblEvents.EventName, " _
& "tblEvents.EventType, tblEvents.EventDate, tblEvents.FormLocation, tblEvents.EventID " _
& "FROM qryMaxBookingID INNER JOIN ((tblBookings INNER JOIN tblParticipants ON tblBookings.ParticipantID = tblParticipants.ParticipantID) " _
& "INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID) ON qryMaxBookingID.MaxOfBookingID = tblBookings.BookingID;"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
filename = rs.Fields("FormLocation")
 
 
'This section loops through the recordset created above and using the txtfile above, creates the email and sends it
'to the email addresses within the recordset.
'These are the field placeholders <<sometext>> which correspond to the text file

If Not (rs.EOF And rs.BOF) Then
    Do Until rs.EOF = True
        'On Error Resume Next
        With imsg
            .To = rs.Fields("PEmail")
            .BCC = "emailaddress"
            .From = """name"" <emailaddress>"
            .Subject = rs.Fields("EventName")
            .AddAttachment = rs.Fields("Formlocation")
            .TextBody = BodyText
            .TextBody = Replace(.TextBody, "<<PName>>", rs!PName)
            .TextBody = Replace(.TextBody, "<<EventName>>", rs!EventName)
            .TextBody = Replace(.TextBody, "<<EventDate>>", rs!EventDate)
            '.TextBody = Replace(.TextBody, "<<AmountPaid>>", "£" & rs!AmountPaid)
            Set .Configuration = iconf
            .Send
        End With
        
        rs.MoveNext
    Loop
  
    Set iconf = Nothing
    Set imsg = Nothing
    Set flds = Nothing
  
End If
MsgBox "Finished looping through records, all emails have been sent."
rs.Close 'Close the recordset
End Sub

 

[Gasman]

No = allowed?

Attachments.Add method (Outlook)

Office VBA reference topic

docs.microsoft.com

 

[onur_can]

You already assigned FormLocation to a variable in the upper line, change the line that gives the error to

 .AddAttachment = filename

 

[mousemat]

.AddAttachment = filename
gives the same error

 

[cheekybuddha]

Is filename/rs.Fields("FormLocation") a full and valid path to the file?

 

[onur_can]

Attachments.Add filename

 

[mousemat]

This is the path as found detailed by rs.fields("FormLocation") C:\AccessTextFiles\ChristmasFayre2020BookingForm.pdf

 

[onur_can]

I assume that the table field you assigned to the filename variable is not empty.

 

[cheekybuddha]

.Attachments.Add filename

I don't think this collection object has this method in CDO

 

[onur_can]

Did the code in the 6th message fail again!

 

[cheekybuddha]

Test like this:

' ...
            If Len(Dir(rs.Fields("Formlocation") & vbNullString)) Then
              .AddAttachment = rs.Fields("Formlocation")
            Else
              MsgBox "File: '" & rs.Fields("Formlocation") & "' does not exist"
            End If
' ...

 

[Gasman]

Test like this:

' ...
            If Len(Dir(rs.Fields("Formlocation") & vbNullString)) Then
              .AddAttachment = rs.Fields("Formlocation")
            Else
              MsgBox "File: '" & rs.Fields("Formlocation") & "' does not exist"
            End If
' ...

Addattachment is a method, not a property?

 

[mousemat]

No joy with the Cheekybuddha

 

[cheekybuddha]

No joy with the Cheekybuddha

Be helpful if you told us what actually did happen! Remember we can't see your screen!

 

[cheekybuddha]

Addattachment is a method, not a property?

Yes

 

[Gasman]

Yes

That was a statement not a question, just trying to query why people are using the = sign.?

 

[cheekybuddha]

Good spot BatGasman!

 

[mousemat]

I get the same error as highlighted at the start of the thread

 

[mousemat]

I get this error now with the code in answer 6

Again though hovering over the line shows the path and the file etc

 

[Gasman]

You have left the prefix full stop off now.