Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

After I enter data in Form, I’d like them sent by email as .xls at

Discussion in 'Information Technology' started by Adnan, Jul 28, 2006.

  1. Adnan

    Adnan
    Expand Collapse
    Guest

    Hi dear MVPs,
    After I fill the form I would like to create a button that would email
    current filled form to someone as an excel attachment, how would I do this?

    Any help is greatly appreciated!
    Thank you,
    Adnan

    --
    Please post all your inquiries on this community so we can all benefit -
    Thank you!
     
  2. Loading...


  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Adnan,

    > Hi dear MVPs,


    You should not be restricting your question to MVP's. There are many people
    who are regular contributors to this newsgroup, who are perfectly capable of
    answering your question, but they are not MVP's. I was one of these people
    until just April of this year.

    1.) First, your form must include a primary key field, or a field set as a
    unique index. This will give you a way to identify the particular record that
    you wish to export.

    2.) Create a new query that includes the fields you wish to export to your
    Excel spreadsheet. This might include all of the fields in the form, or a
    subset of the fields. Add a criteria for the primary key or unique index
    field that points to the open form. Something like this:

    Criteria: [Forms]![frmPeople]![CustomerID]

    for a form that is named "frmPeople", and a unique indexed field named
    "CustomerID". Name the query "qryExcelExport".

    3.) Create a command button on your form, and name it something like
    "cmdSendUsingExcel". Add the following code for the click event procedure of
    this button:

    Private Sub cmdSendUsingExcel_Click()
    On Error GoTo ProcError

    DoCmd.SendObject ObjectType:=acQuery, _
    ObjectName:="qryExcelExport", _
    OutputFormat:=acFormatXLS, EditMessage:=True

    ExitProc:
    Exit Sub
    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "Error in procedure cmdSendUsingExcel_Click..."
    Resume ExitProc
    End Sub


    If you have other information available on your form, such as the person's
    e-mail address, you can add the optional "To:=" argument to the
    DoCmd.SendObject statement.


    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Adnan" wrote:

    > Hi dear MVPs,
    > After I fill the form I would like to create a button that would email
    > current filled form to someone as an excel attachment, how would I do this?
    >
    > Any help is greatly appreciated!
    > Thank you,
    > Adnan
     
  4. Adnan

    Adnan
    Expand Collapse
    Guest

    RE: After I enter data in Form, I’d like them sent by email as .xl

    Tom,
    With the description I’ve provided this is exactly what I was looking for,
    but there was something I didn’t previously mention, would it be possible to
    email current records (export them first into my .xls format I already have
    on my Desktop, also input these records into curtain cells such as: A4, A5,
    etc not including deaders) and then e-mail it (have it attached and ready
    uploaded on the email (outlook) body.

    What I’m trying to do is, when a customer fills the form he/she can also
    email it as an .xls attachment with a single click from an optional button
    (with my .xls format).

    P.S – Speaking of MVP’s, you are right, but I kind of consider/respect//call
    these contributors MVP’s, too.

    Congratulation/happy your new/additional recent title (MVP) ****

    Thank you much Tom!
    Adnan :)

    --
    Please post all your inquiries on this community so we can all benefit -
    Thank you!


    "Tom Wickerath" wrote:

    > Hi Adnan,
    >
    > > Hi dear MVPs,

    >
    > You should not be restricting your question to MVP's. There are many people
    > who are regular contributors to this newsgroup, who are perfectly capable of
    > answering your question, but they are not MVP's. I was one of these people
    > until just April of this year.
    >
    > 1.) First, your form must include a primary key field, or a field set as a
    > unique index. This will give you a way to identify the particular record that
    > you wish to export.
    >
    > 2.) Create a new query that includes the fields you wish to export to your
    > Excel spreadsheet. This might include all of the fields in the form, or a
    > subset of the fields. Add a criteria for the primary key or unique index
    > field that points to the open form. Something like this:
    >
    > Criteria: [Forms]![frmPeople]![CustomerID]
    >
    > for a form that is named "frmPeople", and a unique indexed field named
    > "CustomerID". Name the query "qryExcelExport".
    >
    > 3.) Create a command button on your form, and name it something like
    > "cmdSendUsingExcel". Add the following code for the click event procedure of
    > this button:
    >
    > Private Sub cmdSendUsingExcel_Click()
    > On Error GoTo ProcError
    >
    > DoCmd.SendObject ObjectType:=acQuery, _
    > ObjectName:="qryExcelExport", _
    > OutputFormat:=acFormatXLS, EditMessage:=True
    >
    > ExitProc:
    > Exit Sub
    > ProcError:
    > MsgBox "Error " & Err.Number & ": " & Err.Description, _
    > vbCritical, "Error in procedure cmdSendUsingExcel_Click..."
    > Resume ExitProc
    > End Sub
    >
    >
    > If you have other information available on your form, such as the person's
    > e-mail address, you can add the optional "To:=" argument to the
    > DoCmd.SendObject statement.
    >
    >
    > Tom Wickerath, Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    > "Adnan" wrote:
    >
    > > Hi dear MVPs,
    > > After I fill the form I would like to create a button that would email
    > > current filled form to someone as an excel attachment, how would I do this?
    > >
    > > Any help is greatly appreciated!
    > > Thank you,
    > > Adnan
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    RE: After I enter data in Form, I’d like them sent by email as .xl

    Hi Adnan,

    You can do this, but not by using DoCmd.SendObject, since that is intended
    to send an object (table, query, etc.) that is within your database. You'll
    need to use Outlook, or some other e-mail client that exposes a programmable
    object model. Outlook Express is ruled out in this case.

    I suggest that you take a look at this article:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno2k3ta/html/odc_ac_olauto.asp

    You might also want to download the XP Automation Help file from Microsoft
    (XPAutomation.chm):
    http://www.microsoft.com/downloads/...c8-fbee-4164-b8e9-589464f3fcad&DisplayLang=en

    and perhaps this one:

    Access 2003 Sample: Using Automation with Access and Outlook
    http://www.microsoft.com/downloads/...1a-6b58-4091-92c8-8d727894c080&DisplayLang=en

    Finally, take a look at this thread to see if it gives you any useful ideas:
    http://www.microsoft.com/office/com...cess&mid=a6cff88e-6897-46bb-b112-2d7014c31602


    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Adnan" wrote:

    > Tom,
    > With the description I’ve provided this is exactly what I was looking for,
    > but there was something I didn’t previously mention, would it be possible to
    > email current records (export them first into my .xls format I already have
    > on my Desktop, also input these records into curtain cells such as: A4, A5,
    > etc not including deaders) and then e-mail it (have it attached and ready
    > uploaded on the email (outlook) body.
    >
    > What I’m trying to do is, when a customer fills the form he/she can also
    > email it as an .xls attachment with a single click from an optional button
    > (with my .xls format).
    >
    > P.S – Speaking of MVP’s, you are right, but I kind of consider/respect//call
    > these contributors MVP’s, too.
    >
    > Congratulation/happy your new/additional recent title (MVP) ****
    >
    > Thank you much Tom!
    > Adnan :)
     
  6. Adnan

    Adnan
    Expand Collapse
    Guest

    RE: After I enter data in Form, I’d like them sent by email as .xl

    I was thing of making a button to export current filled form’s data into a
    saved excel spreadsheet then another button to email that excel saved
    spreadsheet. I can do the second but not the first (exporting current data
    into excel). I know I’ve seen some code for this somewhere in this community.
    Got to search again…

    Other then thin, you were greatly helpful. Thank you Tom,
    Adnan, Computer Science Student
    Kosovo

    --
    Please post all your inquiries on this community so we can all benefit -
    Thank you!


    "Tom Wickerath" wrote:

    > Hi Adnan,
    >
    > You can do this, but not by using DoCmd.SendObject, since that is intended
    > to send an object (table, query, etc.) that is within your database. You'll
    > need to use Outlook, or some other e-mail client that exposes a programmable
    > object model. Outlook Express is ruled out in this case.
    >
    > I suggest that you take a look at this article:
    > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno2k3ta/html/odc_ac_olauto.asp
    >
    > You might also want to download the XP Automation Help file from Microsoft
    > (XPAutomation.chm):
    > http://www.microsoft.com/downloads/...c8-fbee-4164-b8e9-589464f3fcad&DisplayLang=en
    >
    > and perhaps this one:
    >
    > Access 2003 Sample: Using Automation with Access and Outlook
    > http://www.microsoft.com/downloads/...1a-6b58-4091-92c8-8d727894c080&DisplayLang=en
    >
    > Finally, take a look at this thread to see if it gives you any useful ideas:
    > http://www.microsoft.com/office/com...cess&mid=a6cff88e-6897-46bb-b112-2d7014c31602
    >
    >
    > Tom Wickerath, Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    > "Adnan" wrote:
    >
    > > Tom,
    > > With the description I’ve provided this is exactly what I was looking for,
    > > but there was something I didn’t previously mention, would it be possible to
    > > email current records (export them first into my .xls format I already have
    > > on my Desktop, also input these records into curtain cells such as: A4, A5,
    > > etc not including deaders) and then e-mail it (have it attached and ready
    > > uploaded on the email (outlook) body.
    > >
    > > What I’m trying to do is, when a customer fills the form he/she can also
    > > email it as an .xls attachment with a single click from an optional button
    > > (with my .xls format).
    > >
    > > P.S – Speaking of MVP’s, you are right, but I kind of consider/respect//call
    > > these contributors MVP’s, too.
    > >
    > > Congratulation/happy your new/additional recent title (MVP) ****
    > >
    > > Thank you much Tom!
    > > Adnan :)
     
  7. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: After I enter data in Form, I'd like them sent by email as .xl

    Don't forget to address the potential of a user forgetting to export
    the data but still pressing the email button, thereby sending the
    previously exported data instead of the current data.

    (Don't enable the second button until the first one has been pressed.)
     

Share This Page