Sending email

[AJ1971]

Hi, I am new to this forum and to Access, i have very limited knowledge of VBA and am currently teaching myself how to find my way around Access. I have currently got the following set up:

Tables

• AccessControl
• HelpdeskTickets
• LeaverRequest
• Users

Forms

• AccessControlRequest
• NewTicket
• NewUser

I have set up the access control form to email the relevant recipient, this was the easy bit what i would like to do now is send an email from the 'NewTicket' which comprises of:

• Brief Description
• TicketID
• Ticket Raised by
• Assigned to
• Opened by
• Opened date
• Due date
• Priority
• Status
• Category
• Referred
• Dept ref
• Full description
• Comments

i would like to be able to add an email button that will email the Brief Description, TicketID, Assigned to and Comments details to the person who the ticked was raised by however i am having real issues. I have added the following into a module called Email1:
Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody, ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem

On Error GoTo ErrMail

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)

With oMail
.To = pvTo
.Subject = pvSubj
If Not IsNull(pvBody) Then .Body = pvBody
If Not IsEmpty(pvFile) Then .Attachments.Add pvFile, olByValue, 1
.Send
End With

Email1 = True
Set oMail = Nothing
Set oApp = Nothing
Exit Function

ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume Next
End Function


and then added the following to the On Click tab in properties:
Call Email1("bob@co.com", 'Ticket Raised By: BOB", [descrption])
however i am getting the following error message:
Microsoft Access cannot find the object 'Call Email1 (bob@co.'If 'Call Email1("bob
co' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.


This is not code that i have thought up myself it was on the advise of someone else however i really dont know how to resolve the error message.


I would really appreciate any help given


Many thanks
AJ

 

[Cotty42]

This function will do this:-

Public Function SendEMail(ToAddress, CCAddress, BCCAddress, Subject, Message, From, Attach)
'open Outlook, attach zip folder or file, send e-mail
    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
 
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
        .BodyFormat = olFormatPlain
        .To = ToAddress
        If Not IsNull(CCAddress) Then .CC = CCAddress
        If Not IsNull(BCCAddress) Then .BCC = BCCAddress
        .Subject = Subject
        '.HTMLBody = "<pre>" + strMessageText + Chr(10) + Chr(10) + strMessageText2 + Chr(10) + strMessageText3 + Chr(10) + Chr(10)
        .HTMLBody = "<pre>" + Message + "</pre>"
        .HTMLBody = .HTMLBody + "<pre>" + From + "</pre>"
        If Not IsNull(Attach) Then
            .Attachments.Add (Attach)
        End If
    ''.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
        .send
    End With
End Function

 

[AJ1971]

Thanks for the quick response, i have removed the previous code and entered this as a visual basic module however nothing is happening, no error messages and no email? Is there something i am missing here?

Many thanks

AJ

 

[Cotty42]

You will need to call the function and pass the necessary variables to it, such as:

     SendEMail pvTo, Null, Null, pvSubj, pvBody, fromText, pvFile

All variables need to be passed hence the 'Null' for CC and BCC, replace from text with your details if you want to sign off the e-mail, otherwise replace it with 'Null'.

Hope this helps

 

[Cotty42]

You will also need to make sure that the Microsoft Outlook 14.0 Object Library is checked in the Tools/References section of the VBA Window.

 

[AJ1971]

Again thank you for your help.

I am really struggling as to where I put the code, how I 'call a function' or 'pass the variable'. the code I have entered is not a standalone module.

I appreciate your help but I think without a more hand holding situation your help is lost on me.:banghead:

Thank you again.

 

[Cotty42]

You said you want a button on your form which you want to send the e-mail

i would like to be able to add an email button that will email the Brief Description

The call function (see post #4) should be put in the 'On Click' Sub of this button.

The module (see post #2) needs to be set up as a separate VBA module.

In the VBA window, click the Tools Tab and References. This will list all of the available references, selected ones are at the top of the list (identified with a Tick). If the reference to Microsoft Outlook 14.0 (see post #5) is not selected, scroll down through the list (they are in alphabetic order) until you find the reference and select it.

You will obviously need to write code etc. to generate the message that you want to send.

When you open the form in Form mode, clicking the e-mail button should then call the procedure to send the e-mail.

It is probably worthwhile adding a break-point to the call function and stepping through the procedure one step (F8) at a time to check it is running OK.

Hope this helps