From query send email in Access

Question

I am making a system for my school with a database that sends an email to the resources department every time a worksheet needs to be printed out.

The table's fields are:

ID (an auto number)
The web address of the online file (a hyperlink)
The key (what the user types in to the parameter query that then emails the corresponding data to resources (a text string)
A description of the worksheet (a memo)

To send the email, the user fills in a set of parameter query. Instead of the query results coming up, the email should be sent. The query should contain the following fields:

The key of the worksheet (a text string)
How many worksheets need to be printed (a number)
Any notes (a memo) Then they click OK and the email is sent.

The email should contain this information, which corresponds to the data entered in the query:

A hyperlink to the worksheet
How many worksheets the user said need to be printed
The notes the user submitted and the notes already in the table

To download my database so far, click here.

I would like to do this in VBA code. Could you guide me along the right lines to making this work? Any code suggestions would be helpful as I am new to VBA. Thanks

Well our department have a limited budget so if we have to spend any money we will have to cancel the project or choose an alternative — user5626618
@HansUp I can get a query to work, I just don't know if the way I do it is right for this project — user5626618

PKatona PKatona · Accepted Answer · 2016-01-08T00:06:13

First, add "Microsoft Outlook 14.0 Object Library" to your references (you might have a different number than 14.0) by click on "Tools" then "References". I have the following code that does a bit more than you're asking but it will work. The signature argument is the text body as string. When creating the string, use "vbCrLf" to create new lines, e.g. signature = "Hello, how are you?" + vbCrLf + "I am fine." will put the sentences on two separate lines.

Private Sub createEmail(ByVal toEmailAddresses As String, _
                    ByVal ccEmailAddresses As String, _
                    ByVal att1 As String, _
                    ByVal att2 As String, _
                    ByVal signature As String, _
                    ByVal subject As String, _
                    ByVal displayIt As Boolean)
    On Error GoTo foundError
    Dim outItem As Outlook.MailItem
    Set outItem = Outlook.CreateItem(olMailItem)

    outItem.BodyFormat = olFormatHTML
    outItem.Recipients.Add toEmailAddresses
    outItem.cc = ccEmailAddresses
    outItem.subject = subject
    If att1 <> "" Then
        outItem.Attachments.Add (att1)
    End If
    If att2 <> "" Then
        outItem.Attachments.Add (att2)
    End If

    outItem.HTMLBody = signature

    outItem.Send
    'Note: if you wanted to create the email and check it first, use outItem.Save
    Exit Sub
foundError:
    MsgBox "Error in createEmail: " + CStr(Err) + ", " + Error(Err), vbOKOnly, "ERROR"
End Sub

From query send email in Access

1 Answers