none
Sending automatic emails using Acess 2007 RRS feed

  • Question

  • Hello,

     I am working on my inventory database. I want to receive an automatic email whenever there is a new order of items. Is it possible?

    Please let me know

    thanks,

    Pranay

    Thursday, January 12, 2017 7:08 PM

Answers

  • Hi PranayGangavarapu,

    on the submit button call the code mentioned below to send mail.

    Sub SendEmailOutlook(strTo As String, strSubject As String, strBody As String, _
        strFile As String, Optional bFileAttach As Boolean = False, Optional bPreview As Boolean = False)
        
        On Error GoTo SendEmailOutlookErr
        
        Dim strEmail As String
        Dim strMsg As String
        Dim oLook As Object
        Dim oMail As Object
        
        Set oLook = CreateObject("Outlook.Application")
        Set oMail = oLook.CreateItem(0)
        With oMail
            .ReplyRecipients.Add "ben@accessdatabasetutorial.com"
            .To = strTo
            '.body = sBody
            .HTMLBody = strBody
            .Subject = strSubject
            If strFile <> "" Then
                .Attachments.Add (strFile)
            End If
            If bFileAttach = True Then
                'THIS IS WHERE YOU CODE YOUR FORM TO ATTACH FILE(S)...
                '.Attachments.Add (CurrentProject.Path & "XYZ.XXX")
            End If
            If bPreview = True Then
                .Display
            Else
                .Send
            End If
        End With
        
        If bPreview = False Then
            Set oMail = Nothing
            Set oLook = Nothing
        End If
        Exit Sub
        
    SendEmailOutlookErrExit:
            Exit Sub
        
    SendEmailOutlookErr:
            MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
            Resume SendEmailOutlookErrExit
    End Sub

    use the code below to call the above sub.

    Sub demo()
    Call SendEmailOutlook("xyz@mail.com", "demosubject", "demo body", "", False, False)
    End Sub

    you need to pass the reference of textboxes placed on your form to pass the parameters of "SendEmailOutlook".

    Reference:

    Sending Email Using Microsoft Access VBA

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 13, 2017 3:14 AM
    Moderator
  • Does the submit button perform any validations and thus can stop the submission process for various reasons?

    Anyways, using the button's On Click event, you should be able to use the DoCmd.SendObject method to send out e-mail(s) as required. The basic idea would be something along the lines of

        Dim sMsg As String
        sMsg = "The following item was just submitted.  Part No.:" & Me.PartNo
        DoCmd.SendObject acSendNoObject, , , "to@xxxx.com", , , "YourSubject", sMsg, bPreviewMsg_True/False

    I included Part No.:" & Me.PartNo just to demonstrate how you can pull the relevant information for the form.

    Also, the last input variable, bPreviewMsg_True/False, is used to specify whether you want to (TRUE) pop-up the e-mail to review it prior to sending it out, or (FALSE) send it automatically without any user interaction.  So don't forget to set it as you see fit.

    SendObject is the most straightforward approach to send out e-mails in Access, but it remain rudimentary.  If you need more advanced functionalities, the Outlook Automation (like the function Deepak supplied) can be an option.  One word of caution regarding Outlook Automation is I have experience issue with the CreateObject and Outlook specifically and wrote a workaround script which you can find at http://www.devhut.net/2014/10/31/createobjectoutlook-application-does-not-work-now-what/.  This issue happens for some, but not all PCs and I've never managed to figure out the why.  By implementing the above workaround, your code will work everywhere.

    Another email technique would be to use CDO mail technique, such as: http://www.devhut.net/2014/11/29/vba-cdo-mail/

    So as you can see, there are numerous options available to you depending on your wants and needs.  Post back if you need any further help.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Friday, January 13, 2017 10:20 AM

