How to use Access to create a button that opens an Outlook Email?

[dfean]

Hi,



In Access, I want to basically make a button that when you click on it would Outlook email saying,

To: (the person email address)
Subject: Request a laptop
Body: I would Like to request a laptop please.
-----------------------
And then you can send this email

[ItsMe]

Not sure if you need to edit the email before sending or you want full automation. Here is some code to get you started.

'Reference Microsoft Outlook and
'use early binding
Dim objOutlook As New Outlook.Application
Dim objNewEmail As MailItem
'Dim objAttachReport As Attachments
''Instantiate your objects
Set objNewEmail = objOutlook.CreateItem(olMailItem)
'Set objAttachReport = objNewEmail.Attachments
''Add your file to the Attachments Collection
'objAttachReport.Add "C:\Test\PDF_Files\Test.pdf", olByValue
    With objNewEmail
        .BodyFormat = olFormatRichText
        .To = "myEmail@email.com"
        .Subject = "Test Message"
        .HTMLBody = "This is the body of the message"
'        'Save the email to ensure the attachment is a
'        'copy of the original file (olByValue)
'        .Save
'        .Send
    End With

'tidy up
Set objAttachReport = Nothing
Set objNewEmail = Nothing
Set objOutlook = Nothing

MsgBox "Complete"

[dfean]

Would I have to create a macro or something else.

[ItsMe]

I am not sure what you mean by, "macro". Access has tools that can help you do stuff, like create emails, interact with Outlook, etc. Access has predefined actions that you can automate by creating an Access Macro.

What I have provided here is VBA code. This can reside in a Form's Module, for example. I typically use forms and VBA code to do stuff.

[dfean]

How do you link that module to the command button?

[ItsMe]

When you create a form, there is not a Module associated with the form. So you can add a Command Button via the Ribbon, under the design tab. It seems you know how to do this. The trick to VBA is not to use the Wizard that will launch by default. Newer versions of Access use the Wizard to create Macro's, embedded Macros to be more precise. These Macros are not VBA.

Work in your form via Design View for best results.

To add VBA code to a Command Button, add the button to the design surface, cancel the wizard if it launches and use the ellipses next to the desired Event that is described in the Command Button's properties window.

Under the Event tab in the properties window, you will see a click event for Command Buttons. Make sure your Command Button has focus while you are looking at the properties window. Otherwise, you will be adjusting the properties for another control and or Object. When you click the elapses (...) a couple of dialog buttons will pop-up. I can't remember what they ask, but you want to create VBA code. You want to create a new event and do not want to use the wizard or create a Macro.

Here is a screenshot of the property window.



You do not want to create an embedded macro.

[dfean]

Something went wrong,

[dfean]

Okay I have started again

Do I click on Code Bulider or Expression Builder

[ItsMe]

You want Code Builder

[dfean]

Okay I did that then I typed the code in,



Then what do I do next do I save it, or something.

[ItsMe]

OK pretty good. When you clicked the ellipses and followed the dialog windows, you launched the VBA editor and created a module that will always be associated with your form.

In addition, because you launched the VBA editor via the ellipses, you created an event handler within your new VBA module. In other words, Access added the first and last line of code for your "Click Event". The first line is "Private Sub Command0_Click" and the last line of code for your event handler is "End Sub".

All of your code, all of your statements that you want executed when the event handles a click, belongs after the first line of code and before the last (End Sub).

[dfean]

Could you show me screenshots of how to do it possibly please, I'm kinda new to all of this.
Thanks

[dfean]

Because this happened

[ItsMe]

Not a problem, give me a minute.

Meanwhile, see if you can find the correct reference needed. Using the VBA editor, from the menu bar at the top, under tools, select References. The References window will open. You want to select your version of Microsoft Outlook from the list of optional references. In this screenshot, you will see I have selected Microsoft Outlook 15.0 Object Library. Mine is 15.0 because of the version of Outlook I am using.

[ItsMe]

Here is a sample DB that will use Outlook to start an email.