Sending Email from Access

Question

I am trying to develop a database where teachers log an application for when they need a lesson to be covered (I'd do this in SQL but I can't currently).

I would like the database to notify a certain member of staff when an application is made. Selecting the member of staff would be done from a combobox, driven by a query. The reason for the query is that I only want specific members of staff to receive this notification - essentially those who manage other staff.

Once that member of staff has been selected, I want the person making the application to click a button, which will then fire an email to the person selected in the combobox.

I get a flicker of Outlook doing something and then nothing.

This is what I have so far, with the DLookup using the staff member selected in the combobox to then find the email address in the Staff table:

Private Sub Command788_Click()
    Dim Email_Note As Variant
    Email_Note = DLookup("Email", "Staff", Forms![Cover Application Form]!Combo767)
    Dim olLook As Outlook.Application
    Dim olNewEmail As Outlook.CreateItem
    Dim StrContactEmail As String
    Set olLook = New Outlook.Application
    Set olNewEmail = olLook.CreateItem(olMailItem)
    strEmailSubject = "Application for Cover: Line Manager Notification"
    strEmailText = "Something in here..."
    StrContactEmail = "Email_Note"
    olNewEmail.Display
End Sub

Thanks - I'll make that change. I'm now clicking the command button and getting no response. Sure I did yesterday. What do you reckon to the rest? — David Meller
I think you need to put a breakpoint in the code and examine olLook — serakfalcon
Dim olNewEmail As Outlook.CreateItem should be Outlook.MailItem — Andre
In addition, your strEmail variables don't do anything. Look here for a start: stackoverflow.com/q/17973549/3820271 — Andre
hey @DavidMeller, here on Stackoverflow, things like 'thanks', 'greetings', 'hello', 'thank you' are not really apreciated, since they add 'noise' to the Q&A system. I've edited them out for you, in future question, don't use them. Thank you — Mafii

LiamH LiamH · Accepted Answer · 2016-05-03T11:44:36

You should ensure that the Outlook library is referenced in the tools tab of VBA Editor. It also looks like you created strings for the body and subject but didn't declare them. Instead of declaring them as string variables just set the outlook. body etc. to the appropriate string as I have shown below.

You don't need to encapsulate email_note with quotes once you have declared it as a variable. I assumed that was an email address?

The strContactEmail is no longer needed, I don't see where it is used.

Private Sub Command788_Click()
Dim Email_Note As Variant
Email_Note = DLookup("Email", "Staff", Forms![Cover Application Form]!Combo767)
Dim olLook As Outlook.Application
Dim olNewEmail As Outlook.mailItem
'Dim StrContactEmail As String
Set olLook = New Outlook.Application
Set olNewEmail = olLook.CreateItem(olMailItem) 
   olNewEmail.Subject="Application for Cover: Line Manager Notification" 
   olNewEmail.Body = "Something in here..."
   olNewEmail.To = email_note
   olNewEmail.Send
Set olNewEmail = Nothing
Set olLook = Nothing
End Sub

Sending Email from Access

1 Answers