How use field data entry as file name when doing a SendObject?

Dim myName as String
myName = nz(Me.TextBoxName,"Empty")
If myName = "Empty" then
Msgbox "You must select a name", vbokonly, "Missing File Name"
Me.TextboxName.setfocus
Exit sub
End If

'send it
'fill rest of address
myName = "C:\My Documents\" & myName

DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLS, myName

To dannywareham,

Before I try this, will this actually create the Excel file that will be named the same as the entry in the form field, and, will it e-mail it to the recipient in have in the macro?

This will output a database object (query/table etc) as a spreadsheet to a specified location.

Do you want to attach an existing excel spreadhseet and send that...?

Sub sndOutlookAtt()
    Dim i As Integer
    Dim appOutl As Outlook.Application
    Dim maiMail As MailItem
    Dim recMEssage As Recipient
    Dim myFile as String
    'Dim ns As Outlook.NameSpace
   
myName = nz(Me.TextBoxName,"Empty")
If myName = "Empty" then
Msgbox "You must select a name", vbokonly, "Missing File Name"
Me.TextboxName.setfocus
Exit sub
End If

    Set appOutl = New Outlook.Application
    'Set ns = appOutl.GetNamespace("MAPI")
    'provide the profilename, if no password leave blank
    'ns.Logon "Danny Wareham", , False, True
    Set maiMail = appOutl.CreateItem(olMailItem)
    With maiMail
        'Optional test against addressbook is ' boorecip = .Recipients.ResolveAll
        'You might want to insert a conditinal here if boorecip=False
        .Subject = "Testing mail by automation"
        'get the attachments file name and path from the array and attach it
'if are going to display it to address it don't need the recipients.add
'you will need this line for each recipient, unless using a group                
'.Recipients.Add ("Name")
                .Attachments.Add ("C:\my documents\" & myFile & ".xls")             '<------  change address here.
         
        'You do not need .body if you are going to type the msg .Body = "Created by vba code"
   
        'Using .Send instead of .Display will send the msg without displaying it
        .Display
    End With
    Set appOutl = Nothing
    Set maiMail = Nothing
    Set recMEssage = Nothing
End Sub

To dannywareham...

I'm sorry but I don't understand what I should do with the code you wrote... can you tell me?

Here is my your code with my field name in place...

Sub sndOutlookAtt()
    Dim i As Integer
    Dim appOutl As Outlook.Application
    Dim maiMail As MailItem
    Dim recMEssage As Recipient
    Dim myFile As String
    'Dim ns As Outlook.NameSpace
   
myName = Nz(Me.txtDate, "Empty")
If myName = "Empty" Then
MsgBox "You must enter a date", vbOKOnly, "Missing File Name"
Me.txtDate.SetFocus
Exit Sub
End If

    Set appOutl = New Outlook.Application
    'Set ns = appOutl.GetNamespace("MAPI")
    'provide the profilename, if no password leave blank
    'ns.Logon "Danny Wareham", , False, True
    Set maiMail = appOutl.CreateItem(olMailItem)
    With maiMail
        'Optional test against addressbook is ' boorecip = .Recipients.ResolveAll
        'You might want to insert a conditinal here if boorecip=False
        .Subject = "MY LABOR LOG FILE - " & [Forms]![frmRequestQuery]![txtDate]
        'get the attachments file name and path from the array and attach it
'if are going to display it to address it don't need the recipients.add
'you will need this line for each recipient, unless using a group
'.Recipients.Add ("Name")
                .Attachments.Add ("C:\my documents\" & myFile & ".xls")             '<------  change address here.
         
        'You do not need .body if you are going to type the msg .Body = "Created by vba code"
   
        'Using .Send instead of .Display will send the msg without displaying it
        .Display
    End With
    Set appOutl = Nothing
    Set maiMail = Nothing
    Set recMEssage = Nothing
End Sub
End Sub

On your form, create a command button.
Behind teh command button (on the OnClick event, found in the Events tab in properties), add this code:

    Dim i As Integer
    Dim appOutl As Outlook.Application
    Dim maiMail As MailItem
    Dim recMEssage As Recipient
    Dim myFile As String
    'Dim ns As Outlook.NameSpace
   
myName = Nz(Me.txtDate, "Empty")
If myName = "Empty" Then
MsgBox "You must enter a date", vbOKOnly, "Missing File Name"
Me.txtDate.SetFocus
Exit Sub
End If

    Set appOutl = New Outlook.Application
    'Set ns = appOutl.GetNamespace("MAPI")
    'provide the profilename, if no password leave blank
    'ns.Logon "Danny Wareham", , False, True
    Set maiMail = appOutl.CreateItem(olMailItem)
    With maiMail
        'Optional test against addressbook is ' boorecip = .Recipients.ResolveAll
        'You might want to insert a conditinal here if boorecip=False
        .Subject = "MY LABOR LOG FILE - " & [Forms]![frmRequestQuery]![txtDate]
        'get the attachments file name and path from the array and attach it
'if are going to display it to address it don't need the recipients.add
'you will need this line for each recipient, unless using a group
'.Recipients.Add ("Name")
                .Attachments.Add ("C:\my documents\" & myFile & ".xls")             '<------  change address here.
         
        'You do not need .body if you are going to type the msg .Body = "Created by vba code"
   
        'Using .Send instead of .Display will send the msg without displaying it
        .Display
    End With
    Set appOutl = Nothing
    Set maiMail = Nothing
    Set recMEssage = Nothing


When you click the button, the code will:

Check the value of "txtDate". If it is null, it will stop the code and tell the user to add a file name.
If not null, it will open an Outlook mail, fetch the Excel file that matches the file name and attach it into your mail.

The location of the file is highlighted in the code.
The file name is pulled from your "txtDate" field.

I did what you said above, but when I click the button I get an error...

Compile error:  User-defined type not defined.  Seems th e be objecting to something on the 2nd line of code because its hightlighted in yellow.

???

--Steve

You need to set a reference to Outlook in TOOLS > REFERENCES in the VBA window...

:-)

Did that.  Now stuck on...

.Attachments.Add ("C:\" & myFile & ".xls")             '<------  change address here.

I set to go to C:\ directory but isn't working.

??

--Steve

What is the addess of the file...?

Change   "C:\"   to "C:\My Documents\Test folder\"  etc, depending on the file location.

I have...

.Attachments.Add ("C:\PaulExcelFiles\" & myFile & ".xls")

on that line but it still doesn't work.  I do have a folder set up named PaulExcelFiles.

if the value in the txtDate is "123456", then teh file name will be

C:\PaulExcelFiles\123456.xls

Is there a file with this name in the folder...? Or do you need to create the file first?

Actually, I am entering "5/5/05" in the field which has a format of Medium Date.  So it gets changed to 05-May-05 after entering the date in that field.  So then, the file, which will be named "05-May-05.xls" would be a new file created each day we run this thing.  Therefore, the file isn't in the folder yet.

OK.
Right.
Before you generate the mail, you need to create the file first.
Where is the file coming from? Is it a table/query withni Access?
Are you renaming an external excel file?

Is a query in the Access database named "qryLaborTranx".  There is no external Excel file.  Only the one I'm hoping this thing will create in C:\PaulExcelFiles.

OK. This makes it even easier.... (as we don't need to use the Outlook object at all)

Dim myName as String
myName = nz(Me.txtDate,"Empty")
If myName = "Empty" then
Msgbox "You must select a name", vbokonly, "Missing File Name"
Me.txtDate.setfocus
Exit sub
End If

myName = "C:\PaulExcelFiles\" & myName
'save file
DoCmd.OutputTo acOutputQuery, "qryLaborTranx", acFormatXLS, myName
'send file
DoCmd.SendObject acSendQuery, "qryLaborTranx", acFormatXLS,myName , , , "File name: " & myName, "Message Text", True




This will create your file in the folder, attach a copy into a mail and open the mail for you to add in your SendTo name.

Now error 2302.  "Paul's log file can't save the output data to the file you've selected"  Stuck at:

'save file
DoCmd.OutputTo acOutputQuery, "qryLaborTranx", acFormatXLS, myName

OK.
The problem here is the value assigned to myFile.
Check the value of myfile in a line before with

Msgbox myFile

Make sure that this location exists

I'm sorry.. where do I put the line "Msgbox myFile" ?

above the error line.

This will flash up a message box with the value of myFile...

:-)

It's my guess that you're missing a "\" from the address

It could also be that the file extension is missing. Change this line:

myName = "C:\PaulExcelFiles\" & myName

To this

myName = "C:\PaulExcelFiles\" & myName & ".xls"

I tried...

myName = "C:\PaulExcelFiles\" & myName & ".xls"

Same error message.

Is the message in teh message box the correct address to your folder and file...?

Also, does the text box have an input mask for date?
If it does then Access will stoer the date as an integer value (ie 36512 might be today).

Message box says:

Run-time error '2302':
Pauls' Log File can't save the output data to the file you've selected.

When I click Debug, I get the line of code...
DoCmd.OutputTo acOutputQuery, "qryLaborTranx", acFormatXLS, myName
highlighted in yellow.

Also, I am entering "5/5/05" (for example) in the field which has a format of Medium Date.  So it gets changed to 05-May-05 after entering the date in that field.  I do not have anything in the input mask for that field.

What does your user mesasge box say?
Add

Msgbox myFile

before this line
What does that message box say...?

Sorry, I don't understand your last comment.

Change your code to this:

Dim myName as String
myName = nz(Me.txtDate,"Empty")
If myName = "Empty" then
Msgbox "You must select a name", vbokonly, "Missing File Name"
Me.txtDate.setfocus
Exit sub
End If

myName = "C:\PaulExcelFiles\" & myName
msgbox "The value passed is: " & vblf & myFile



This will now generate a message (no error)

What does it say?

It says,

The value passed is:
[OK]

Do you have a value in txtDate before you run the code..?

If nothing appears in the message box, it isn't picking up the value...

I am entering "5/5/05" (for example) in the field which has a format of Medium Date.  So it gets changed to 05-May-05 after entering the date in that field.  I do not know why this value, or whatever value, isn't getting passed.

Thanks for all your hard work.  I have so much time in this that I think I'll abort the mission for now.  Nothing seems to be working quite right.

I do appreciate your efforts.

Thanks for the grade...

The offer still stands if you want me to take a look at the db

:-)