Sending out a report with a specific File name

[June7]

Did you try the revised code?

[Thompyt]

I am using this: DoCmd.OutputTo 3, "By Field Report", acFormatPDF, "C:\users\Username\Documents\TempA\By Field - " & Me.Controls(Me.[Field Name]) & " - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0

When I do this for the Me.Controls(Me.[Field Name]) is get an error saying the dB cannot find the field "Instrumentation" or whatever I select from the Select Field form.

[June7]

I don't see a field "Instrumentation" in the query in post 13. The code expects these fields to be in the form's RecordSource.

Maybe I don't understand what you are really trying to do.

[Thompyt]

"Instrumentation" is one of the Field Names.

I have a form with a combo box named "My Field" the record source is from a table named Field. This sets up the [By Field] query with Like "[Forms]![Select Field].[My Field] & "*" " in the criteria of the [Field Name] column. Then the [By Field Report] opens and displays the selected field.

What I am having issues with -

If I put in the Me.Controls(Me.[Field Name]) it comes up with the error stating that "Microsoft Access cannot find the field (whatever field is selected) referred to in your expression".
If I put in the Me.Controls(Me.[My Field Name]) it comes up with the error stating "Microsoft Access cannot find the field '|1' referred to in your expression".

DoCmd.OutputTo 3, "By Field Report", acFormatPDF, "C:\users\username\Documents\TempA\By Field - " & Me.Controls(Me.[Field_Name]) & " - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0

[June7]

Since the code is referencing Controls collection, needs to be a control named "Instrumentation" bound to field "Instrumentation". Since Instrumentation is not in the posted query, how do you expect to pull values from that field?

[Thompyt]

DoCmd.OutputTo 3, "By Field Report", acFormatPDF, "C:\users\wayne.p.thompson\Documents\TempA\By Field - " & Me.[Field Name] & " - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0

Fixed - changed Me.Controls(Me.[Field Name]) to Me.[Field Name] Works fine now.

[Thompyt]

Next step is to pick up this in an email from the stored location, then delete it.

[Thompyt]

This is what I have developed so far and I added it in the EMAIL VBA vice the Sendobject.

Attachments.Add "C:\users\username\Documents\TempA\Like " * ""

With this I get a mismatch error 13.

If I use Like [*]* or ? o get and Expected End of Statement Error.

[June7]

Certainly would not use LIKE operator.

Attachments.Add "C:\users\username\Documents\TempA\*.*"

However, I have no idea if wildcard will work. Might have to add each attachment individually.

[Thompyt]

wildcard doesn't work *.* still get an error 424 Object Required.

Tried this:

Attachments.Add "C:\users\username\Documents\TempA\By Field - " & Me.[Field Name] & " - " & Format(Date, "dd mmm yyyy") & ".pdf"

Comes out the same. How would I specify the object?

[June7]

Sorry, just noticed you said "SendObject", cannot attach files with SendObject method.

Would have to open and manipulate an Outlook mail object. Review: https://www.accessforums.net/access/...ect-17713.html

[Thompyt]

Dang, making it even harder. LOL, Thanks June, I'll see what i can do. I'll definitally give a hollar.

[Thompyt]

I tried the following:

Private Sub sbSendMessage_Click()
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
Dim strSubject, strBody, strAddresses, strFile As String
 On Error GoTo ErrorMsgs
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
 If Not IsNull(MyField) Then

'Signature Block
    strBody = strBody & vbCrLf & vbCrLf & vbCrLf
    strBody = strBody & "V/R" & vbCrLf
    strBody = strBody & "Name" & vbCrLf & vbCrLf
    strBody = strBody & "Command"& vbCrLf
    strBody = strBody & "Division" & vbCrLf
    strBody = strBody & "Address 1" & vbCrLf
    strBody = strBody & "Address 2" & vbCrLf
    strBody = strBody & "Phone Number" & vbCrLf
    strBody = strBody & "Email address" & vbCrLf
    strSubject = "By Field Report - " & ![Field Name] & " - " & Format(Now, "dd mmm yyyy")
' Make file       
DoCmd.OutputTo 3, "By Field Report", acFormatPDF, "C:\users\wayne.p.thompson\Documents\TempA\By Field - " & Me.[Field Name] & " - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0
'Close form
DoCmd.Close acForm, "By Field"

 '.Subject = "This is an Automation test with Microsoft Outlook"
 '.Body = "Last test." & vbCrLf & vbCrLf

 If Not IsMissing(AttachmentPath) Then
 Set objOutlookAttach = .Attachments.Add("C:\users\wayne.p.thompson\Documents\TempA\By Field - " & Me.[Field Name] & " - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0)
  End If
  End If
 End With
 Set objOutlookMsg = Nothing
 Set objOutlook = Nothing
 Set objOutlookRecip = Nothing
 Set objOutlookAttach = Nothing
 Exit Sub
ErrorMsgs:
 If Err.Number = "287" Then
 MsgBox "You clicked No to the Outlook security warning. " & _
 "Rerun the procedure and click Yes to access e-mail " & _
 "addresses to send your message. "
 Else
 MsgBox Err.Number & " " & Err.Description
End If
End Sub

I get a "438 Object doesn't support this property or method." error

[June7]

On what line??

In my code, I don't declare and set an attachment object.

With objOutlookMsg
...
.Attachments.add ("file path and name here")
.DeleteAfterSubmit = True
.Send
End With

[Thompyt]

June,
Corrected with your script:

Private Sub sbSendMessage_Click()
 On Error GoTo ErrorMsgs
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim strSubject, strBody, strAddresses, strFile As String
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
 If Not IsNull(MyField) Then
    strBody = strBody & vbCrLf & vbCrLf & vbCrLf
    strBody = strBody & "V/R" & vbCrLf
    strBody = strBody & "Name" & vbCrLf & vbCrLf
    strBody = strBody & "Command" & vbCrLf
    strBody = strBody & "Division" & vbCrLf
    strBody = strBody & "Addy" & vbCrLf
    strBody = strBody & "Zip" & vbCrLf
    strBody = strBody & 'Phone" & vbCrLf
    strBody = strBody & "email" & vbCrLf
    strSubject = "By Field Report - " & ![Field Name] & " - " & Format(Now, "dd mmm yyyy")
     DoCmd.OutputTo 3, "By Field Report", acFormatPDF, "C:\users\wayne.p.thompson\Documents\TempA\By Field - " & Me.[Field Name] & " - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0
     DoCmd.Close acForm, "By Field"
    Attachments.Add ("C:\users\wayne.p.thompson\Documents\TempA\By Field - " & Me.[Field Name] & " - " & Format(Date, "dd mmm yyyy") & ".pdf")
   .DeleteAfterSubmit = True
   .Send
    End If
 End With
 Set objOutlookMsg = Nothing
 Set objOutlook = Nothing
 Set objOutlookAttach = Nothing
 Exit Sub
ErrorMsgs:
 If Err.Number = "287" Then
 MsgBox "You clicked No to the Outlook security warning. " & _
 "Rerun the procedure and click Yes to access e-mail " & _
 "addresses to send your message."
 Else
 MsgBox Err.Number & " " & Err.Description
End If
End Sub

I still get the "438 Object doesn't support this property or method" as soon as I select the button to send email.