Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-05-10
34
Medium Priority
?
427 Views
Last Modified: 2011-09-20
I have a form which asks for a date entry.  Format is Medium Date for that field.  On the form I have a button which runs a macro.  The macro runs a query which creates a SendObject action.  I want the Excel file which is created to be named the same as the field entry on the form.  Can I do this?

--Steve
0
Comment
Question by:SteveL13
  • 19
  • 15
34 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 13969104
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



0
 

Author Comment

by:SteveL13
ID: 13969225
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?

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13969251
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...?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 26

Expert Comment

by:dannywareham
ID: 13969281
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
0
 

Author Comment

by:SteveL13
ID: 13971508
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
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13975559
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.
0
 

Author Comment

by:SteveL13
ID: 13976924
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
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13977125
You need to set a reference to Outlook in TOOLS > REFERENCES in the VBA window...

:-)
0
 

Author Comment

by:SteveL13
ID: 13977195
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
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13977208
What is the addess of the file...?

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

Author Comment

by:SteveL13
ID: 13977271
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.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13978394
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?
0
 

Author Comment

by:SteveL13
ID: 13978583
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.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13978887
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?
0
 

Author Comment

by:SteveL13
ID: 13978944
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.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13979533
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.
0
 

Author Comment

by:SteveL13
ID: 13979908
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
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13979947
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
0
 

Author Comment

by:SteveL13
ID: 13979970
I'm sorry.. where do I put the line "Msgbox myFile" ?

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13980311
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

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13980321
It could also be that the file extension is missing. Change this line:

myName = "C:\PaulExcelFiles\" & myName

To this

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

Author Comment

by:SteveL13
ID: 13980491
I tried...

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

Same error message.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13984095
Is the message in teh message box the correct address to your folder and file...?
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13984096
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).
0
 

Author Comment

by:SteveL13
ID: 13985217
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.


0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13985733
What does your user mesasge box say?
Add

Msgbox myFile

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

0
 

Author Comment

by:SteveL13
ID: 13985778
Sorry, I don't understand your last comment.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13985855
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?
0
 

Author Comment

by:SteveL13
ID: 13986098
It says,

The value passed is:
[OK]

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13986328
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...
0
 

Author Comment

by:SteveL13
ID: 13986886
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.
0
 
LVL 26

Accepted Solution

by:
dannywareham earned 1800 total points
ID: 13986963
Strange...
Try this:

Dim myName as String
myName = nz(Me.txtDate.value,"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


Failing that, do you want to post your db somewhere public, or (asa  last resort), you can mail to me (address in profile)

:-)
0
 

Author Comment

by:SteveL13
ID: 13987836
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.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13988395
Thanks for the grade...

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

:-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question