Paste Function through VBA (Access XP)

Topic

New Reply

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.

Reply | Quote

Replies

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!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 =CreateMail() in the On Action property.
– Click Close twice.

Reply | Quote

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.

Reply | Quote

Thanks for your kind words!

Reply | Quote