Emailing based upon a query 2

[Thanyellin]

I have built this routine and everything works except for the attachment. Any suggestions?

Private Sub Command22_Click()


    Dim otk As Outlook.Application
    Dim eml As Outlook.MailItem
    Dim rs As ADODB.Recordset
    

    ' Open your table and build your distribution list
    Set rs = New ADODB.Recordset
    

    With rs
        .Open "Student_Data1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
        .MoveFirst
        Do While Not .EOF
            Set otk = CreateObject("Outlook.Application")
            Set eml = otk.CreateItem(olMailItem)
                eml.To = .Fields("Email")
                eml.Subject = .Fields("Subject")
                eml.Body = .Fields("Body")
                [COLOR=red]eml.Attachments.Add = .Fields("Attachment")[/color]
                
                eml.Display
            
            .MoveNext
        Loop
        .Close
    End With
    otk.Quit
    Set otk = Nothing
End Sub

 

[PHV]

Have you tried to replace this:
eml.Attachments.Add = .Fields("Attachment")
By this ?
eml.Attachments.Add .Fields("Attachment")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Thanyellin]

I made the change you recommented and this is the result.

eml.attachments.[COLOR=red]Add[/color].fields("attachment")

Compile error: Argument not optional

 

[PHV]

eml.attachments.Add.fields("attachment")
This is not what I suggested.

eml.Attachments.Add[highlight] [/highlight].Fields("Attachment")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Thanyellin]

I made the correct change this time and this is the result I recieved:
Run time error "436" Object doesn't support this property or method

In the debugger the entire line is highlighted.

I appreciate your help on this.

 

[PHV]

And this ?
eml.Attachments.Add .Fields("Attachment"), 1, "Test"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Thanyellin]

The error now is:

Run time error "438"
Object doesn't support this property or method

 

[PJStephenson]

might be a dumb question but does .Fields("Attachment") contain a string that is the full path and filename of the file you want to send or does it contain an OLE object? If it is the former you could test if the file exists using the .FileExists method of the FileSystemObject, if the latter then you may need to export it from the database before sending it.
HTH
Peter

 

[PHV]

And something like this ?

Private Sub Command22_Click()
  Dim otk As Outlook.Application
  Dim eml As Outlook.MailItem
  Dim rs As ADODB.Recordset
  Dim myAttachments As Outlook.Attachments
  Set otk = CreateObject("Outlook.Application")
  ' Open your table and build your distribution list
  Set rs = New ADODB.Recordset
  With rs
    .Open "Student_Data1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
    .MoveFirst
    Do While Not .EOF
      Set eml = otk.CreateItem(olMailItem)
      eml.To = .Fields("Email")
      eml.Subject = .Fields("Subject")
      eml.Body = .Fields("Body")
      Set myAttachments = eml.Attachments
      myAttachments.Add .Fields("Attachment"), 1, "Test"
      eml.Display
      .MoveNext
    Loop
    .Close
  End With
  otk.Quit
  Set otk = Nothing
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Thanyellin]

The field contains a string that is the full path and file name. it is found in the same table "studentdata1" that the other fields (Email,subject, and body) are found in. What I don't understand is why the other fields work flawlessly if I rem this line. Yet with this line in it hangs.

I appreciate your help and input on this one.

 

[Thanyellin]

PHV

I ran your modification and recieved the "438" error

Any ideas?

 

[Thanyellin]

PHV

The error occured at this line:

myAttachments.Add .Fields("Attachment"), 1, "Test"

 

[PHV]

Can you manually add attachments when you're in Outlook ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Thanyellin]

PHV
Yes I can add attachments manually

The following hard coded version executes fine. But when I refer to the same information from the table it doesn't work.

Private Sub Command22_Click()
Dim otk As Outlook.Application
  Dim eml As Outlook.MailItem
  Dim rs As ADODB.Recordset
  Dim myAttachments As Outlook.Attachments
  Set otk = CreateObject("Outlook.Application")
  ' Open your table and build your distribution list
  Set rs = New ADODB.Recordset
  With rs
    .Open "Student_Data1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
    .MoveFirst
    Do While Not .EOF
      Set eml = otk.CreateItem(olMailItem)
      eml.To = .Fields("Email")
      eml.Subject = .Fields("Subject")
      eml.Body = .Fields("Body")
      Set myAttachments = eml.Attachments
      myAttachments.Add "C:\mailing\Test.doc"
      eml.Display
      .MoveNext
    Loop
    .Close
  End With
  otk.Quit
  Set otk = Nothing
End Sub

 

[PHV]

What is the type of the field Attachment in Student_Data1 ?
What is displaying if you add this line of code:
MsgBox "'" & .Fields("Attachment") & "'" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PJStephenson]

at the point it errors what is the value of .Fields("Attachment")? you can find out by typing ?rs.Fields("Attachment") into the debug window. also have you tried .Fields("Attachment").value ? I know it's the default but it might have an effect. also have you tried the FileSystemObject? you will need to make sure that 'Microsoft scripting runtime' is ticked in the references.

dim fso as object
set fso = new filesystemobject
if fso.FileExists(.Fields("Attachment").value) then
eml.Attachments.add .Fields("Attachment").value
end if

Peter

 

[Thanyellin]

By running the following code:

Private Sub Command22_Click()
Dim otk As Outlook.Application
  Dim eml As Outlook.MailItem
  Dim rs As ADODB.Recordset
  Dim myAttachments As Outlook.Attachments
  Set otk = CreateObject("Outlook.Application")
  ' Open your table and build your distribution list
  Set rs = New ADODB.Recordset
  With rs
    .Open "Student_Data1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
    .MoveFirst
    Do While Not .EOF
      Set eml = otk.CreateItem(olMailItem)
      eml.To = .Fields("Email")
      eml.Subject = .Fields("Subject")
      eml.Body = .Fields("Body")
      MsgBox "'" & .Fields("Attachment") & "'"
      Set myAttachments = eml.Attachments
      myAttachments.Add .Fields("Attachment")
      
      eml.Display
      .MoveNext
    Loop
    .Close
  End With
  otk.Quit
  Set otk = Nothing
End Sub

The msg box value is 'C:\mailing\test.doc'

The field is a text field with a lenght of 100

 

[Thanyellin]

Hurray,

Adding the .value seems to have worked!

Thank you both for your help

Stars for you!