All replies

  • What qualifies as a "new order of items"?

    When a form entry is made?

    Please explain a little more, give us a better picture of your process and setup.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Thursday, January 12, 2017 7:42 PM
  • Hello Daniel,

    My process  has 2 points, 1. Ordering point, 2. Delivery point.

    Ordering point ,Person fills the  access order form with the items he /she needs and the quantity.

    Once they  hit the submit, The Person at the delivery point should receive an email with details of the same.

    Hope this explains my set up.

    Thanks,

    Pranay



    Thursday, January 12, 2017 8:08 PM
  • Hi PranayGangavarapu,

    on the submit button call the code mentioned below to send mail.

    Sub SendEmailOutlook(strTo As String, strSubject As String, strBody As String, _
        strFile As String, Optional bFileAttach As Boolean = False, Optional bPreview As Boolean = False)
        
        On Error GoTo SendEmailOutlookErr
        
        Dim strEmail As String
        Dim strMsg As String
        Dim oLook As Object
        Dim oMail As Object
        
        Set oLook = CreateObject("Outlook.Application")
        Set oMail = oLook.CreateItem(0)
        With oMail
            .ReplyRecipients.Add "ben@accessdatabasetutorial.com"
            .To = strTo
            '.body = sBody
            .HTMLBody = strBody
            .Subject = strSubject
            If strFile <> "" Then
                .Attachments.Add (strFile)
            End If
            If bFileAttach = True Then
                'THIS IS WHERE YOU CODE YOUR FORM TO ATTACH FILE(S)...
                '.Attachments.Add (CurrentProject.Path & "XYZ.XXX")
            End If
            If bPreview = True Then
                .Display
            Else
                .Send
            End If
        End With
        
        If bPreview = False Then
            Set oMail = Nothing
            Set oLook = Nothing
        End If
        Exit Sub
        
    SendEmailOutlookErrExit:
            Exit Sub
        
    SendEmailOutlookErr:
            MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
            Resume SendEmailOutlookErrExit
    End Sub

    use the code below to call the above sub.

    Sub demo()
    Call SendEmailOutlook("xyz@mail.com", "demosubject", "demo body", "", False, False)
    End Sub

    you need to pass the reference of textboxes placed on your form to pass the parameters of "SendEmailOutlook".

    Reference:

    Sending Email Using Microsoft Access VBA

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 13, 2017 3:14 AM
    Moderator
  • Does the submit button perform any validations and thus can stop the submission process for various reasons?

    Anyways, using the button's On Click event, you should be able to use the DoCmd.SendObject method to send out e-mail(s) as required. The basic idea would be something along the lines of

        Dim sMsg As String
        sMsg = "The following item was just submitted.  Part No.:" & Me.PartNo
        DoCmd.SendObject acSendNoObject, , , "to@xxxx.com", , , "YourSubject", sMsg, bPreviewMsg_True/False

    I included Part No.:" & Me.PartNo just to demonstrate how you can pull the relevant information for the form.

    Also, the last input variable, bPreviewMsg_True/False, is used to specify whether you want to (TRUE) pop-up the e-mail to review it prior to sending it out, or (FALSE) send it automatically without any user interaction.  So don't forget to set it as you see fit.

    SendObject is the most straightforward approach to send out e-mails in Access, but it remain rudimentary.  If you need more advanced functionalities, the Outlook Automation (like the function Deepak supplied) can be an option.  One word of caution regarding Outlook Automation is I have experience issue with the CreateObject and Outlook specifically and wrote a workaround script which you can find at http://www.devhut.net/2014/10/31/createobjectoutlook-application-does-not-work-now-what/.  This issue happens for some, but not all PCs and I've never managed to figure out the why.  By implementing the above workaround, your code will work everywhere.

    Another email technique would be to use CDO mail technique, such as: http://www.devhut.net/2014/11/29/vba-cdo-mail/

    So as you can see, there are numerous options available to you depending on your wants and needs.  Post back if you need any further help.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Friday, January 13, 2017 10:20 AM
  • Hello,

    I have used both codes but I am not getting what I want. So, I just want to elaborate what my problem is.

    Firstly,

    This is my order form (Below) which is connected to order table which has same entities.So, After entering new record with all the following details(Press #, item#..etc..,) and when hit the submit button (it should send the email to the emails in the Material Handler table. Is it possible.

    Thanks,

     

    Monday, January 16, 2017 3:33 PM
  • Hi PranayGangavarapu,

    did you call the code I suggested you in my previous post on the button click and pass the parameters?

    I don't understand what do you mean by it's not working as per your requirement.

    simply you need to call it and it will send the mail.

    when you test my code did it send the mail?

    did you get any error?

    if still you have issue then try to post your button click code.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 17, 2017 5:07 AM
    Moderator
  • Hello Deepak,

     I am still a beginner in using Access VBA. Can you help  me where I need to insert my form text fields (Press #,Item#, etc..,)

    appreciate your help.

    Thanks,

    Pranay

    Tuesday, January 17, 2017 2:07 PM
  • Hi PranayGangavarapu,

    For example refer form below.

    I have created 3 textboxes and 1 button.

    when you select the textbox in the design view and go to it's properties.

    here you can set it's name. it will help you to access it's value in code.

    set name for all controls.

    then select button.

    go to properties and select "Event" Tab.

     then you will see first option "On click". then click down arrow and select "[Event Procedure]".

    then click the button beside it "[...]".

    it will open a VBE and show you a click event of that button.

    you need to call my function there and pass the parameter from textbox.

    you can refer the textbox value like below.

    Me.txtto.Value

    Hope it will give you an idea to implement and use the code.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 18, 2017 2:43 AM
    Moderator
  • You can send email using CDO... Reference with name of "Microsoft CDO for Windows 2000".. It's easy to setup and you can set any email account with it.. I can share code if you want. with CDO Email will be sent instantly and you don't need to wait for any dependency.

    Regards, Wasif Shahid ACCEDER software


    Thursday, January 19, 2017 7:58 PM