CODEWITHSUNDEEP
vbams-accessoutlook
David Meller
David Meller

Reputation: 33

Sending Email from Access

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

Upvotes: 3

Views: 2636

Answers (1)

LiamH
LiamH

Reputation: 1502

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

Upvotes: 4

Related Questions