Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Paste Function through VBA (Access XP)

    I am a newbie to both access and VBA....so pls. keep this in mind when responding as I am venturing out into new horizons that are SIGNIFICANTLY beyond my current skill set. I would like to automate a process to generate and send an e-mail from access. I have found the VBA code on the internet to generate the e-mail message in access, but I can't figure out how to past the access information into the BODY of the e-mail [i.e., I don't want an attachment]. All of the internet examples assume that data is posted as an attachment or at least the ones I have identified. After the end user selects the row from the query screen, I would like to send this information in the BODY of the automated e-mail [i.e., end user would need to activate the macro after selecting the row on the query screen]. I also need suggestions on how the end user can easily activate the VBA macro code after selection the row on the query screen.

    I have validated that if I manually select the query row, press the copy button icon, then open a blank e-mail in Outlook XP and hit the paste icon the process will work [i.e., the row with four columns of data is pasted into the body of the e-mail]...how can this be done in VBA once the end user has selected the row? The query has only four columns of data [account number, name, address and file location]. I hope I haven't been too vague or boring with too much detail. THANKS. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste Function through VBA (Access XP)

    What you want requires advanced VBA coding - it involves controlling Outlook from Access in VBA code. There are several threads about this subject in this forum, you'll find them if you do a search for Outlook.Application, but I wouldn't recommend a newbie to try this as a first attempt at programming in Access. Sending info as an attachment is MUCH easier!

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Paste Function through VBA (Access XP)

    Thanks for the reply. Unfortunately, an attachment will not work for what I need to accomplish. I'll need to trash the idea for this project. I thought that I was so close as, with the help of the code in this lounge and the internet, I had been able to generate the code to successfully send the e-mail from access. The last missing piece was how to paste what was on the clipboard into the body of the e-mail....I know I'm a newbie, but I can't believe that there is not fairly simple code to use the standard windows paste function to get something pasted into the body of the e-mail. I seems so easy when you do this process manually as I stated in my previous post. Bummer. THANKS.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Paste Function through VBA (Access XP)

    What you are proposing to do requires the use of Automation (sometimes referred to as OLE Automation). Using it requires that you understand VBA reasonably well, and that you understand the object model for both Access and Outlook. If you want to do some preliminary exploration, you might find our Automation Tutorial useful. It also contains some links to MSKB articles that provide sample code.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Paste Function through VBA (Access XP)

    Hans,
    Can I create VBA code in Access and attach it to a button to place on the Access tool bar that will open a blank e-mail, with the "to" and "subject" line already filled in [these will be the same for every e-mail]? We use Outlook XP with Exchange server. This way, the user could highlight the fields as discussed in my first post, click copy and then click the Access tool bar button. An e-mail then opens [with the to and subject lines already populated] and then the user clicks the paste icon to paste the access records into the body of the e-mail. Of course, the last step would have the user then click send. The database is stored on our network, so I am hoping by adding the e-mail code & menu button to the database, that it will appear for all users when opening this database. I am still probably asking for the world as a newbie, but if you point me in the right direction, I'll give it a try....thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste Function through VBA (Access XP)

    Yep.
    - Activate the Modules section of the database window.
    - Click New.
    - Copy the following code into the module window that appears:

    Public Function CreateMail()
    On Error GoTo ErrHandler
    DoCmd.SendObject To:="Somebody@Somewhere.com", Subject:="Something"
    Exit Function
    ErrHandler:
    If Err = 2501 Then
    ' Canceled by user - ignore
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Function

    - Switch back to Access.
    - Select Tools | Customize...
    - Activate the Commands tab (if necessary)
    - Select the File category, and drag the 'Custom' button to a convenient location on a toolbar.
    - Right-click the new button and select Properties.
    - Edit the Caption to (for example) Send e-mail.
    - Enter <code>=CreateMail()</code> in the On Action property.
    - Click Close twice.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Paste Function through VBA (Access XP)

    Hans,
    Works like a charm. You are surely a wizard at VBA, Excel and Access. Also, I read the EXCEL board all most everyday to help me to grow my Excel skills and want to thank you for the countless hours you give to help people you don't even know learn more about Excel, Access, etc.. So on those "down" days where you may feel unappreciated, you have helped so many people ---including me---and I am thankful for your talent and willingness to share your talent. Take a bow.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste Function through VBA (Access XP)

    Thanks for your kind words!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •